Show List of tables in another database

[Mikeauz]

Hi,

Using Access 2000 I'm trying to display a list of tables in a remote access db either in a listbox or msgbox, without showing all the system tables.

The reason for this is that new tables are added to the remote db and the macro in the first db needs the name of the most recent one to run.

If anyone knows how to get the table name with the most recent creation date in a remote db this would be even better.

Thanks for any help
Mike

 

[faeryfyrre]

To point you in the right direction.

This can all be done with the DAO library.

You will need the following objects:
DAO.Database
DAO.TableDefs
DAO.TableDef
The Access Help documentation will tell you everything you need to use these objects.

point the database object to point to the remote database.
loop through the TableDefs object that is exposed by the database object and examine each TableDef in the collection (use a For Each loop).
For each TableDef object examine it's name property and compare the first 4 chars of it's name to "MSys" (use the Left() and StrComp() string functions).
If the table name does not start with MSys (IE is not a system table) then examine the DateCreated property of the TableDef. Keep a track of the name and datecreated of the most recently created table.




Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[faeryfyrre]

If you can't get it working post your code and i'll look at it for you.

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[flyover789]

create a table with three fields: Name (Char,255), DateCreate (Date), DateUpdate (Date)
The following function inserts into this table the names, created dates and updated dates of the tables in your remotedb

Function list_tables_in_remotedb()
Dim db As Database
Dim db1 As Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset

Set db = OpenDatabase("C:\yourfolder\yourremotedb.mdb")
Set rs = db.OpenRecordset("select m.Name, m.DateCreate, m.DateUpdate " & _
"from MSysObjects m where m.Name not like ""MSys*"" and m.Type = 1;")

DoCmd.RunSQL "delete * from tbl_RemoteTables;"

Set db1 = Application.CurrentDb
Set rs1 = db1.OpenRecordset("tbl_RemoteTables")

rs.MoveLast
rs.MoveFirst
Do Until rs.EOF

rs1.AddNew
rs1.Fields(0).Value = rs.Fields(0).Value
rs1.Fields(1).Value = rs.Fields(1).Value
rs1.Fields(2).Value = rs.Fields(2).Value
rs1.Update

rs.MoveNext

Loop

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

rs1.Close
Set rs1 = Nothing
db1.Close
Set db1 = Nothing

End Function

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

 

[Mikeauz]

Flyover789,

I'm getting the error "No read permission on Myssysobject" on the openrecordset line

Thanks for the help
Mike

 

[Mikeauz]

Everyone,

Worked out a solution to this, a listbox with the following rowsource:

"SELECT * FROM msysobjects IN "C:\Remotedb.mdb