Listing all Field names of each Table in db

[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

 

[Stuart McCall]

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

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

 

[Jim Evans]

Thank you, Stuart. This is exactly what I needed.

Jim

 

[Jim Evans]

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

 

[John Spencer]

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

 

[Stuart McCall]

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

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

 

[Stuart McCall]

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

Thanks John. Shoulda thought of that.

 

[Jim Evans]

John,

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

Thanks for your input.

 

[Jim Evans]

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

 

[John Spencer]

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

 

[Jim Evans]

Thank you, John.

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

 

[Jim Evans]

Thanks Stuart and John. This gives me a piece of reference material I have
thought about for years!

Jim

Thank you, John.

Jim

 

[Stuart McCall]

Thanks Stuart and John. This gives me a piece of reference material I have
thought about for years!

<snip>

You're welcome. How did you fare using ShellExecute?

 

[Jim Evans]

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

Thanks Stuart and John. This gives me a piece of reference material I have
thought about for years!

<snip>

You're welcome. How did you fare using ShellExecute?

 

[vanderghast]

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