Creating OBBC connection in Access 2000 1

[vaneujl]

In some MS Access 97 applications I developped in the past I was using a VBA code to create a ODBC linked table.
Here is the MS Access97 code.

Dim tdfLinked As TableDef
Dim ConnectString as String

ConnectString = "ODBC;DSN=B5P2;DBALIAS=B5P2;UID=MyLogon;PWD=MyPswd;TABLE=AB5BL001.MPR2"

Set tdfLinked = CurrentDb.CreateTableDef("MPRdb&quot
tdfLinked.Connect = ConnectString
tdfLinked.SourceTableName = "AB5BL001.MPR2"
tdfLinked.Attributes = dbAttachSavePWD
CurrentDb.TableDefs.Append tdfLinked

We are now migrating to MS Access2000. So, the code here above is not more working.

Is somebody can help me converting that code in MS Access2000?

Thanks

 

[danvlas]

If you add a reference to DAO 3.6. Object Library, the code will work fine in Access 2K.

Otherwise, you have to add a reference to ADOX and use 'Catalog.Tables' instead of 'CurrentDb.TableDefs'.
Something like:

Dim cat As New ADOX.Catalog
Dim tdfLinked As New ADOX.Table
cat.ActiveConnection = CurrentProject.Connection
tdfLinked.Name = "MPRDb"
Set tdfLinked.ParentCatalog = cat
tdfLinked.Properties("Jet OLEDB:Create Link&quot = True
tdfLinked.Properties("Jet OLEDB:Link Provider String&quot = _
"ODBC;DSN=B5P2;DBALIAS=B5P2;UID=MyLogon;PWD=MyPswd;TABLE=AB5BL001.MPR2"
tdfLinked.Properties("Jet OLEDB:Remote Table Name&quot = "AB5BL001.MPR2"
cat.Tables.Append tdfLinked
Set cat = Nothing


HTH

Daniel Vlas
Systems Consultant
danvlas@yahoo.com