Tables in Current DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.

Arvin Meyer said:
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

Deadeye said:
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.
Click to expand...
Click to expand...
 
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"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Deadeye said:
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.

Arvin Meyer said:
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

Deadeye said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.

Douglas J. Steele said:
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"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Deadeye said:
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.

Arvin Meyer said:
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
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.
Click to expand...
Click to expand...
Click to expand...
 
Deadeye said:
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.

Douglas J. Steele said:
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"))
Click to expand...
Click to expand...

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