Re: Is there an SQL equivalent in MS Access for the MySQL command"show columns in tablename?&q

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

Is there an SQL equivalent in MS Access for the MySQL command "show

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

Re: Is there an SQL equivalent in MS Access for the MySQL command "show columns in tablename?&

Dan wrote 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

Re: Is there an SQL equivalent in MS Access for the MySQL command

There's a need to document the existing databases and tables, and I'm
leaning toward a "metabase," a database (in MySQL, of course!) that
details the servers, the databases, the tables and the individual
fields. Over the course of the company's operations, we seemed to have
added a whole slew of tables, and unfortunately, not a lot of
documentation went into it at the time. As a business analyst trying
to make heads or tails of it, I want to try to capture the "thinking
that went into it," if at all possible, by not only cataloguing the
individual components, but also adding explanatory - and searchable! -
notes and keywords. That way, when I want to try to identify the
specific field which - hopefully! - contains the much-needed info to
make a new application work, I can search the metabase, and hopefully
ID potential data resources.

If I can get a comprehensive list of component tables, and then a
comprehensive list of component fields in those tables, I can prep a
data file for dumping into MySQL - which'll save me a whole heap o'
typin'! That's my plan, anyway!

Thanks for the responses! They are much appreciated, believe me!

Dan sends...

On Dec 31 2007, 4:08 pm, lyle fairfield wrote:
> Dan wrote 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

Re: Is there an SQL equivalent in MS Access for the MySQL command "show columns in tablename?&q

You can go tools->analyze->documenter.

Make sure you click on the advanced tab to disable a few of the extra things
that the document are normally includes who when you print out table
definitions. if you don't go into the advanced tab and disabled as
additional information, then you will not get a nice columnar listing.

when you're viewing the resulting report, you can hit the word
buttion, and at that point you have the table def as text.

Here...lets do that right now:

C:\Program Files\RidesXP\Rides_be.mdb
Wednesday, January 02, 2008
Table: tblGPayments
Page: 1

Name Type Size

GroupsMain_id Long Integer 4
Pdate Date/Time 8
Pamount Currency 8
HowPaid Text 1
Pcomments Text 50
CardHolder Text 35


So, as you can see, you can easily print, or even cut/paste the information
into a newsgroup posting as above. I not really sure why you care if this is
a sql solution, or a built in command that simply outputs the above
information....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com