Tables in Current DB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Deadeye - 29 Dec 2005 10:20 GMT

I am working with a DB with 10 tables. Let's say tbla,tblb,tblc,tbld,tble
etc. I have created a form (frm1) and want the combo box (cbo1) within the
form to display the names of the tables within the DB. I also want to do this
in separate combo boxes with queries,forms and reports.

What do I put as my data sources for this?

Thanks in advance.

Reply to this Message

Arvin Meyer [MVP] - 29 Dec 2005 12:03 GMT

Put the following in an SQL window in a query, or use it as the rowsource of
a combo or list box:

Tables:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Type)=1) AND (Not (Left([Name],4))="Msys"));

Queries:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Type)=5) AND (Not (Left([Name],4))="~sq_"));

Forms:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Type)=-32768));

Reports:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Type)=-32764));

All specified objects will be shown, even hidden ones.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

> I am working with a DB with 10 tables. Let's say tbla,tblb,tblc,tbld,tble
> etc. I have created a form (frm1) and want the combo box (cbo1) within the
[quoted text clipped - 4 lines]
>
> Thanks in advance.

Reply to this Message

Deadeye - 29 Dec 2005 12:31 GMT

Arvin,

Thank you for this. It does exactly what I wanted. However, my fault, with
regard to the tables query I have some that are linked to SQL server. How do
I ammend my query to see these?

Thanks once again.

> Put the following in an SQL window in a query, or use it as the rowsource of
> a combo or list box:
[quoted text clipped - 40 lines]
> >
> > Thanks in advance.

Reply to this Message

Douglas J. Steele - 29 Dec 2005 13:03 GMT

Tables that are linked through ODBC are identified as Type = 4, while other
linked tables are Type = 6.

You can alter Arvin's SQL statement to:

SELECT Name
FROM MsysObjects
WHERE Type IN (1, 4, 6)
AND (Not (Left([Name],4))="Msys"))

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Arvin,
>
[quoted text clipped - 53 lines]
>> >
>> > Thanks in advance.

Reply to this Message

Deadeye - 29 Dec 2005 14:39 GMT

Douglas,

Thanks for this. Most helpful got it to run by modifying statement as follows:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Type)=1 Or (MsysObjects.Type)=4 Or
(MsysObjects.Type)=6) AND ((Left([Name],4))<>"Msys"));

Great help. Thanks once again to you and Arvin.

> Tables that are linked through ODBC are identified as Type = 4, while other
> linked tables are Type = 6.
[quoted text clipped - 63 lines]
> >> >
> >> > Thanks in advance.

Reply to this Message

Dirk Goldgar - 29 Dec 2005 17:43 GMT

> Douglas,
>
[quoted text clipped - 17 lines]
>> WHERE Type IN (1, 4, 6)
>> AND (Not (Left([Name],4))="Msys"))

Incidentally, Doug's query would have worked fine except it had an extra
closing parenthesis on the end.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message