How to retrieve all the table names in the database?

[Guest_imported]

Does anyone know how to retrieve all the table names available in the access database?

Thanks in advance.

 

[cmmrfrds]

Here is an example through ADO, you can do it through DAO also but I don't have the example handy.


Function ADOTables()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table

Set cg.ActiveConnection = CurrentProject.Connection

For Each tb In cg.Tables
Debug.Print "table = "; tb.Name
Debug.Print "type = "; tb.Type
Next

End Function

 

Thanks. It helps.

 

[jebry]

Hi!

You can also do it in SQL:

Select Name From MySysObjects Where Type = 1 And Left(Name, 5) <> &quot;MySYS&quot;

hth
Jeff Bridgham
bridgham@purdue.edu

 

[fabry]

Can you explain me this SQL statement? I try to use it and I got this error:

Microsoft OLE DB Provider for ODBC Drivers errore &quot;80040e37'

[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'MySysObjects'. Make sure it exists and that its name is spelled correctly.

What is MySysObjects? Why it doesn't exist?
Thans
Fabrizio

 

[DaOtH]

I quess it has to exist.

Go to Tools -> Options -> View and tick 'System Objects' &quot;In three words I can sum up everything I've learned about life: it goes on.&quot;
- Robert Frost 1874-1963