Access97 / 2002 DB

[Giffordw]

I've inherited a project which pulls data from 2 DB (*.mdb), one is access 97 and one is access 2002. DAO 3.6 is referenced. We use OpenDatabase and OpenRecordset instructions. When running, we get "Unrecognized data format" error or error code 3343 when attempting to refresh any table in the access 2002 DB.
Any clues??

[vb5prgrmr]

Switch over to ado or save the 2k2 database in a previous format 2k or 97...

Good Luck

[kinwang2009]

I wonder why people use DAO instead of ADO. I find ADO connection is best to use rather than DAO. If you have some knowledge about ADO connection, you can switch between any number of DB. I prefer ADO instead of DAO.

Thanks

[vb5prgrmr]

kw2k9, just so you know, DAO is faster than ADO when it comes to the older access databases and when using DAO to access certain blob/memo type fields you do not need to use the getchunck method that you would have to do with ADO to get the contents of one of those fields...AND DAO can access any kind of database that ADO can via ODBC Direct! Just so you know...

ALSO, RDO is faster and more reliable than ADO also... Since ADO is a wrapper for both DAO and RDO and thus is slower as it adds another layer of calls...

Just so you know... :)

[Giffordw]

I'll try ODBC Direct and see where it takes me. Thanks.

[hkdani]

I'll try ODBC Direct and see where it takes me. Thanks.

Access97 uses Microsoft DAO 3.51 Object Library as opposed to the 3.6 Object library used by AccessOffice 2002

Your problem probably arises from the fact that you have loaded the a DAO Object Library that will not support the format of the data that you are trying to load. Either that or not loading the appropriate provider when using the ADO object library.

So, you probably should avoid using the DAO object libraries and use the ActiveX Data Objects 2.8 library exclusively since they should work with either version of Access97 or Access 2002.

dim rst as adodb.recordset
dim cn as adodb.connection

Private sub form_load()
     set rst = new adodb.recordset
     set cn = new Adodb.Connection

     With cn
          .Provider = "Microsoft.Jet.OLEDB.3.51"
          .open "c:\My Folder\myfile.mdb" 
     End with
      
     rst.Open "MyTableName", cn, adOpenDynamic, adLockOptimistic
End sub()

Notice the Provider property of the ADODB connection.

You'll probably have to have different functions to load the appropriate provider or use parameters in the function to load the appropriate Provider depending on whether it's Access97 or a later version of Access

Private Function Load_Access ( AccessVersion as Integer) as Boolean
     '   Instantiate your recordset and connection here   
      Select CASE AccessVersion
      Case 97 
           cn.Provider = "Microsoft.Jet.OLEDB.3.51"
      Case 2002
           cn.Provider = "Microsoft.Jet.OLEDB.4.0"
      Case Else
      End Select

      ' Load your recordset here
End Function()

[Giffordw]

Combining the above response and the MSDN article "OLE DB is the Answer" I seem to have resolved my problem. Thank you all.