How to obtain the names of all tables in an Access DB 1

[kellyto]

Is there way using VBA to obtain the list of names of all tables of a given database?

I would like to display the names of the tables in the current database in a list box so the user can make a selection.

Thanks for your help!

 

[LittleSmudge]

How you do it depends on the version of Access

For A2k
You simply cycle through the Tables collection of the Catalog object.

( And you'll need the ADO Ext 2.5 for DLL & Security library loaded to get that working. )

Something like

Dim cat As Catalog
Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

Dim tbl As Table

For Each tbl In cat.Tables
If tbl.Name Like "MSys*" Then
    ' Skip this line because it is a System Table
Else
    Debug.Print tbl.Name
End If

Replace Debug Print with something that populates a list box's RowSource


'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[RamziSaab]

to populate the list box in the rowsource just put

SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type)=1));

this will put all the tables in the list box

 

[MichaelRed]

hmmmmmmmmmm ... mmmmmmmmm ... mmmmmmmmmmmmmmmm

RamziSaab

Well the request was for ALL tables, but I think kellyto]/b] will be a bit suprised (and -perhaps confusetd by some of the entries. And, the query in-it self doesn't " ... will put all the tables in the list box .... ". It does retrieve them, but w/o context / assignment they do not migrate the results to any where in particular?

Otherwise, it is a reasonable approach until Ms. decides (AGAIN!) to redefine / revise the name, content and acdess rights to the metadata.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RamziSaab]

I am not sure but he can add a criteria e.g. Like "tbl *" if he follows the naming convention.

I used it with a combo box and just used afterupdate to populate a list box with the fields...but maybe there is a simpler way of doing it

 

[kellyto]

Thank you guys for your responses.

LittleSmudge, your solution would solve my problem, but my version of Access 2K doesn't have ADO Ext 2.5 for DLL & Security library avalable in the list of references!

I was wondering how do we get this list of references (the one I have at home is different from the one I have at work, both Access 2000). Do I have to upgrade to a newer version of Access or this reference can be downloaded somewhere?

Thanks for your suggestions!

 

[LittleSmudge]

It's not a mattewr of an upgrade - its a matter of digging in the list of references and finding it.


If, some some strange reason the file is genuinely missing - then on the machine that does have it - click on the reference in the references dialog box and note down the file path and name that appears in the bottom section of the dialog box.
Then simply copy that file onlt a floppy - ( or email it as an attacvhment to yourself ) - then add it to the appropriate folder on the other machine.
Then go back and link it in as usual.

On my machine they are all in :-
C:\Program Files\Common Files\System\ADO\file.name



Graham

 

[kellyto]

I guess I missed it the first time I looked :~/, but I found I have version 2.1. I tried it and it works like a charm!

Thank you all for you help!