Questions That Should Be Asked More Frequently

[Previous] [Next]

Q. What if I don't include the key field or fields for my table in my initial query? Will I still be able to update my database with the Recordset object?

A. Short answer: Don't do this. (Why would you ever do this? Bad programmer!) Include the key field or fields in your query.

Long answer: I have to admit, I've done some testing of this scenario because I was curious and I've had people ask about it before. In my tests, I watched a trace of the action queries passed by ADO to my SQL Server database through SQL Server Profiler. It turns out that ADO included primary key values in the action queries, even though I didn't include the primary key field in my query. However, that field didn't show up in the trace as being part of the query. What happened?

The SQL Server OLE DB provider (and ODBC driver) returned this data in a hidden field. I discovered this by checking the Recordset's Hidden Columns dynamic property. Sure enough, it was set to 1. Needless to say, I was impressed. This functionality seems similar to the Microsoft OLE DB Provider For Oracle having the sense to retrieve row ID data to facilitate updating. But don't rely on this functionality.

Why ask ADO, the OLE DB provider, or the ODBC driver to do all this work when you could simply include the key field or fields in your query? Don't be lazy. Besides, not all providers and drivers retrieve this data. When I ran this test with the Jet OLE DB Provider, it didn't retrieve the key field or fields, but it still allowed me to update my database. How? From some painful testing, I deduced that it simply used data from all the fields in the Recordset to form some sort of mock key.

In short, if you don't include the key field or fields in your query, you might still be able to update your database, but how that's accomplished depends on your OLE DB provider or ODBC driver—then again, you really shouldn't be doing this in the first place. Instead, just say no.

Q. What happens if I don't have a live connection to my database and I call Update or UpdateBatch?

A. The ADO Cursor Engine assumes you have good reason to do this and pretends it successfully updated your database. There might be times when you want to make use of this particular behavior. Just be sure to keep this behavior in mind if you disconnect your Recordset from your database. If you call the UpdateBatch method while your Recordset is disconnected, those changes will no longer be marked as pending. When you reconnect the Recordset, those changes will not be transmitted to your database.

Q. I'm having problems with my joins. With some back-end databases, I can't tell what table a particular field references because both tables in my join contain that same field name. What should I do, and why does this problem occur only with particular back-end databases?

A. ADO simply uses the field names that the OLE DB provider or ODBC driver returns. If the OLE DB provider or ODBC driver doesn't return the field names the way you want, try using aliases in the query string so that you can tell the fields apart in the Recordset object.