I finally figured out how to query a list of all table names out of MS Access. There is a hidden table called MSysObjects. Many thanks to http://eis.bris.ac.uk/~ccmjs/access_section.html for the information.
To get a list of table names from Access (or form names, or query names, etc.), simply query the MSysObjects table, where type=1. Easy.
8 comments:
Hi There!
First, let me thanks you for bringing this info up, it saved me a lot of time, and gave me a couple ideas I'll try in the next couple days for enhancing a couple DBs I own.
Most of my DBs use linked tables, so I keep Front End logic and Data in different files (for performance and security), so, when I looked for those tables, I didn't find them, so looking at all the table I found out linked tables have type=6.
I hope this helps someone else as your post helped me this time.
i tried to query the msysobject but it returns me this "Record(s)cannot be read; no permission to msyobject".
can you help me how solve this problem?
SELECT NAME FROM MSYSOBJECT
WHERE TYPE = 1
AND NAME NOT LIKE 'MSYS*'
ORDER BY NAME;
The above query populates the list of all user-defined tables of an MS-ACCESS database.
Better:
SELECT Name
FROM MSysObjects
WHERE
Left([Name],1)<>"~"
AND
Left([Name],4)<>"MSys"
AND
Type In (1,4,6)
ORDER BY Name;
@renel: you probably don't have permissions... try with: Tools/security/user and group permisison... set all users, groups to "administer". but then they will all have all privileges....
I have a related question on this topic. I am trying to create a union query of 2+ table based on one field, let's say "PID" but also need to include a second field that contains the name of the table the record came frome, e.g., Table1 and Table2. Union query, easy enough. I tried out this MSYSOBJECT query and that works by itself also.
Thanks for any help.
For the new accdb-format access to the MSysObjects are no longer available. I finally found a way to do it though: http://t.co/8hYWEvfn
Hope it helps someone :)
SELECT NAME FROM MSYSOBJECT
WHERE TYPE = 1
AND NAME NOT LIKE 'MSYS*'
ORDER BY NAME;
The above qry will not work
Just add 'S' i.e MSYSOBJECTS
SELECT NAME FROM MSYSOBJECTS
WHERE TYPE = 1
AND NAME NOT LIKE 'MSYS*'
ORDER BY NAME;
SELECT NAME FROM MSYSOBJECT
WHERE TYPE = 1
AND NAME NOT LIKE 'MSYS*'
ORDER BY NAME;
The above qry will not work unless you add 'S' i.e. MSYSOBJECTS
and corrected qry is shown below
Any thanks for your help
SELECT NAME FROM MSYSOBJECTS
WHERE TYPE = 1
AND NAME NOT LIKE 'MSYS*'
ORDER BY NAME;
Post a Comment