Display source table name in Access 2000

[mackers2]

I have a problem with displaying the source table for a linked table in Access 2000 without using DAO. In Access 97 this works (using DAO)

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("PPList&quot
MsgBox "PPList is linked to " + (tdf.SourceTableName) + Chr(10) + "in" + Chr(10) + Mid((tdf.Connect), 11)

but I need to do it using objects that are automatically referenced in Access 2k modules (Access 9 Object Library and ADO).

Or is there an easy way to make a reference to DAO load when a database is opened?

Thanks

 

[scking]

Of critical importance is not to change the way you are getting this information. You need to tell the system that you are using DAO. I notice you don't even dimesion dbs or tdf unless you aren't showing it to us. If you don't dimension it you have a variant. Is Access 97 the default is DAO, in Access 2000 the default is ADO, so simple resolve that confusion for the system.

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Steve King Growth follows a healthy professional curiosity

 

[mackers2]

Thanks Steve. The problem is not confusion by the system as to the objects, but that I want to do it without using DAO at all, because DAO is not referenced automatically in a new Access 2000 database. So the code you've suggested still doesn't work as it expects the DAO Object Library.

I've tried using CurrentData.AllTables("PPList&quot but the properties don't seem to be accessible.

Thanks for any help

 

[scking]

Many people miss the point. Access 2000 will support BOTH ADO and DAO. Using the default it automatically sets ADO as the reference. If you want to use DAO, and millions should unless they all want to rewrite legacy code, then you simply have to reference the DAO library. While in a code module go to tools\references look for 'DAO 2.6/3.5 Compatability Library' or 'DAO 3.6 Object Library' and select it. Your Access 2000 application will then use the DAO code IF you explicity use it in the dimension statement. And that was the comment I made previously. Besides not wanting to rewrite from DAO to ADO there are also functionality differences between the two and I don't believe anyone should have to rewrite for the sake of rewriting the application.

Steve King Growth follows a healthy professional curiosity

 

[cmmrfrds]

Here is an example of one way to get table information using ADO.

Function TestAdo()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
Dim cn As New Connection
Dim rs As Recordset, connString As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\bigtuna\Databases\Bank.mdb;" & _
"Persist Security Info=False"

cn.ConnectionString = connString
cn.Open connString
Set rs = cn.OpenSchema(adSchemaTables)

While Not rs.EOF
If rs!table_type = "table" Then
Debug.Print rs!table_name
End If
rs.MoveNext
Wend
rs.Close
Set cn = Nothing

End Function

Here is a LINK that show examples of ADO substitutes for DAO code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp