Print the table structure 4

[MyLifeServices]

I think I have a simple question. How do I print the table structure of an Acess database. In the older version of Access I could print the table definition. I am lost now....

 

[tlbroadbent]

In Access, select Tools | Analyze | Documenter from the main menu. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Quehay]

MLS:

I've never liked the Documenter that much--tons of paper and the results aren't that great. Here's a routine (second routine to show field data types is called) that will create a table with your tabledefs. Each time you run it, it will expunge the existing temp table and create a new one so that any changes will be reflected (the error of trying to DROP a non-existent table is handled). Just to be a show-off I've included a simple routine that will drop all of your query object SQL into the debug window--just copy results to the clipboard, and drop them in Notepad.

HINT: Query the resulting table and create a report that shows this info. I like this much better than the documenter. You can play with this as needed--learn the DAO object model and you'll be rewarded with a wealth of info (it's friendlier than the ADOX catalog object).

[tt]

Public Function ShowTableDefs() As Boolean
On Error GoTo Err_ShowTableDefs

Dim db As DAO.Database
Dim Rst As DAO.Recordset
Dim Tdf As DAO.TableDef
Dim Fld As DAO.Field

Dim strDbName As String, strTblName As String, strFldName As String
Dim strSQL As String

'Delete existing copy of table
strSQL = "DROP TABLE TableDefs;"

DoCmd.RunSQL strSQL


strSQL = "CREATE TABLE TableDefs"
strSQL = strSQL & " (TableName TEXT(30), FieldName TEXT(30), FieldData TEXT(15),"
strSQL = strSQL & "FieldSize TEXT(5), DefaultValue TEXT(30), IsPrimary TEXT(10),"
strSQL = strSQL & "IsRequired TEXT(20));"

DoCmd.RunSQL strSQL

Set db = CurrentDb()

Set Rst = db.OpenRecordset("TableDefs&quot

With Rst
For Each Tdf In db.TableDefs
If Left(Tdf.Name, 4) <> &quot;Msys&quot; Then

For Each Fld In Tdf.Fields
.AddNew
!TableName = Tdf.Name
!FieldName = Fld.Name
!FieldData = FieldType(Fld.Type)
!FieldSize = Fld.Size
!DefaultValue = Fld.DefaultValue

If Fld.Required Then
!IsRequired = &quot;Required&quot;
End If

.Update
Next
End If
Next
.Close
End With

ShowTableDefs = True

Exit_Err_ShowTableDefs:
Set Tdf = Nothing
Set Rst = Nothing
Set db = Nothing
Exit Function

Err_ShowTableDefs:
Select Case Err.Number
'The error if table doesn't exist already
Case 3371
Resume Next
Case Else
MsgBox Err.Number & &quot;: &quot; & Err.Description
Resume Exit_Err_ShowTableDefs
End Select

End Function
Public Function FieldType(v_fldtype As Integer) As String

Select Case v_fldtype
Case dbBoolean
FieldType = &quot;Boolean&quot;
Case dbByte
FieldType = &quot;Byte&quot;
Case dbInteger
FieldType = &quot;Integer&quot;
Case dbLong
FieldType = &quot;Long&quot;
Case dbCurrency
FieldType = &quot;Currency&quot;
Case dbSingle
FieldType = &quot;Single&quot;
Case dbDouble
FieldType = &quot;Double&quot;
Case dbDate
FieldType = &quot;Date&quot;
Case dbText
FieldType = &quot;Text&quot;
Case dbLongBinary
FieldType = &quot;LongBinary&quot;
Case dbMemo
FieldType = &quot;Memo&quot;
Case dbGUID
FieldType = &quot;GUID&quot;
End Select

End Function
Private Sub ShowQueryDefs()
On Error GoTo Error_ShowQueryDefs
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb



For Each qdf In db.QueryDefs

Debug.Print qdf.Name & vbCrLf & vbCrLf & qdf.SQL & vbCrLf & vbCrLf

Next


Exit_Error_ShowQueryDefs:
Set qdf = Nothing
Set db = Nothing
Exit Sub

Error_ShowQueryDefs:
MsgBox Err.Number & &quot;: &quot; & Err.Description
Resume Exit_Error_ShowQueryDefs
End Sub

[/tt]

 

[tlbroadbent]

Quehay,

Thanks for the excellent routine. In Access 2000 the table drop error wasn't handled but was easy to fix - error code 3376 instead of 3371. I especially like the query definition print routine.

I suggest that you create a FAQ for this routine. It will be helpful to many. One of the regular questions in these forums regards documentation of a database and objects.

You definitely earned at least one star. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[MyLifeServices]

I thank you very much for the help I'll use it!

 

[Lightning]

Quehay

Love the Query documentor. So straightforward and so time-saving!

Here's another star.

Lightning

 

[Quehay]

Terry,

That's interesting about the error--I use this in 2000. I wonder why the discrepancy?? (Thanks for the nice feedback)

 

[Rosydeal]

Is there an ADO equivalent of this code available somewhere? If so, please provide the link. Thanks

 

[paron]

(Bump) Yeah, anyone done this in ADO?

Ron

 

[khood]

I suggested this free shareware documenter to another person in the tables and relationships forum:

http://www.aadconsulting.cjb.net/ezydoc.html

(I am not affiliated in any way with this company).

I got tired of settling for the access documenter and needed something that I could use without having to code my own, especially when I'm working in various databases (data-only, for example). It does what I need. They have lots of other shareware, but I haven't tried any of them.

Good luck,

K.Hood

 

[Quehay]

You'd use the ADOX library to do this in ADO. (Reference: &quot;ADO Ext 2.1 for DDL & Security&quot. I haven't worked out an ADO version--DAO is so much more convenient for documenting Access objects. I always think about the loss of support by Microsoft(&quot;We're going to stop supporting DAO, blah blah...as soon as we get ADO together...&quot, however authors like Getz & Litwin continue to publish DAO object routines, so if they're doing it I'll worry about this later when Access.NET2005 comes out. ;-) Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[paron]

Thanks, everyone, for the responses. For those of you who have marked this thread, or found it &quot;trying&quot; to document a database using only ADO, there is a dandy whitepaper at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wp/htm/wpmigratingschema.asp

which makes it not horrible. Honest!

Jeff -- DAO is easier, and more intuitive, I agree. However, we are doing a major change at our agency, moving from Win98 to 2k, Access97 backends to SQLServer, and Access97 frontends to Access2002. I decided that if I didn't switch away from Jet to ADO at the same time, skipping DAO altogether, that I was in serious danger of developing lifelike qualities.

No danger now!!

Ron

 

[GGleason]

Here is my contribution to Quehay’s spectacular code, since it would not make the table if it did not exist in the first place. This code replaces the area from “'Delete existing copy of table” to the first occasion of “DoCmd.RunSQL strSQL” inclusive.
[tt]
'Delete existing copy of table if it exists

If DCount(&quot;[Name]&quot;, &quot;MSysObjects&quot;, &quot;[Name] = &quot;&quot;TableDefs&quot;&quot; And [Type] = 1&quot = 1 Then
strSQL = &quot;DROP TABLE TableDefs;&quot;
DoCmd.RunSQL strSQL
End If
[/tt]
Thanks,
GGleason