I would like to grab, at the very least, the Field Name, Data Type and Description of each field in a table.

I tried clicking on the upper, left-hand corner of the grid and then copying but when I went to Excel to paste it, it wasn't in the clipboard.

I have a myriad of tables and need to provide a basic table definition, in excel.


Has anyone already done this?



(I don't want to export the data -- just the basic table definition.)

Have you tried Tools/Analyze/Documenter

I have tried that. It's long and overwhelming for the user. Plus it doesn't give me the field description.

It's output looks like:
blnActive
AllowZeroLength: False
Attributes: Fixed Size
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DataUpdatable: False
DisplayControl: 106
Format: Yes/No
GUID: {guid{0486D8F0-97FF-4BFE-BBB6-370CBBF71E83}}
OrdinalPosition: 7
Required: False
SourceField: blnActive
SourceTable: tblReportConfigurations

What I want is (just a basic table definition):
FieldName DataType Description
blnActive Yes/No When True, the report will appear in the list of reports available to the user.

Sorry, can't help you.

I have some script at my other office that sent it to html. I am sure it could be modified to suit.

Here is a small script I have just knocked up that loops throught the fields collection and creates a txt file which you could open in Excel to get the right results.

Function Scripting()

Dim rs As DAO.Recordset
Dim ff As String
ff = "S:\GMSC\Structure.txt"
Open ff For Output As #1
Set rs = CurrentDb.OpenRecordset("tbdDischarges")
For x = 0 To rs.Fields.Count - 1
Print #1, rs.Fields(x).Name & vbTab & rs.Fields(x).Type
Next
Close #1
rs.Close
Set rs = Nothing


End Function


you could put an outside loop to go though all your tables


Note the columns are tab delimited

CodeMaster::cool:

Back in the office today.

Here is the demo of the docuumenter for generating the scheama of a table.

I am sure you could change it to suit your needs.


CodeMaster::cool:

Ive found a pretty and cool Firefox Extension called OutWit Hub at Ghacks.net. It helps you extract HTML tables and export them into an Excel spreadsheet. You can see the article at http://www.ghacks.net/2008/08/09/outwit-hub-firefox-web-collection-tool/

Cheers

I know this is a little late to help you but I found this when trying to do the exact same thing. I was actually working to create a transpose to create SQL table structure, and found the information here most informative. The method I used is as follows

Tools >Analyze>Documenter

True if you do nothing to format the documenter, the documenter will produce alot of extra details, but if you select the options button, you can filter it down nicely to the information you would like.

The method I used produced me
field Name, type and size. I didn't see a method that would import your description though that might take a little more knowhow.

A little late but hey it might help someone else in the furure

Function Scripting()

Dim rs As DAO.Recordset
Dim ff As String
ff = "S:\GMSC\Structure.txt"
Open ff For Output As #1
Set rs = CurrentDb.OpenRecordset("tbdDischarges")
For x = 0 To rs.Fields.Count - 1
Print #1, rs.Fields(x).Name & vbTab & "-" & rs.Fields(x).Properties("Description")
Next
Close #1
rs.Close
Set rs = Nothing


End Function

'I use this with a small change :)
'Thanks for sharing!!! Have nice result!

This works pretty well - I initially found it at the site listed in the comments, and made a few enhancements to get what I wanted. You should be able to just drop this into a module and run it to generate a listing of all your tables and desired properties in Excel.

Happy documenting.

Sub ListTablesAndFields()
'Macro Purpose: Write all table and field names to and Excel file
'Source: vbaexpress.com/kb/getarticle.php?kb_id=707
'Updates by Derek - Added column headers, modified base setting for loop to include all fields,
' added type, size, and description properties to export

Dim lTbl As Long
Dim lFld As Long
Dim dBase As Database
Dim xlApp As Object
Dim wbExcel As Object
Dim lRow As Long

'Set current database to a variable adn create a new Excel instance
Set dBase = CurrentDb
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.workbooks.Add

'Set on error in case there are no tables
On Error Resume Next

'DJK 2011/01/27 - Added in column headers below
lRow = 1
With wbExcel.sheets(1)
.Range("A" & lRow) = "Table Name"
.Range("B" & lRow) = "Field Name"
.Range("C" & lRow) = "Type"
.Range("D" & lRow) = "Size"
.Range("E" & lRow) = "Description"
End With

'Loop through all tables
For lTbl = 0 To dBase.TableDefs.Count
'If the table name is a temporary or system table then ignore it
If Left(dBase.TableDefs(lTbl).Name, 1) = "~" Or _
Left(dBase.TableDefs(lTbl).Name, 4) = "MSYS" Then
'~ indicates a temporary table
'MSYS indicates a system level table
Else
'Otherwise, loop through each table, writing the table and field names
'to the Excel file
For lFld = 0 To dBase.TableDefs(lTbl).Fields.Count - 1 'DJK 2011/01/27 - Changed initial base from 1 to 0, and added type, size, and description
lRow = lRow + 1
With wbExcel.sheets(1)
.Range("A" & lRow) = dBase.TableDefs(lTbl).Name
.Range("B" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Name
.Range("C" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Type
.Range("D" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Size
.Range("E" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Properties("Description")
End With
Next lFld
End If
Next lTbl
'Resume error breaks
On Error GoTo 0

'Set Excel to visible and release it from memory
xlApp.Visible = True
Set xlApp = Nothing
Set wbExcel = Nothing

'Release database object from memory
Set dBase = Nothing

End Sub

I always wanted to download the table def. like that. I copied the codes & put it in a module in Access 2007. When I tried to execute the module, a window of "Microsoft Visual Basic" was popped up with a message of "Compile error: User-defined type not defined". What i should do to run the codes properly? I am a newbee to module. Any help will be appreciated.

Sounds like you might be missing a reference to a library. I don't know which one is needed for this without more investigation, but I have listed the ones I have enabled in this particular db (and attached an image, which I saw I could do after I had already typed them in).

You can set your references by selecting Tools -> References from the VBA editor, then simply check the appropriate boxes.

References:
Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Access database engine object library
Microsoft Scripting Runtime
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Office 12.0 Object Library

Hope that helps.

Derek

It worked like a charm! Thanks very much!

Sure thing - This is something I had been trying to do off and on for a while, so when I found and modified that code I thought maybe it would help someone else like me. Very happy it happened so quickly!

Derek

Function Scripting()

Dim rs As DAO.Recordset
Dim ff As String
ff = "S:\GMSC\Structure.txt"
Open ff For Output As #1
Set rs = CurrentDb.OpenRecordset("tbdDischarges")
For x = 0 To rs.Fields.Count - 1
Print #1, rs.Fields(x).Name & vbTab & "-" & rs.Fields(x).Properties("Description")
Next
Close #1
rs.Close
Set rs = Nothing


End Function

'I use this with a small change :)
'Thanks for sharing!!! Have nice result!

Hi, I am specifically having trouble with calling the description: rs.Fields(x).Properties("Description")
The module works except for that property field. Is there a typo that I am missing?
Thanks!

Function Scripting()

Dim rs As DAO.Recordset
Dim ff As String
ff = "Z:\VanMeter\Structure.txt"
Open ff For Output As #1
Set rs = CurrentDb.OpenRecordset("SCID2")
For x = 0 To rs.Fields.Count - 1
'Print #1, rs.Fields(x).Name & vbTab & rs.Fields(x).Type (this line works fine)
Print #1, rs.Fields(x).Name & vbTab & "-" & rs.Fields(x).Properties("Description")
Next
Close #1
rs.Close
Set rs = Nothing


End Function

I have just written a table analyser that lets you snapshot a backend database

tables/fields - stores datatype, size, default value etc
indexes - stores name, attributes, fields
relations - stores name and details of linked tables

it also has additional functionality to let you use the captured snapshot to update a similar backend - eg - to update a client's verson of the same database to reflect design changes.

I am not giving it away, but I am hoping to be able to market it at a very low price - I am just trying to sort out copy protection issues.

Dave
If you need someone to beta test it for you then send me a copy. Will abide by your IPR.

I have just written a table analyser that lets you snapshot a backend database

tables/fields - stores datatype, size, default value etc
indexes - stores name, attributes, fields
relations - stores name and details of linked tables

it also has additional functionality to let you use the captured snapshot to update a similar backend - eg - to update a client's verson of the same database to reflect design changes.

I am not giving it away, but I am hoping to be able to market it at a very low price - I am just trying to sort out copy protection issues.

Do you have a website with screenshots you might provide?