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

[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

[Salad]

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

[lyle fairfield]

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