What I'm describing here

This is strictly changing from DAO to ADO for accessing JET database data. We're not doing any engine changes, too. And this is brief - not comprehensive, but I hope it will make your comprehensive study a lot less frustrating. At the risk of looking a little dim-witted by showing you how easily I fell victim to some mis-steps in learning this stuff, I hope to save you the same trouble.

Where to begin?

There are some very useful documents in the MSDN CD and finding some of them was a real chore. The most useful of the bunch (and one with jumps to the others) was:

Migrating from DAO to ADO
Using ADO with the Microsoft Jet Provider
Alyssa Henry
Microsoft Corporation
July 1999

Believe it or not, if you type "Migrating from DAO to ADO" into the search box on MSDN and tell it to search, this document comes up (at least for me in July 2000 version) as 23rd. Go figure. This one is in the Data Access Technical Articles area if you want to search for it manually on the contents tab. Some of the most useful information is presented in tabular form in the first two Appendices, but be sure and look at sample code.

The seventh item in that list (your mileage may vary) is the document to read first, though. It discusses whether the move even makes sense for you. This one is in the VB section of the Knowledge base and is described as:

INFO: Issues Migrating From DAO/Jet to ADO/Jet
ID: Q225048

That one warns you about the relative last of functionality of ADO for JET vs. DAO. It also talks about engine versions issues.well go see what it says. The bottom line is that you can't do everything with ADO that was possible in DAO. This paragraph should set the tone of your expectations: "The OLE DB Provider for Jet 4.0 exposes most of the Microsoft Jet functionality, but not all of it. In addition to the ADO type library, you must include references in your project to the ADOX and Jet and Replication Objects type libraries to take advantage of functionality outside the core ADO objects. This mainly falls into the category of manipulating Access-specific objects and some of the other issues noted below. " (I don't show those "other issues" here, but security is a pretty big part of it).

Some of the other useful documents (that I have in my Favorites list, to be sure!) are called ADO Object Model, ADOX Object model, Microsoft ADOX Programmer's Reference, and JET and Replication Objects.

Mapping the Object Transition

A lot of hype was devoted in VBPJ and elsewhere about how much simpler the object model is in ADO vs. DAO. Only three objects! And that is true; there are only three ADO objects (Connection, Command, and Recordset).

But the news is really quite sad because that means there is no equivalent to the Engine itself (for which you need access to Compact the DB and to force refreshing of the cache) or even the TableDef object or its collection to which you obviously need access in order to create or modify table structures in the database.

There's plenty of information around about how to open connections and command objects and recordsets and we don't need to spend any time there. The stuff works as advertised.

But the two new object libraries you will need to hunt and learn are those mentioned in that quoted paragraph.

ADOX is the "structure" library. The "X" in its name stands for the "Extensions for DDL and Security" in its longer name/description. The object model here is of a Catalog (the Database level object), its Tables collection of Table objects (our old TableDef friends) and the Table's Columns collection of Column objects (heaven forbid we should call them fields and confuse them with the very limited Field objects of the Recordset object in ADO proper). Some sample code is found for the ADOX stuff in the first document I mentioned above. Building tables looks like the old DAO technique - define the New Table, define New column objects and append them to the Columns collection of the Table and when you have them all, append the Table to the Tables collection of the catalog.

There are some very cool Provider-specific Column properties you can set, but you must set the .ParentCatalog property of the column in order to use them. This seems a bit counter-intuitive since the column has not even been appended to the table yet, nor the table to the catalog. But if you don't do this, ADOX has no way of knowing which Provider's properties you have access to. The sample code shows this if you look carefully enough for it (I did not on my first try, of course).

The Jet Object and Replication Library supports the JRO object. Even if you don't care one whit about Replication, you need the Engine object of this family in order to perform the CompactDatabase action and to idle the engine for `dbRefreshCache', as you likely recall from DAO. Read the help carefully on the CompactDatabase because you can get an unwanted "upgrade" to version 4.0 if you don't specify that you want it to stay in 3.x (assuming that is a concern for you). You also need to provide the security information in this call if your database is secured (Workgroup File Name, Logon Name, Password). So for a methods that has only two real arguments, this dude can get LONG.

Trying to implement the advice you find

In no particluar order:

• Watch out for Provider version in your reading
  Which version of the Provider should you use? 4.0 is far superior to 3.51 in what you can do with JET. And even using it for table building, it is capable of working with an existing 3.51 database without forcing a conversion (critical in my case, maybe not even interesting in yours). But be careful with the "helpful" documents you find because some of them are for 3.51 and some are for 4.0. In particular, the datatype enums in 3.51 are different from those in 4.0 and I lost a day's work discovering and correcting this. In particular watch the Date and Text fields. Some docs recommended adDBDate for the Column's Type property. I didn't find that to be correct for either Provider (and I tested both). Just use adDate. For text, the provider does matter: 3.51 uses adChar and 4.0 uses adVarWChar.
• Watch case and spaces on Provider-specific column properties in ADOX
  When using those neat column properties, be VERY careful about the spaces in their names and about use of case. If you want, for example, to set the "Allow Zero Length" property of one of your text fields you better use:
  MyColumn.Properties("JET OLEDB:Allow Zero Length") = True
  with spaces between JET and OLEDB and between all three words on the other side of the colon (and don't forget to set MyColumn.ParentCatalog before using it!!!)
• Don't trance over "Type is invalid."
  And when building tables, don't get confused by an error msg of "Type is invalid" when you execute the statement to append the table to the tables collection. It is NOT complaining about the type of the table; it is complaining about the type of one of your column objects. This one cost me a bunch of time, too.
• Disconnected recordsets
  Disconnected recordsets really are a cool phenomena. But make sure you have the Cursor Location set to adUseClient!! It will not work without that. I found this in the documents sometimes and not others. Really, as a good rule of thumb, you better have a good reason NOT to be using adUseClient anyway. The need to set the cursor location means that you can't simply declare the recordset and throw it Open with a single line of code as there is no option in the Open method for the cursor location. Too bad. Devote a line of code to it:

  	Dim rs As ADODB.Recordset
  	Set rs = New ADODB.Recordset
  	rs.CursorLocation = adUseClient
  	rs.Open "myTable", myConnection, adOpenKeyset, _
              adOpenReadOnly, adCmdTable

  The docs tell you, but while we're on the subject, you can set all those options with property assignments and then just call .Open with lots fewer specified. If you do both, the options used in the .Open call will override the property assignments. That could be a gottcha, I suppose, too.
• Watch references carefully, as always
  We should always be careful with object references. But there is a particular trap I fell into with the disconnected recordset that I want to bring to your attention. It really applies to any recordset assignment situation but it happened to come up here with me. I had more than one recordset argument in a function whose job was to pass back some disconnected recordsets. My strategy was to re-use a local RS object and when I got is populated properly I would assign it to the argument reference and then disconnect it, like this.

  	Set rsArg1 = rsTemp
  	Set rsArg1.ActiveConnection = Nothing

  Then the code would return to building the next one by setting rsTemp to a New RS object and repopulate itself for the next RS argument. But at the conclusion of the routine I close and set to nothting all my local vars and executed a:

  	rsTemp.Close

  Since I had not reassigned rsTemp to a New RS object, it still had the same reference as the last argument I processed and I closed it, too, of course. This is easily avoided of course.just be a bit more careful than I was.

None of these things would have bitten you? Great! Then please write up one of these for me for SQL Server and MSDE which I will be tackling in a couple of weeks.

Happy coding