Creating a code book from Access 2003

**fredg** · Jun 27 '08, 07:23 PM

Re: Creating a code book from Access 2003

On Tue, 06 May 2008 16:13:04 GMT, mlthomas007 wrote:

Hi,
>
A client asked for a code book (all fields, descriptions, tables, etc.) from
our Access database. Has anyone had to do this? It seems to me there must
be a way to extract all this information from Access.
>
Any assistance would be wonderful.
>
Thanks.

is this what you want?

Tools + Analyze + Documenter
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

**Larry Linson** · Jun 27 '08, 07:23 PM

Re: Creating a code book from Access 2003

A third-party tool, Total Access Analyzer, from FMS, Inc.
(htttp://www.fmsinc.com) will provide more information than you or your
client is likely ever to want to go through. You can find a free
documentation tool at the Access Junkie site,
http://www.accessmvp.com/JConrad/accessjunkie.html, specifically at
http://www.accessmvp.com/JConrad/acc.../csdtools.html. There are
others, but these are two that provide extensive documentation.

Larry Linson
Microsoft Office Access MVP

"mlthomas00 7" <u43434@uwewrot e in message news:83c0e43c9e dd0@uwe...

Hi,
>
A client asked for a code book (all fields, descriptions, tables, etc.)
from
our Access database. Has anyone had to do this? It seems to me there
must
be a way to extract all this information from Access.
>
Any assistance would be wonderful.
>
Thanks.
>

**mlthomas007 via AccessMonster.com** · Jun 27 '08, 07:23 PM

Re: Creating a code book from Access 2003

Thanks I will check it out.

Larry Linson wrote:

>A third-party tool, Total Access Analyzer, from FMS, Inc.
>(htttp://www.fmsinc.com) will provide more information than you or your
>client is likely ever to want to go through. You can find a free
>documentatio n tool at the Access Junkie site,
>http://www.accessmvp.com/JConrad/accessjunkie.html, specifically at
>http://www.accessmvp.com/JConrad/acc.../csdtools.html. There are
>others, but these are two that provide extensive documentation.
>
Larry Linson
Microsoft Office Access MVP
>

>Hi,
>>

>[quoted text clipped - 7 lines]

>>
>Thanks.

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200805/1

**mlthomas007 via AccessMonster.com** · Jun 27 '08, 07:23 PM

Re: Creating a code book from Access 2003

Hi,

We need to pull the tables, fields, etc with descriptions and vaules.

Thanks

fredg wrote:

>Hi,
>>

>[quoted text clipped - 5 lines]

>>
>Thanks.

>
>is this what you want?
>
>Tools + Analyze + Documenter

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200805/1

**timmg** · Jun 27 '08, 07:24 PM

Re: Creating a code book from Access 2003

On May 6, 11:13 am, "mlthomas00 7" <u43434@uwewrot e:

Hi,
>
A client asked for a code book (all fields, descriptions, tables, etc.) from
our Access database. Has anyone had to do this? It seems to me theremust
be a way to extract all this information from Access.

I use a report called tblFields with the following code. The report
groups on table names and shows the field descriptions. Sometimes
this is eough for the client, sometimes I send it into Word and
explain a little more what's going on. I can copy the report to any
application for a quick look of what's what.

Private Sub Report_Open(Can cel As Integer)
' Arvin Meyer 12/7/1995
' Modified 3/17/2000 Tim Mills-Groninger
' Finds and lists all fields in all tables

On Error Resume Next
Me.CreateNewTab leWithChecking = Null 'check for tblFields, make if not
present

Dim db As Database
Dim rst As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim intI As Integer
Dim intJ As Integer
Dim strDesc As String

Set db = CurrentDb()
' Clean out the "working" table tblFields
db.Execute "Delete * From tblFields"
' Open the table for data entry
Set rst = db.OpenRecordse t("tblFields" , DB_OPEN_TABLE,
DB_APPENDONLY)
' Outer loop for tables
For intI = 0 To db.TableDefs.Co unt - 1
Set tdf = db.TableDefs(in tI)
' Skip system tables
If Left(tdf.Name, 4) <"MSys" Then
' Now loop through fields
For intJ = 0 To tdf.Fields.Coun t - 1
Set fld = tdf.Fields(intJ )
rst.AddNew
rst!TableName = tdf.Name
rst!FieldName = fld.Name
rst!FieldNumber = fld.OrdinalPosi tion
Select Case fld.Type
Case dbDate
rst!DataType = "Date/Time"
Case dbText
rst!DataType = "Text"
Case dbMemo
rst!DataType = "Memo"
'Case dbHyperlinkFiel d
' rst!DataType = "Hyperlink"
Case dbBoolean
rst!DataType = "Yes/No"
Case dbInteger
rst!DataType = "Integer"
Case dbLong
rst!DataType = "Long Integer"
Case dbCurrency
rst!DataType = "Currency"
Case dbSingle
rst!DataType = "Single"
Case dbDouble
rst!DataType = "Double"
Case dbByte
rst!DataType = "Byte"
Case dbLongBinary
rst!DataType = "OLE Field"
Case Else
rst!DataType = "Unknown"
End Select
strDesc = ""
' The Resume Next will avoid an error if there is no
Description
strDesc = fld.Properties( "Descriptio n")
rst!Description = IIf(IsNull(fld. Properties("Des cription")),
"", strDesc)
rst.Update
Next intJ
End If
Next intI
rst.Close
End Sub

Function CreateNewTableW ithChecking()
' check for table fields, and if not present, make one
' Helen Feddema - the Access Archon, modified from her column
' in Woody's Access Watch Vol2 No11

On Error GoTo ErrorHandler

Dim dbs As DAO.Database
Dim strTable As String
Dim strSQL As String
Dim tdf As TableDef
Dim lngResult As Long

Set dbs = CurrentDb
strTable = "tblFields"
Set tdf = dbs.TableDefs(s trTable)
'dbs.TableDefs. Delete strTable

CreateNewTable: 'Runs only if "tblFields" does not exist

strSQL = "CREATE TABLE " & strTable & "(TableName TEXT (50),
FieldName TEXT (50), "
strSQL = strSQL + " FieldNumber INTEGER, DataType TEXT (50),
DefaultValue TEXT (50)"
strSQL = strSQL + " , Description TEXT (150), Constraint myCon
Primary Key (TableName, FieldName));"

dbs.Execute strSQL

ErrorHandlerExi t:

Exit Function

ErrorHandler:

Select Case Err.Number
Case 3265
GoTo CreateNewTable
Case Else
'MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description
Resume ErrorHandlerExi t
End Select

End Function

**mlthomas007 via AccessMonster.com** · Jun 27 '08, 07:24 PM

Re: Creating a code book from Access 2003

Hi,

Is there a way to get the Values as well?

Thanks.

Mlthomas007

Larry Linson wrote:

>A third-party tool, Total Access Analyzer, from FMS, Inc.
>(htttp://www.fmsinc.com) will provide more information than you or your
>client is likely ever to want to go through. You can find a free
>documentatio n tool at the Access Junkie site,
>http://www.accessmvp.com/JConrad/accessjunkie.html, specifically at
>http://www.accessmvp.com/JConrad/acc.../csdtools.html. There are
>others, but these are two that provide extensive documentation.
>
Larry Linson
Microsoft Office Access MVP
>

>Hi,
>>

>[quoted text clipped - 7 lines]

>>
>Thanks.

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200805/1

**Larry Linson** · Jun 27 '08, 07:24 PM

Re: Creating a code book from Access 2003

"mlthomas00 7 via AccessMonster.c om" <u43434@uwewrot e in message
news:83d9818984 62f@uwe...

Is there a way to get the Values as well?

Multiple methods are provided, directly in Access, to display, print, and/or
export the data -- no special tools are required. Assuming this is a
production application, the data will be transitory, and that data dump will
be accurate only until the next deletion, update, or addition.

Larry Linson
Microsoft Office Access MVP

Creating a code book from Access 2003

Creating a code book from Access 2003

Comment

Comment

Comment

Comment

Comment

Comment

Comment