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.