list of table names and fields

[Guest]

is there a way to get a list of table names that are in a database and also
show the fields associated with those tables?

 

[Al Campagna]

dlb,
Use Tools/Analyze/Documenter

 

[CyberDwarf]

Tracy

What type of tables? SQL Server, ODBC linked, Jet?

Steve

 

[Guest]

just regular tables i created in access not linked i guess these would be
considered jet

 

[fredg]

is there a way to get a list of table names that are in a database and also
show the fields associated with those tables?

You can get that information, and more, by using the Access Documenter
(Tools + Analyze + Documenter)
or... you can use the following sub procedure:

Public Sub GetFieldNames()
Dim dbs As Database, tdf As TableDef, fld As Field
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> "MSYS" Then
Debug.Print tdf.Name
For Each fld In tdf.Fields
Debug.Print " " & fld.Name
Next fld
End If
Next tdf
Set dbs = Nothing
End Sub

 

[Jeff Conrad]

This should help (and it's free):

http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html

And also this page for a whole bunch more options:

http://home.bendbroadband.com/conradsystems/accessjunkie/doctable.html

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

in message:

 

[Guest]

where do i put the sub procedure of course in a module then where do i use
it in a query and how would i use it in a query

 

[fredg]

where do i put the sub procedure of course in a module then where do i use
it in a query and how would i use it in a query

You asked for a way to show a list of tables and their field names.
Place the code in a module.
You can then run it directly from the module by placing the cursor
within the code and pressing F5.
Or you could call it from anywhere in the database using code:
Call GetFieldNames

In both instances, you would then read the results in the Debug
Window.

However, I have no idea of what you are intending to do with this
information as your question of "how do I use it in a query" has no
relevance. You wouldn't.

Perhaps you should start again and instead of asking how to show table
and field names, tell us why you need this information.

 

[aaron.kempf]

for the record; this is MUCH easier in SQL Server than it is in Jet /
MDB.

you don't need to program obsolete DAO code; you can just write a
simple data dictionary using a simple SQL Statement

Select SC.Name, (select SO.name from sysobjects so where so.id = sc.id)
as tblName
from Syscolumns SC

try doing THAT in a silly little MDB file; it just doesn't work.

DAO is DED and I for one; am not going to sit idly by while Microsoft
waffles on obsolete libraries

Spit on anyone that uses DAO or MDB


-Aaron

 

[Guest]

i would like to make a table out of this data is this possible

 

[aaron.kempf]

use SQL Server and then make a view out of it

it's super easy; then you can treat it JUST like a table

-Aaron