How to get table's name and field's name?

[XS]

When I get 300 tables from SQL database, I want to get a report of each
table's name and what contents they have. Is there any function or using VBA
to get table's and field's name???

Thank you very much!

 

[Stefan Hoffmann]

hi,

When I get 300 tables from SQL database, I want to get a report of each
table's name and what contents they have. Is there any function or using VBA
to get table's and field's name???

Define "when I get"...

If you have a .mdb with linked tables use DAO.TableDefs to enumerate
them, e.g.

Dim td As DAO.TableDef

For Each td In CurrentDb.TableDefs
Debug.Print td.Name, td.Connect
Next td

If you need to inspect the SQL Server database itself run a query
against INFORMATION_SCHEMA.TABLES in your database, e.g.

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

You may also link this view in your .mdb.

Or you use sys.all_objects:

SELECT *
FROM sys.all_objects
WHERE type_desc = 'USER_TABLE'

or better sys.tables:

SELECT *
FROM sys.tables


mfG
--> stefan <--

 

[Klatuu]

The Database Documenter will give you all that information and more, if you
want it.

 

[John Spencer MVP]

And to get the field names you will need to expand that a bit
If using VBA then the snippet is like:


Dim td As DAO.TableDef
Dim fldAny as Field

For Each td In CurrentDb.TableDefs
Debug.Print td.Name, td.Connect
With each fldAny in td.Fields
Debug.Print fldAny.Name
Next fldAny
end with
Next td

Or you could use the Documenter (Tools: Analyze: Documenter) to list the
tables and all the associated fields. Use the option button to restrict what
is returned in the report.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[kc-mass]

Try this it will put out the table and field names to an Excel file:

Sub TableAndFieldList()
Dim lngTable As Long
Dim lngField As Long
Dim db As Database
Dim xlApp As Object
Dim wbExcel As Object
Dim ws As Worksheet
Dim lngRow As Long
Set db = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Add
lngRow = 1
On Error Resume Next
'Put out some column Headers
With wbExcel.Sheets(1)
.Range("A" & lngRow) = "Table"
.Range("B" & lngRow) = "FieldName"
.Range("C" & lngRow) = "FieldLen"
.Range("D" & lngRow) = "FieldType"
End With
Set ws = wbExcel.Sheets(1)
With ws.Range("A11").Font
.Bold = True
.Name = "MS Sans Serif"
.Size = 8.5
End With
ws.Range("A11").HorizontalAlignment = xlCenter
ws.Range("A11").Interior.ColorIndex = 15


ws.Range("A11").Borders(xlDiagonalDown).LineStyle = xlNone
ws.Range("A11").Borders(xlDiagonalUp).LineStyle = xlNone
With ws.Range("A11").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With ws.Range("A11").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

ws.Range("A2").Select
xlApp.Windows(1).FreezePanes = True

'Loop through all tables
For lngTable = 0 To db.TableDefs.Count
'Do nothing if temporary or system table
If Left(db.TableDefs(lngTable).Name, 1) = "~" Or _
Left(db.TableDefs(lngTable).Name, 4) = "MSYS" Then
Else
'Loop through each table, writing the table and field names
'to an Excel file
For lngField = 0 To db.TableDefs(lngTable).Fields.Count - 1
'For lngField = 0 To 2
lngRow = lngRow + 1
With wbExcel.Sheets(1)
.Range("A" & lngRow) = db.TableDefs(lngTable).Name
.Range("B" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Name
.Range("C" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Size
.Range("D" & lngRow) =
db.TableDefs(lngTable).Fields(lngField).Type
End With
Next lngField
lngRow = lngRow + 2
End If
Next lngTable
'Errors back in effect
On Error GoTo 0
ws.Columns("A:B").Select
ws.Columns("A:B").EntireColumn.AutoFit
'Set Excel to visible so user can save or let go
xlApp.Visible = True
Set xlApp = Nothing
Set wbExcel = Nothing
Set db = Nothing

End Sub


Regards

Kevin

 

[XS]

Hi Stefan,

Thanks very much!

I use "External Date" to import those tables from ODBC Databse. I think
those are .accdb files.

XS

 

[XS]

Hey kc-mass:

Thanks very much!!! that's what I want!

But when I use the code, it comes out
---------------------------
Compile error:

User-defined type not defined
---------------------------
for "Dim ws As Worksheet"

What should I do to fix that?

Thanks!!!!!!!!!!
XS

 

[Douglas J. Steele]

Go into Tools | References while in the VB Editor and make sure that the
entry for Microsoft DAO 3..6 Object Library is checked.

 

[XS]

I checked "Microsoft DAO 3..6 Object Library ". But still has the same error
for "Dim ws As Worksheet"....Is there anything else should be check?

I tried to delet the line of "Dim ws As Worksheet". It can be RUN and I get
a Excel file.

Thanks very much!
XS

 

[Hans Up]

Compile error:

User-defined type not defined

Go into Tools | References while in the VB Editor and make sure that the
entry for "Microsoft Excel <version number> Object Library" is checked.

 

[Douglas J. Steele]

Sorry: mental lapse. I read Workspace for Worksheet.

Change

Dim ws As Worksheet

to

Dim ws As Object.

Now, the fact that deleting the line of code worked for you indicates that
you haven't told Access that all variables must be declared. In my opinion,
that's a huge mistake. Not requiring declaration of all variables can make
it difficult to find typos and other subtle errors in your code.

Each code module should have a line

Option Explicit

at the top (usually the second line, after Option Compare Database). You'll
have to add that line manually to all existing modules, but you can set up
the VB Editor to add that line automatically to all future modules by
selecting Tools | Options while in the VB Editor, finding the Module tab (I
believe it is) and ensuring that the "Require Variable Declaration" check
box is checked.

 

[XS]

It works! Thanks!

Sorry: mental lapse. I read Workspace for Worksheet.

Change

Dim ws As Worksheet

to

Dim ws As Object.

Now, the fact that deleting the line of code worked for you indicates that
you haven't told Access that all variables must be declared. In my opinion,
that's a huge mistake. Not requiring declaration of all variables can make
it difficult to find typos and other subtle errors in your code.

Each code module should have a line

Option Explicit

at the top (usually the second line, after Option Compare Database). You'll
have to add that line manually to all existing modules, but you can set up
the VB Editor to add that line automatically to all future modules by
selecting Tools | Options while in the VB Editor, finding the Module tab (I
believe it is) and ensuring that the "Require Variable Declaration" check
box is checked.

 

[kc-mass]

The gentleman who suggested setting a reference to the Excel library is
correct. That will solve the problem.

Regards

Kevin

 

[Douglas J. Steele]

While setting the reference to Excel will solve the problem, all the rest of
the code is consistent with Late Binding, so there's no need to set a
reference. You can only set a reference to a single version of Excel, so if
your users have a mix of versions installed, you'll run into problems.
Changing the declaration from Worksheet to Object will work with no
reference required, which means it's more flexible.

 

[Hans Up]

While setting the reference to Excel will solve the problem, all the rest of
the code is consistent with Late Binding, so there's no need to set a
reference. You can only set a reference to a single version of Excel, so if
your users have a mix of versions installed, you'll run into problems.
Changing the declaration from Worksheet to Object will work with no
reference required, which means it's more flexible.

The code includes several Excel constants (xlCenter, xlNone, xlEdgeTop,
etc.) Using those named constants requires a reference to Excel. If he
wants to eliminate the Excel reference he should revise the subroutine
to replace the named constants with the corresponding values.

 

[Douglas J. Steele]

The code includes several Excel constants (xlCenter, xlNone, xlEdgeTop,
etc.) Using those named constants requires a reference to Excel. If he
wants to eliminate the Excel reference he should revise the subroutine to
replace the named constants with the corresponding values.

You're right. I obviously missed the use of the constants.

On the other hand, the OP did report that changing the declaration to As
Object solved the problem, so perhaps the constants have been declared
already.

 

[XS]

Hi kc-mass,

Thank you very much for your code. It really help.

Do you know how can I get the first record of each table?

I tried to use
"
..Range("E" & lngRow) = db.TableDefs(lngTable).Fields(lngField).**
"
is there any function to get the fist row?

Many thanks!

 

[John W. Vinson]

Thank you very much for your code. It really help.

Do you know how can I get the first record of each table?

I tried to use
"
.Range("E" & lngRow) = db.TableDefs(lngTable).Fields(lngField).**
"
is there any function to get the fist row?

Access tables should be viewed as unordered "heaps" of data. A table has no
defined sort order, so there really is no "first" or "last" record. If you
want to see records in a particular order you must use a Query based on the
table with a sort order specified.

 

[XS]

Actually, I do not want to sort my data. I just want to get a sample data
from each table. Now I have already get the table's name and field's name, I
want it to be like this:
TableName FieldName RecordSample
PolicyFile ad_ID ????

Since I need to report on 600+ tables' name and contents. So I want to get
the sample of record for each table automaticlly.

Thanks!

 

[Douglas J. Steele]

The TableDef object does not let you get at the data contained within a
table.

You'd need either to create a recordset that returns one or more rows of
data and loop through the fields in that recordset, or do a number of
DLookups on the table.