[RESOLVED] ADO reference

[krtxmrtz]

I'm using ADO to connect to an Access 2000 data base by setting a reference to 'Microsoft ActiveX Objects 6.1 Library'. Now my database has been updated to Access 2013. What reference do I have to set?

[SamOscarBrown]

What are available on your system, and what ones have you tried?
I've been using 2.8 for Access 2000, 2003, 2010 AND 2013.
I'll research the difference, but so far, 2.8 has sufficed well.

[SamOscarBrown]

An 'old' interesting post....

https://msdn.microsoft.com/en-us/lib...8VS.85%29.aspx

[SamOscarBrown]

Well, I obviously must bow out to let the experts answer this for you (us)...I have NO idea which to use, or WHY? All I know is I always have successfully used 2.8.

[dilettante]

Wasn't ADO 6.1 the problematic version that broke binary compatibility? Seems as if even the "fix" left it broken, with the advice being to use 6.0 or earlier instead for long term portability. Since the newer interfaces in MSADO15.DLL (the actual workings no matter what "version" you select) offer darned little to Jet or ACE programmers after 2.5, (and as far as I can think of nothing after 2.7) there seems very little reason to ever go higher.

Pretty much the only new stuff there was just for SQL Server and a little for Oracle.

My advice: stick to ADO 2.5 unless you know you need features of 2.6 or 2.7, most of which are unknown to VB6 coders anyway.

Is your database still in Jet 4.0 MDB format? Installing a newer version of Access has nothing to do with "updating" a database. However Access 2007 and beyond did introduce a series of new mutually incompatible ACE ACCDB file formats. If so you need to switch to a compatible ACE Provider, but the ADO version isn't relevant.

Once again, all "versions" of ADO point to the same DLL. Most of them are just typelibs for the different levels of interfaces.

[si_the_geek]

Any "recent" versions of ADO should be fine (I'm assuming 2.7 and later, but could be wrong. edit: dilettante is probably right).

The important thing is that you use an appropriate Provider (which you specify in the connection string), which may have changed depending on the file differences. eg: if you were using Jet to connect to an .mdb file, but switched to a .accdb, you would need to use the ACE provider - which may need to be installed on your users computers.

[dilettante]

And be sure to deploy the ACE Provider you need.

There are at least 3 of them loose in the wild now, and though backward compatibile there is no guarantee of forward compatibility (otherwise why keep making new ones?). The user might have the ACE 12 provider but it may fail trying to work with an ACE 15 format ACCDB.

Since the ACCDB format is mainly meant for use with SharePoint you can avoid it and stick with Jet 4.0 MDBs that work everywhere (even most Windows 95 machines that are still running).

[SamOscarBrown]

since the accdb format is mainly meant for use with sharepoint you can avoid it and stick with jet 4.0 mdbs that work everywhere (even most windows 95 machines that are still running).

great advice!!!

[gibra]

I'm using ADO to connect to an Access 2000 data base by setting a reference to 'Microsoft ActiveX Objects 6.1 Library'. Now my database has been updated to Access 2013. What reference do I have to set?

You need do download and install the appropriate ACE driver (for each computer).

Since the improvements are mainly for MS Access environment, and have no influence on the use 'outside' of the database, such as VB6 programming, it is preferable to use a database version 2003.

This work for any db version, from 2007 to 2013:
Download 2007 Office System Driver: Data Connectivity Components from Official Microsoft Download Center
https://www.microsoft.com/en-US/down....aspx?id=23734

Next you have to change your connection string as below:

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    With cn
        .CursorLocation = adUseClient
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDatabasePath
        .Open
    End With

ADO
theoretically, you can use any ADO version, but if you deply you application keep in mind that ADO 6.x isn't supported in all Windows versions.
By me, use of ADO 2.8 it's enough, which alredy installed in all versions from Windows 2000 and above.

N.B.
It does not matter if your computer is running Office: ACE driver have to be installed anyway.

[krtxmrtz]

Thank you, now it works and I've also switched to 2.8 instead of 6.x as you suggest.

You need do download and install the appropriate ACE driver (for each computer).

Since the improvements are mainly for MS Access environment, and have no influence on the use 'outside' of the database, such as VB6 programming, it is preferable to use a database version 2003.

This work for any db version, from 2007 to 2013:
Download 2007 Office System Driver: Data Connectivity Components from Official Microsoft Download Center
https://www.microsoft.com/en-US/down....aspx?id=23734

Next you have to change your connection string as below:

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    With cn
        .CursorLocation = adUseClient
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDatabasePath
        .Open
    End With

ADO
theoretically, you can use any ADO version, but if you deply you application keep in mind that ADO 6.x isn't supported in all Windows versions.
By me, use of ADO 2.8 it's enough, which alredy installed in all versions from Windows 2000 and above.

N.B.
It does not matter if your computer is running Office: ACE driver have to be installed anyway.