Enumerate tables in Access Database

I have an Access database to which I am connecting to via ODBC. The connection is fine, but I need to get a list of all the tables in the database. How can I do this? I tried SELECT Name FROM MSysObjects WHERE Type = 1 but it failed.

Any ideas?

Thanks

Isn’t is MSSysObjects ?

Hello William,

Here is some sample code which opens a mdb Microsoft Access Database, creates a listbox, loads the FieldSchema into a recordset from a table called ‘Library’ and adds the field names to the first row of the listbox.

[code] //Getting Information from the Database
Dim db as ODBCDatabase
Dim rs as RecordSet
db = new ODBCDatabase
db.DataSource = “Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\test\Math.mdb;Uid=Admin;Pwd=;”
if db.Connect() then //Was the connection successful?
'MsgBox “Connected”
else
MsgBox "Connection failed: " + db.ErrorMessage
return
end if

//Format the Listbox
Listbox1.HasHeading = true //show the heading
Listbox1.ColumnCount = 3 //make 3 columns
Listbox1.ColumnWidths = “20%, 25%, 55%”
Listbox1.InitialValue = “ID” + chr(9) + “Units” + chr(9) + “Description”

//Get the data from the database
rs = db.FieldSchema(“Library”)

if rs <> Nil then //Continue if there is data
do until rs.EOF //continue until we reach the End Of File
Listbox1.AddRow
Listbox1.Cell(Listbox1.LastIndex, 0) = rs.IdxField(1).StringValue
rs.MoveNext //move to the next recordset
loop //get the next row of data
else
MsgBox “No RecordSet exists.”
Return
end if
rs.Close
db.Close[/code]

Replacing the recordset variable (rs) with db.TableSchema will list all of the tables in an Access Database. (Sorry, copy and paste remembered an older copy and am unable to edit the above forum code).

[code] //Getting Information from the Database
Dim db as ODBCDatabase
Dim rs as RecordSet
db = new ODBCDatabase
db.DataSource = “Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\test\Math.mdb;Uid=Admin;Pwd=;”
if db.Connect() then //Was the connection successful?
'MsgBox “Connected”
else
MsgBox "Connection failed: " + db.ErrorMessage
return
end if

//Format the Listbox
Listbox1.HasHeading = true //show the heading
Listbox1.ColumnCount = 3 //make 3 columns
Listbox1.ColumnWidths = “20%, 25%, 55%”
Listbox1.InitialValue = “ID” + chr(9) + “Units” + chr(9) + “Description”

//Get the data from the database
rs = db.TableSchema

if rs <> Nil then //Continue if there is data
do until rs.EOF //continue until we reach the End Of File
Listbox1.AddRow
Listbox1.Cell(Listbox1.LastIndex, 0) = rs.IdxField(1).StringValue
rs.MoveNext //move to the next recordset
loop //get the next row of data
else
MsgBox “No RecordSet exists.”
Return
end if
rs.Close
db.Close[/code]

Just for my understanding . I am sure I already know the answer but to be sure : This does not worc on a mac where the .mdb file is in it right?

Hi Anthony,

You’d need an appropriate ODBC driver for OS X. This is the only one I know of:

http://www.actualtech.com/product_access.php

They support Xojo:

http://www.actualtech.com/compatibles.php

It’s a commercial product; offers support.

Once installed, you can create a DSN on OS X using the ODBC Manager:

http://www.odbcmanager.net/faq.php

Hope the helps,
Anthony