ODBC call FAILED on record ADD to SQL table

[Guest]

I'm using ADO methodology to open, read, and add records to a SQL table.
However, when I get to RecSet.Update, I get an error:

Run-time error '-2147217887 (80040e21)';

ODBC -- call failed.

Here's my code:

Dim dbConn As ADODB.Connection
Dim recset As ADODB.Recordset

Set dbConn = CurrentProject.Connection
Set recset = New ADODB.Recordset

recset.CursorLocation = adUseServer
recset.CursorType = adOpenKeyset
recset.LockType = adLockOptimistic
recset.Open "dbo_tbInstallSteps", dbConn, , , adCmdTable

recset.AddNew
recset!field1 = 'something'

 

[Sylvain Lafontaine]

From the name of the table that you are using ("dbo_tbInstallSteps"); it
looks like that you are accessing the SQL-Server through an intermediary MDB
file instead of using a direct connection to the SQL-Server. I have seen in
the past some other posts about the same kind of problem but I cannot tell
you more on this because I don't use this configuration myself; however,
here a some suggestions:

1- tell us what kind of provider you are using to access this mdb file (Jet
ODBC or Jet OLEDB 3.5 or 4.0) ?

2- try a client location (adUseClient) instead of adUseServer.

3- use a static recordset instead of adOpenKeyset.

4- make a direction connection to the SQL-Server instead of using an
intermediary linked table.

5- Use DAO instead of ADO.