Using ADO on current database but changing Provider to Jet

[Kronix]

How do I use ADO on my current database but change the provider to Microsoft Jet so I can use bookmarks with dynamic cursors?

As stated here CurrentProject.Connection defaults to an ODBC provider. Debug.print CurrentProject.Connection gives me:

Microsoft.ACE.OLEDB.12.0

From what I understand the provider Microsoft.Jet.OLEDB.4.0 will allow bookmarks with all cursors. But how do I change that when getting the connection from CurrentProject? I tried changing the Provider in the string from the debug.print and then assigning the string to the con object's ConnectionString property, but then it says it can't recognize the file format. Not to mention I don't want to lock in a specific file name. Can't I change the properties of CurrentProject.Connection without needing to use a specific file name?

I'm really wondering how ADO is supposed to be better than DAO when it's so difficult to use.

 

[The_Doc_Man]

The problem is file format between JET and ACE, just as you reported as the error message. A file saved in JET format is an .MDB file that was last available (as the "current" format) in Ac2003. After that, the "current" format was the ACE format and .ACCDB files. Changing the connection string from ...ACE... to ...JET... doesn't work if the file you are accessing stays in ...ACE... format. You CAN try to save the file as an .MDB in Ac2003 format, but to be honest, I wouldn't recommend it.

Here's the issue: When you run newer versions of Access, the ACE engine can read the old format and "restrict itself" to the correct features. However, if you tried to use JET on a newer file, no version of JET can read the extensions made via ACE format. You can read a bit more about that in this Wikipedia article near the bottom of the article.

https://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine

ADO isn't better or worse - it is different because it was intended for different situations. However, since I don't work in ADO I'll have to defer your final question to others.

 

[CJ_London]

DAO has to work with an underlying table or query source

ADO can work with an underlying table or query or it can be disconnected from a source - i.e. you can create a recordset that only exists in memory.

Performance wise there is no real difference between the two methods although DAO has more features.

I suspect anyone currently thinking ADO is the way to go has been reading up on very old threads/articles. For a very short period about 15 years ago, Access moved to ADO but very quickly returned to DAO.

ADO might have some benefits when connecting to alternative datasources although I couldn't tell you what.

Personally I use ADO only for disconnected recordsets - and use those in much the same way as I would for collections and dictionaries - the benefit being they can be displayed and edited in a form (if appropriate) - classic example being a checkbox selection method similar to multivalue fields rather than using a multiselect listbox.