By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Printing MS Access Database Structure in ASP

P: n/a
How can I print the database structure, including non default
variables like Requiered, FieldSize, and most important DESCRIPTION. I
want a table like this:

-----------------------------------------------
| Name | Type | Size | Required | Description |
-----------------------------------------------
| ... | ... | ... | ... | ... |
Does anyone know how to do this? I can only print Name and Type as
they are predefined variables. Thanks in advance!

Alternatively, how can I copy my variable descriptions from MS Access'
Design View to another program (SPSS or EXCEL or a text document)? MS
Access doesn't allow copying the description fields (only one at a
time and I have 900 variables in my tables!!) and importing into SPSS
does not include the descriptions. All help is more than welcome!

Cheers!

Patricio
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
pa*************@hotmail.com (Pato Secruza) wrote in message news:<21**************************@posting.google. com>...
How can I print the database structure, including non default
variables like Requiered, FieldSize, and most important DESCRIPTION. I
want a table like this:

-----------------------------------------------
| Name | Type | Size | Required | Description |
-----------------------------------------------
| ... | ... | ... | ... | ... |
Does anyone know how to do this? I can only print Name and Type as
they are predefined variables. Thanks in advance!

Alternatively, how can I copy my variable descriptions from MS Access'
Design View to another program (SPSS or EXCEL or a text document)? MS
Access doesn't allow copying the description fields (only one at a
time and I have 900 variables in my tables!!) and importing into SPSS
does not include the descriptions. All help is more than welcome!

Cheers!

Patricio


Patricio,
cuidate con el nombre "pato"... significa maricon! (Lo juro!)
You can download the DFUtility addin from Danny Lesandrini's
website...

You could use something like this...

Public Sub ShowFieldDescriptions(ByVal strTable As String)
Dim db As DAO.database
Dim tdf As DAO.tabledef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs(strTable)
For Each fld In tdf.Fields
Debug.Print fld.Name, fld.Properties("Description")
Next fld

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

End Sub

If you wanted, you could populate a table with the information, and
send that to Excel or whatever you want that SPSS or whatever can
read...

HTH a little,
Pieter
Nov 12 '05 #2

P: n/a
pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
pa*************@hotmail.com (Pato Secruza) wrote in message news:<21**************************@posting.google. com>...
How can I print the database structure, including non default
variables like Requiered, FieldSize, and most important DESCRIPTION. I
want a table like this:

-----------------------------------------------
| Name | Type | Size | Required | Description |

-----------------------------------------------
| ... | ... | ... | ... | ... |
Does anyone know how to do this? I can only print Name and Type as
they are predefined variables. Thanks in advance!

Alternatively, how can I copy my variable descriptions from MS Access'
Design View to another program (SPSS or EXCEL or a text document)? MS
Access doesn't allow copying the description fields (only one at a
time and I have 900 variables in my tables!!) and importing into SPSS
does not include the descriptions. All help is more than welcome!

Cheers!

Patricio

Public Sub DocumentTable(ByVal strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset

Set db = CurrentDb
DoCmd.RunSQL "DELETE * FROM ztblDocumentation;", dbFailOnError

'--Open the recordset that the documentation data will go into
Set rs = db.OpenRecordset("ztblDocumentation", dbOpenTable)

'--Open the tabledef that will be documented.
Set tdf = db.TableDefs(strTable)
For Each fld In tdf.Fields
rs.AddNew
rs.Fields("TableName") = tdf.Name
rs.Fields("FieldName") = fld.Name
rs.Fields("FieldType") = GetFieldType(fld.Type)
rs.Fields("FieldSize") = fld.Size
rs.Fields("Required") = fld.Required
rs.Fields("Description") = GetFieldDescription(fld.Name,
tdf.Name)
rs.Update
Next fld

rs.Close
Set rs = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

Private Function GetFieldType(ByVal lngType As Long) As String

Select Case lngType
Case dbBoolean
GetFieldType = "Yes/No"
Case dbCurrency
GetFieldType = "Currency"
Case dbDate
GetFieldType = "Date/Time"
Case dbGUID
GetFieldType = "GUID"
Case dbInteger
GetFieldType = "Integer"
Case dbLong
GetFieldType = "Long Integer"
Case dbLongBinary
GetFieldType = "Long Binary"
Case dbMemo
GetFieldType = "Memo"
Case dbNumeric
GetFieldType = "Numeric"
Case dbSingle
GetFieldType = "Single"
Case dbText
GetFieldType = "Text"
Case dbTime
GetFieldType = "Time"
Case dbTimeStamp
GetFieldType = "Date"
Case dbVarBinary
GetFieldType = "VarBinary.... whatever!"
Case Else
GetFieldType = vbNullString
End Select

End Function
Public Function GetFieldDescription(ByVal strField As String, ByVal
strTable As String) As String
On Error GoTo ErrHandler

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs(strTable)
Set fld = tdf.Fields(strField)

GetFieldDescription = fld.Properties("Description")

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

Exit Function

ErrHandler:
'For Each Err In Errors
Err.Clear
'Next Err
GetFieldDescription = ""

End Function

Okay, then you could output the whole thing to HTML... just specify
the table and use the OutputTo function...
Nov 12 '05 #3

P: n/a
Okay, got it working to my satisfaction... although I output the
result to HTML instead of ASP... (just change the outputTo constant to
....ASP)

'--- begin dodgy code....

Public Sub DocumentTable(ByVal strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset

Set db = CurrentDb
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM ztblDocumentation;", dbFailOnError
DoCmd.SetWarnings True

'--Open the recordset that the documentation data will go into
Set rs = db.OpenRecordset("ztblDocumentation", dbOpenTable)

'--Open the tabledef that will be documented.
Set tdf = db.TableDefs(strTable)
For Each fld In tdf.Fields
rs.AddNew
rs.Fields("TableName") = tdf.Name
rs.Fields("FieldName") = fld.Name
rs.Fields("FieldType") = GetFieldType(fld.Type)
If fld.Type = dbText Then
rs.Fields("FieldSize") = fld.Size
End If
rs.Fields("Required") = fld.Required
rs.Fields("Description") = GetFieldDescription(fld.Name,
tdf.Name)
rs.Update
Next fld

rs.Close
Set rs = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

Private Function GetFieldType(ByVal lngType As Long) As String

Select Case lngType
Case dbBoolean
GetFieldType = "Yes/No"
Case dbCurrency
GetFieldType = "Currency"
Case dbDate
GetFieldType = "Date/Time"
Case dbGUID
GetFieldType = "GUID"
Case dbInteger
GetFieldType = "Integer"
Case dbLong
GetFieldType = "Long Integer"
Case dbLongBinary
GetFieldType = "Long Binary"
Case dbMemo
GetFieldType = "Memo"
Case dbNumeric
GetFieldType = "Numeric"
Case dbSingle
GetFieldType = "Single"
Case dbText
GetFieldType = "Text"
Case dbTime
GetFieldType = "Time"
Case dbTimeStamp
GetFieldType = "Date"
Case dbVarBinary
GetFieldType = "VarBinary.... whatever!"
Case Else
GetFieldType = vbNullString
End Select

End Function
Public Function GetFieldDescription(ByVal strField As String, ByVal
strTable As String) As String
On Error GoTo ErrHandler

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs(strTable)
Set fld = tdf.Fields(strField)

GetFieldDescription = fld.Properties("Description")

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

Exit Function

ErrHandler:
'For Each Err In Errors
Err.Clear
'Next Err
GetFieldDescription = ""

End Function
Public Sub OutputTableToHTML(ByVal strOutputPath As String)
DoCmd.OutputTo acOutputTable, "ztblDocumentation", acFormatHTML,
strOutputPath & "\Dox.html", True
End Sub

'---End dodgy Code
Nov 12 '05 #4

P: n/a
Hey Thanks a lot Pieter!

I`ll check it out! I'm sure I'll be able to do it now.
Did you program it as a windows application or as a web script?

Nunca he escuchado lo de Pato... en cuál país aprendiste castellano, o
si eres latino, de cuál país eres?

I appreciate your effort!

Patricio
Nov 12 '05 #5

P: n/a
pa*************@hotmail.com (Pato Secruza) wrote in message news:<21**************************@posting.google. com>...
Hey Thanks a lot Pieter!

I`ll check it out! I'm sure I'll be able to do it now.
Did you program it as a windows application or as a web script?

Nunca he escuchado lo de Pato... en cuál país aprendiste castellano, o
si eres latino, de cuál país eres?

I appreciate your effort!

Patricio


The code? I wrote it myself (well, probably with a lot of input from
this NG, to be totally honest). It would run inside Access... You'd
just put it in a code module...

Oi lo de pato desde hace mucho tiempo... aprendi en la calle, en la
escuela, en la universidad, en Espana... Latino? Yo?!! Soy MUY
rubio... mi padre es holandes.... pero la madre muy yanqui
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.