Listing all Field names of each Table in db

  • Thread starter Thread starter Jim Evans
  • Start date Start date
J

Jim Evans

Access 2003; XP Pro

I would like to make a chart of all field names in each table in a db. I
have been trying to do this in VBE and have successfully been able to
achieve one table at a time but I believe that I should be able to iterate
through all user defined tables and print to the immediate window. I can
then copy into text editor and print or have the text file for reference.

Can someone help with this?

Thanks in advance,

Jim
 
Jim Evans said:
Access 2003; XP Pro

I would like to make a chart of all field names in each table in a db. I
have been trying to do this in VBE and have successfully been able to
achieve one table at a time but I believe that I should be able to iterate
through all user defined tables and print to the immediate window. I can
then copy into text editor and print or have the text file for reference.

Can someone help with this?

Thanks in advance,

Jim
Click to expand...

Forget copying & pasting from the debug window. If you want the info in a
text file, you can write to that as you loop. You can then print from
Notepad.

Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim f As Integer

f = FreeFile
Open "C:\Temp\MyFile.txt" For Output As f

For Each tdf In DBEngine(0)(0).TableDefs
Print #f, tdf.Name
For Each fld In tdf.Fields
Print #f, Tab(5);fld.Name
Next
Print #f, ""
Next

Close f
 
Stuart,

For the sake of the knowledge and possibly being able to use it in the
future, how would I send the completed file to the default printer on the
Workstation?

Jim
 
I might make one change to Stuart's code and that would be to eliminate system
tables from printing to the file.

You can test to see if a table is a system table by using
If (tdf.Attributes And dbSystemObject) = 0 Then


Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim f As Integer

f = FreeFile
Open "C:\Temp\MyFile.txt" For Output As f

For Each tdf In DBEngine(0)(0).TableDefs
If (tdf.Attributes And dbSystemObject) = 0 Then
Print #f, tdf.Name
For Each fld In tdf.Fields
Print #f, Tab(5);fld.Name
Next
Print #f, ""
End If
Next

Close f

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Jim Evans said:
Stuart,

For the sake of the knowledge and possibly being able to use it in the
future, how would I send the completed file to the default printer on the
Workstation?

Jim
Click to expand...

Paste the following into the top of a standard module, under any option
statements:

Private Declare Function ShellExecuteA Lib "shell32.dll" _
(ByVal hWnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Then call it like this:

ShellExecuteA Application.hWndAccessApp, "print", _
"C:\Temp\MyFile.txt", vbNullString, vbNullString, 1
 
John Spencer said:
I might make one change to Stuart's code and that would be to eliminate
system tables from printing to the file.
Click to expand...


Thanks John. Shoulda thought of that.
 
John,

I took care of this by looking at the Name of each file in an If...Then
block.

Thanks for your input.
 
This is the applicable portion of the procedure to print thr Field data:
For Each tdf In DBEngine(0)(0).TableDefs
If Not Left(tdf.Name, 4) = "MSys" And Not Left(tdf.Name, 1) = "z" Then
Print #f, tdf.Name;
For Each fld In tdf.Fields
Print #f, Tab(5); fld.Name; Tab(25); fld.Type; fld.Size
Next
Print #f, ""
End If
Next

All is well but the fld.Type is returning an Integer and I would like the
string value of the Field Type. Have searched for some time in the VBE Help
and cannot find a way. Also tried to find a table of Field Type return
values to no avail.

Jim
 
I think the following is correct. I have a version with an error in it that
mis-identifies the field type.

Private Function fGetFieldTypeName(fldAnyType) As String
'=============================================================
'Translate the numeric fldtype to a text field type
'=============================================================
Dim strAny As String
Select Case fldAnyType
Case dbBigInt
strAny = "Big Integer"
Case dbBinary
strAny = "Binary"
Case dbBoolean
strAny = "Boolean"
Case dbByte
strAny = "Byte"
Case dbChar
strAny = "Char"
Case dbCurrency
strAny = "Number (Currency)"
Case dbDate
strAny = "Date/Time"
Case dbDecimal
strAny = "Decimal"
Case dbDouble
strAny = "Number (Double)"
Case dbFloat
strAny = "Number (Float)"
Case dbGUID
strAny = "GUID"
Case dbInteger
strAny = "Number (Integer)"
Case dbLong
strAny = "Number (Long)"
Case dbLongBinary
strAny = "Long Binary (OLE Object)"
Case dbMemo
strAny = "Memo"
Case dbNumeric
strAny = "Numeric"
Case dbSingle
strAny = "Number (Single)"
Case dbText
strAny = "Text"
Case dbTime
strAny = "Time"
Case dbTimeStamp
strAny = "Time Stamp"
Case dbVarBinary
strAny = "VarBinary"
Case Else
strAny = "Unknown Type"
End Select

fGetFieldTypeName = strAny

End Function



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

Jim

John Spencer said:
I think the following is correct. I have a version with an error in it
that mis-identifies the field type.

Private Function fGetFieldTypeName(fldAnyType) As String
'=============================================================
'Translate the numeric fldtype to a text field type
'=============================================================
Dim strAny As String
Select Case fldAnyType
Case dbBigInt
strAny = "Big Integer"
Case dbBinary
strAny = "Binary"
Case dbBoolean
strAny = "Boolean"
Case dbByte
strAny = "Byte"
Case dbChar
strAny = "Char"
Case dbCurrency
strAny = "Number (Currency)"
Case dbDate
strAny = "Date/Time"
Case dbDecimal
strAny = "Decimal"
Case dbDouble
strAny = "Number (Double)"
Case dbFloat
strAny = "Number (Float)"
Case dbGUID
strAny = "GUID"
Case dbInteger
strAny = "Number (Integer)"
Case dbLong
strAny = "Number (Long)"
Case dbLongBinary
strAny = "Long Binary (OLE Object)"
Case dbMemo
strAny = "Memo"
Case dbNumeric
strAny = "Numeric"
Case dbSingle
strAny = "Number (Single)"
Case dbText
strAny = "Text"
Case dbTime
strAny = "Time"
Case dbTimeStamp
strAny = "Time Stamp"
Case dbVarBinary
strAny = "VarBinary"
Case Else
strAny = "Unknown Type"
End Select

fGetFieldTypeName = strAny

End Function



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
 
Thanks Stuart and John. This gives me a piece of reference material I have
thought about for years!

Jim

Thank you, John.

Jim
 
Jim Evans said:
Thanks Stuart and John. This gives me a piece of reference material I have
thought about for years!
Click to expand...
<snip>

You're welcome. How did you fare using ShellExecute?
 
I followed your instructions and called the function from the proc that
creats the text file, just after the file is closed. It went off without a
hitch.

I would like to be more familiar with the Windows API at times like
this...maybe one day.

Jim

Jim Evans said:
Thanks Stuart and John. This gives me a piece of reference material I have
thought about for years!
Click to expand...
<snip>

You're welcome. How did you fare using ShellExecute?
 
The problem is ill defined:

A to B to A to B to A to B to C


as example, is nothing more than A to B to C, where you spent time turning
around (A to B and back to A) a couple of time. And you can get another
solution just by adding another loop A to B to A. Probably not what you
want.


There is a well defined algorithm finding THE shortest path (Dijkstra's
algorithm) between any two destinations (through other intermediate points).

If you want to find a sequence of paths without loop, and having at least
one different arc, chose the unwanted arc, make its distance 'infinite' (or
remove it from the graph) , and recompute the shortest path on that modified
graph.



Vanderghast, Access MVP
 
Back
Top