Query that will return all table names in Access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Query that will return all table names in Access

Author

Topic

bdubyah57
Starting Member

3 Posts

Posted - 2004-11-10 : 18:32:08

I use SQL Server daily. It's no problem using SELECT to return all of the user created table names in a SQL server database. That query would look like this.

SELECT * from sysobjects where xtype = 'u'

Does anyone know an equivalent query in Access that would return all of the table names from a .mdb file?

Thanks,
Brian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-10 : 18:48:20

The best way to get this information is to use some VB. You can modify the following to put the table names into a list or array:

Dim tb As Object
For Each tb In CurrentDb.TableDefs
If Not tb.Name Like "MSys*" Then MsgBox tb.Name
Next
Set tb = Nothing

You can try querying the MSysObjects table (go to Options:View to show system objects) but you'd have to experiment to determine how a table is identified, compared to other object types. Not to mention they'll probably change it in the next version of Access.

EDIT: I forgot to add that you should paste that code into a new module in Access and run it.

bdubyah57
Starting Member

3 Posts

Posted - 2004-11-11 : 10:57:40

You were right on the money with the MSysObjects query. I appreciate your help. I'm doing this query from a C# .NET windows app and needed to display the table names in a combo box so a user could import the selected table. The query is as follows:

Select Name from MSysObjects where LvProp <> null and Type = 1

I'm not sure what LvProp is but it only has a value when the object is a table. When it's a table it holds the value 'Long binary data'.
What is interesting is if you use these variations:

Select Name from MSysObjects where LvProp = 'Long binary data' and Type = 1

OR

Select Name from MSysObjects where LvProp like 'Long' and Type = 1

The query returns nothing. I found that interesting. I'm not sure what data type LvProp is but it appears to be a string.

Thanks again,
Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources