ADO Updates and ODBC Linked Tables

[tylersontag]

Hey all, first post here and I have to admit, I'm got getting very far via Google and a 2003 edition of Access VBA Programmer's Reference.

My problem is, I'm migrating data out of the local Access tables into a SQL backend. The data’s all there and the linked tables are pulling correctly but whenever CurrentDB.Execute tries to execute an insert command the tables do not reflect this. No error thrown, just no data inserted.

I tried supplying the argument dbRunAsync but that throws an invalid argument error. I'm seeing I might be able to make up a bunch of queries and use DAO to update, but that would be very intrusive.

Any suggestions?

 

[pbaldy]

Try including the dbFailOnError option:

CurrentDb.Execute strSQL, dbFailOnError

 

[tylersontag]

Good call, "ODBC--call failed" ... nothing like an extremely ambiguous error to clear things up... if i can do selects off a database, whats a common reason that i'd not be able to do inserts?

 

[pbaldy]

The SQL Server tables must have key field(s), which is probably the most common issue. You might have a referential integrity problem, like inserting an invoice record for a customer ID that doesn't exist in a customer table (if that has been set up on the server). You could try copying the SQL into a new Access query and trying to run it, which might give you a more descriptive error.

 

[tylersontag]

Hmm, the tables were imported and filled from the source access database. The source DB had no constraints and neither does the SQL DB. Is it possible to supply multiple arguments as the execute option?.. i.e

CurrentDb.Execute sSQL, [dbFailOnError, dbSQLPassThrough]

It gives me an error i at first thought was related to my query but then realized it just didn't like the square brackets.

 

[tylersontag]

heres an exampe SQL command (edited for your safety)

"INSERT INTO VarFiles(FileName, S*****Nmbr, A****Nmbr, A*****e, Te****l, [Begin Date], [End Date], [Creation Date])
VALUES ("*************", 21, 2, "G*****", "D****", #8/16/2009 7:00:00#, #8/17/2009 7:00:00#, #8/17/2009 14:41:39#)"

 

[pbaldy]

I don't think you want the brackets. Try

CurrentDb.Execute sSQL, dbFailOnError, dbSQLPassThrough

Did you make sure the tables in SQL Server have keys? You can upsize without, but appends will fail.

 

[Banana]

FYI:

When you get a ODBC error, VBA only will give you error 3146. You need to interrogate the Errors collection to review the errors as given by backend:

Dim v As Variant

For Each v in DAO.Errors
   Debug.Print v.Number & ": " & v.Description
Next

The reason is that there can be multiple errors in a single operations and VBA's Err can only show most recent error, which is of course "ODBC Call failed."

Additional information

 

[tylersontag]

Thanks, looking at each element of the DAO error solved the problem (though i couldn't get any debug window to pop up...) of the 3 errors thrown the first one was an integrety constraint, a field that had been an autonumber field was now just a non-null field i was not filling out on my insert.

Thanks all.

 

[pbaldy]

Nice one Banana-dude; that will probably save me some grief the next time I get the famous "ODBC Call failed" error.

 

[Banana]

tylersontag,

The "debug windows" is actually called an "Immediate Windows", and you can get to it in VBA editor by pressing CTRL+G. I believe by default it'd be the small windows just below of the code windows.

Glad this helped.

In past, I have found it useful to write a generic ODBC error handler that's separate from the generic error handler to help reduce on the code and simplifying the runtime decisions on how to handle expected ODBC errors.

Good luck.