Write SQL statement to get list of tables in database

[vbc22]

Hi there,

Anyone know the correct syntax for writing up an SQL statement that'll return me a list of tables in a database?

I use ADO code to write SQL statements. I'd like to know what I need to write in the line where I equate the sql string.

Private Sub listDBTables()

    Dim sql As String, rst As New ADODB.Recordset
    
    sql = "SELECT fname, lname, email FROM tCustomerInfo"
    rst.Open sql, CurrentProject.Connection, adOpenKeyset
    
    ' do something with recordset object...
    
    rst.Close
    Set rst = Nothing
    
End Sub

Regards.

 

[PCLewis]

Take a look (and google if you wish) at MSysObjects.

This is a hidden table in your access database that you can query.

 

[vbc22]

Thanks for the tip!

Ended up finding this page, which basically answered my question.

http://www.accessdatabasetips.com/msysobjects.html

 

[cmmrfrds]

Check out the OpenSchema method of ADO.

Set rst = CurrentProject.Connection.OpenSchema(adSchemaTables)

While Not rst.EOF
If rst!table_type = "table" Then
Debug.Print rst!table_name
End If
rst.MoveNext
Wend
rst.Close

 

[easyit]

hi,
this should also work...

SELECT MSysObjects.Type, MSysObjects.Connect, MSysObjects.Database, MSysObjects.DateCreate, MSysObjects.DateUpdate, MSysObjects.Flags, MSysObjects.ForeignName, MSysObjects.Id, MSysObjects.Lv, MSysObjects.LvExtra, MSysObjects.LvModule, MSysObjects.LvProp, MSysObjects.Name, MSysObjects.Owner, MSysObjects.ParentId, MSysObjects.RmtInfoLong, MSysObjects.RmtInfoShort
FROM MSysObjects
WHERE (((MSysObjects.Type)=1));

easyit

 

[mp9]

Don't forget that attached tables have a different Type value - check out the list on

http://www22.brinkster.com/accessory/queries/003.shtml

So, for the sake of completeness, you might want something like:

SELECT * FROM MSysObjects
WHERE Type=1 OR Type=6;