getting SQL Server table properties via DAO

[AlanS]

In the following code, there are two "Set dbs =" statements, only one of which is uncommented at a time. When I use the first one, the code works and accurately reports the total number of tables in the current database, along with the name and update date/time of the most recently and least recently modified tables. When I use the second one, it reports zero tables. Using F8 to step through the code, I see that the For Each/Next code segment is not executed at all, making it seem as if there are no tables in the SQL Server database, which in fact contains almost 300 tables.

How can I make this work to read the properties of the SQL Server tables?

Public Sub CountTables()
Dim Counter As Integer, wrk As Workspace, dbs As Database, tdf As TableDef
Dim Earliest As Date, Latest As Date, EName As String, LName As String
Counter = 0
Set wrk = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Workspaces.Append wrk
'Set dbs = CurrentDb
'Set dbs = wrk.OpenDatabase("SQLServerDataSetName", , , ConnectString)
For Each tdf In dbs.TableDefs
With tdf
Counter = Counter + 1
If Counter = 1 Then
Earliest = .LastUpdated
EName = .Name
Latest = .LastUpdated
LName = .Name
Else
If .LastUpdated < Earliest Then
Earliest = .LastUpdated
EName = .Name
End If
If .LastUpdated > Latest Then
Latest = .LastUpdated
LName = .Name
End If
End If
End With
Next tdf
Set dbs = Nothing
MsgBox Format$(Counter) & " ODBC tables found" & vbCrLf & "Earliest: " & _
EName & ", " & Earliest & vbCrLf & "Latest: " & LName & ", " & Latest
End Sub