By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,553 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Is there an SQL equivalent in MS Access for the MySQL command "showcolumns in tablename?"

P: n/a
Dan
Hello, all!

New here, so please forgive if this has been answered before. I use
MySQL most often, and can use the commands:

show tables in [databasename];
show columns in [tablename];

which lists all the tables in the specified database, and columns/
fields in the specified table. Is there an SQL equivalent for use in
MS Access 97? I use that at work, and it'd be helpful to be able to
do. I'd also prefer straight SQL, if possible, and shy away from VBA.

Thanks!

Dan
Dec 31 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Dan wrote:
Hello, all!

New here, so please forgive if this has been answered before. I use
MySQL most often, and can use the commands:

show tables in [databasename];
show columns in [tablename];

which lists all the tables in the specified database, and columns/
fields in the specified table. Is there an SQL equivalent for use in
MS Access 97? I use that at work, and it'd be helpful to be able to
do. I'd also prefer straight SQL, if possible, and shy away from VBA.

Thanks!

Dan
Not really. From the database window you can select from the menu
Tools/Analyze.

Or write a rountine
Sub ListTables
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
Debug.Print tdf.name
End If
Next
MsgBox "done"
End Sub

Sub ListTablesAndFields()
Dim tdf As TableDef
Dim fld As Field
For Each tdf In CurrentDb.TableDefs
Debug.Print tdf.name
For Each fld In tdf.Fields
Debug.Print fld.name
Next fld
Exit For
Next
MsgBox "done"
End Sub

Married
http://www.youtube.com/watch?v=LIwC96ZHHSM
Dec 31 '07 #2

P: n/a
Dan <da*@ecorry.comwrote in news:f08bbdc4-3671-422f-bc60-ced27ba0cf14
@e25g2000prg.googlegroups.com:
Hello, all!

New here, so please forgive if this has been answered before. I use
MySQL most often, and can use the commands:

show tables in [databasename];
show columns in [tablename];

which lists all the tables in the specified database, and columns/
fields in the specified table. Is there an SQL equivalent for use in
MS Access 97? I use that at work, and it'd be helpful to be able to
do. I'd also prefer straight SQL, if possible, and shy away from VBA.

Thanks!

Dan
Interesting post. My news-client shows that you posted it today and that
Salad answered it yesterday. Now that's Promptness

I can't remember a lot about Access 97.

It seems to me that

SELECT Name FROM mSysObjects
WHERE Type = 6"

returns the names of Linked Tables
so I guess that

SELECT Name FROM mSysObjects
WHERE Type = 6"
OR Type = some other number

will give all the table names.

Further, (beyond suspecting) I'm guessing you could mess with the MSys
Tables and get a column list too.

I have never pursued this because Access 97 gave a nice view of all user
tables in the database window and of the columns of any table in table
design view.
So why run SQL to get something that's already there?

--
lyle fairfield
Dec 31 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.