add record to foxpro free table with ADO

[dav]

Hi, I'm trying to add new record to a foxpro free table connect to the
database by odbc. After all tests with DAO (the method don't accept '.update
') I tried with this that I found on this site but always I get :
Run-time error '-2147217900 (80040e14)'

the code is (the ole db driver has been installed):

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim fox As ADODB.Recordset
Dim szName As String
Dim szConnect As String
Dim szSQL As String

szName = "c:\folderdbf\;"

''' Create the connect string
szConnect = "Driver=Microsoft Visual Foxpro Driver;"
szConnect = szConnect & "SourceType=DBF;SourceDB="
szConnect = szConnect & szName

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set fox = New ADODB.Recordset

cnn.Open szConnect
Set cmd.ActiveConnection = cnn

SQL = ("INSERT INTO tbl ( ID,field1,field2) " & _
"VALUES (" & Me.ID & "," & Me.field1 & "," & Me.field2 & ");")


szSQL = SQL
cmd.CommandText = szSQL
cmd.Execute

fox.Close
cnn.Close
Set fox = Nothing
Set cmd = Nothing
Set cnn = Nothing

thank you in advance

 

[Cindy Winegarden]

Hi Dav,

...connect to the database by odbc. After all tests with DAO (the method
don't accept '.update
') I tried with this that I found on this site but always I get :
Run-time error '-2147217900 (80040e14)'

You haven't said which line of code gives you the error.

the code is (the ole db driver has been installed):

Please don't confuse OLE DB with ODBC.

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim fox As ADODB.Recordset
Dim szName As String
Dim szConnect As String
Dim szSQL As String

szName = "c:\folderdbf\;"

''' Create the connect string
szConnect = "Driver=Microsoft Visual Foxpro Driver;"
szConnect = szConnect & "SourceType=DBF;SourceDB="
szConnect = szConnect & szName

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set fox = New ADODB.Recordset

cnn.Open szConnect
Set cmd.ActiveConnection = cnn

SQL = ("INSERT INTO tbl ( ID,field1,field2) " & _
"VALUES (" & Me.ID & "," & Me.field1 & "," & Me.field2 & ");")


szSQL = SQL
cmd.CommandText = szSQL
cmd.Execute

fox.Close

Fox hasn't been opened and gives an error. I assume you've snipped out some
code.

Your code worked just fine with a test table I created where ID, Field1 and
Field2 were all integers. Are all your fields some flavor of numeric? If any
are character strings or dates then your Insert statement will need some
adjustment.

 

[dav]

hi,
sorry I posted wrong code.
the string conenction is:

szConnect = "Provider=vfpoledb.1;Data Source=" & szName & ";Collating
Sequence=general"

I know that if one field is string I have to use ' ' in SQL.


I get error con cmd.execute as syntax error, but why? thanks



"Cindy Winegarden" ha scritto: