Get data from an Access 2000 mdb using 97

[smandoli9]

An Access 2k database (on a networked workstation) has the data I need. I am using Access 97. I can't link the needed table ("unrecognized format&quot.

Having gotten a beginning mastery of VBScript, it seems reasonable to think I can open the 2k mdb/table as a DAO object in VBA. But I haven't been able to do this using GetObject.

(I even went to see if an ODBC link would work ... back to the linking approach ... no success.)

Surely there's a way?!

I have a hilarious work-around but I really hope it doesn't come to this. Create an accessory database in Access 2k. Link tables to my source mdb (2k) and to my target mdb (97). Make some code that transfers the data regularly, from one linked table to the other. Keep this accessory database running in the background.

Spare me this fate if you can!

S. Mandoli

 

[dds82]

If you're writing a VBA module just to open this database, you still won't be able to view it in Access 97 natively. You'll need to make your own forms to be able to view its data; those forms will need to be controlled by your VBA functions to get anything done with that data. Essentially, you're rewriting Access.

The easiest solution? Upgrade your PC to Access 2k.

 

[smandoli9]

dds82:

Thanks for reply. I knew about the "easiest solution," it's a matter of cost of license. Also, if I had the 2000 table open as an object, I would have everything I need to update my native-held data, so I was not sure the part about forms is relevant.

I very much appreciate your time and courtesy.

-- SM

 

[mackers2]

I was intrigued by the problem and remembered that when we changed databases on our intranet from access 97 to 2000 none of the code needed changing in the asp scripts - so I figured that the ADO stuff can access both 97 and 2000 without change.

Using the samples from:

http://msdn.microsoft.com/library

Data Access
DAO
Technical Articles
Migrating from DAO to ADO

I used this code in an Access 97 module to read data from an Access 2000 database (you need to set a reference to the ADO object library first)

Sub ADOOpenJetDatabaseReadOnly()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field

' Open shared, read-only
cnn.Mode = adModeRead
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=baxters\baxters2k.mdb;"

Rem this database is an Access 2000 database

MsgBox ("opened baxters2k&quot

rst.Open _
"SELECT * FROM patinvh ", _
cnn, adOpenForwardOnly, adLockReadOnly
Rem patinvh is a table in the Access 2k database

' Print the values for the fields in
' the first record in the debug window
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next

Debug.Print

' Close the recordset
rst.Close
cnn.Close
End Sub

Does this help? Or have I got the wrong end of the stick?
Anyway thanks for the interesting problem

 

[smandoli9]

Mackers2, Thanks so much for your reply. I'm afraid I haven't been able to try to implement it due to another set of crises barging in.

I also ended up with a POTENTIAL ALTERNATE solution based on MS Knowledge Base 247440, 244040. Involves redirecting the ODBC calls to Jet 3.5 versions. Looks scary, if I'm able to address these issues again I'll try Mackers ADO solution first.