Obtain List of Tables in Remote dB

[Sorrells]

Greetings!

I have searched here and in the newsgroups to no avail for this small problem. Perhaps someone could enlighten me.

I need to obtain a recordset of all the table names in a remote database that I can use for later parsing and deletion activities.

I am sure I have seen a method to do so, I think in the Access97 Help System but now when needed, I cannot locate it.

Any assistance would be appreciated.

 

[AlanS]

Here's an example, written in Access 97 VBA, which connects to an SQL Server back end, reads the name of each back end table, and displays each name in a message box. See the help screens for details on the arguments for the CreateWorkspace and OpenDatabase methods

Dim wrk As Workspace, dbs As Database, tdf As TableDef
Set wrk = CreateWorkspace("MyWorkspaceName", "MyUserName", "MyPassword", dbUseODBC)
Workspaces.Append wrk
Set dbs = wrk.OpenDatabase("MyODBCDataSourceName", , , "MyConnectString")
For Each tdf In dbs.TableDefs
With tdf
MsgBox .Name
End With
Next tdf
Set tdf = Nothing
Set dbs = Nothing
Set wrk = Nothing
End Sub

 

[Sorrells]

AlanS, Thanks for your reply. After posting, I went over to Tek-Topics forum (http://www.tek-tips.com/) and found a SQL string that works fine in my database. It is below:

SELECT MSysObjects.Name, MSysObjects.Type FROM MSysObjects
WHERE ((MSysObjects.Type)=6);

As I understand it, Type 6 is linked tables and type 1 are internal tables.

But this may not work at all on a remote database. Somehow, I would first have to make it the current or active database. I will work with your code and post my results.

To complicated matters a bit, I am expecting to encounter table names such as:
Task_Ordered9/22/02
Task_Ordered10/2/02
Task_Ordered10/12/02
Task_Ordered10/22/02
My plan is to delete tables with the same prefix name and suffix date older than 3 generations. This is part of a backup routine and is intended to ensure that the backup dB does not grow too large. There are 4 data-tables of different names to be backed-up. The user will back up all when a utility is run.
I am not sure how to code it but thought that I would use an approach like this:

For each record where parsed (name) = "Task_Ordered"
store count in variable
loop

If variable > 3 then
find the oldest table by parsing(date)
delete the table
delete the other 3 tables with this date
end if

At this point I don't know how to delete a remote table and am not sure that I can easily parse the date.

I am creating the date with a concatenation in the TransferDatabase method as noted below.

DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Program Files\HMS\Backup\HMS_Backup.mdb", acTable, "utlExport_Room_Ordered", "Room_Ordered" & Date, False, False

Any advice you might have will be appreciated. I am going to work with your code now.

 

[Sorrells]

AlanS,

Your code worked very ncely. In the procdure I had already declared the database and workspace so here is what ran:

(there is procedure code above this not shown)
' LIST TABLES IN BACKUP DATABASE
'
Dim tdf As TableDef
Set dbs = wspDefault.OpenDatabase("C:\Program Files\HMS\Backup\HMS_Backup.mdb")
For Each tdf In dbs.TableDefs
With tdf
MsgBox .Name
End With
Next tdf

Set tdf = Nothing
Set dbs = CurrentDb
Set wspDefault = Nothing

Exit_cmlBackup_Data_Click:
Exit Sub

I did get several system tables that if you have the technique, I'd like to filter out but I can also work around this as I will be looking for matches to "Task_Ordered".

Thank you very much!!! If you have thoughts on parsing, date comparision, and deletion of the excess table, I would enjoy seeing them.

 

[AlanS]

Parsing the table names is easy, assuming you know exactly what the part before the date is (as seems to be the case here). Using your example (the "Task_Orderedmm/dd/yy" tables, where the part before the date is 12 characters), the following expressing will isolate just the date part (replace TableName with the appropriate string variable, field, expression or property which contains the table's full name):

Right$(TableName, Len(TableName) - 12)

If you store the date part of Table1 in strTable1Date, and the date part of Table2 in strTable2Date, you can compare them as follows:

DateValue(strTable1Date) < DateValue(strTable2Date)

This expression will return True if the date part of Table1's name is earlier than the date part of Table2's name, and False otherwise.

I'm not sure if or how you can delete a table from a non-current database - check the help screen for the DAO Delete method. Also note that deleting a table from an Access database does NOT free the space that the table occupied - you can only reclaim that space and shrink the overall database size by compacting it.