How to export a table definition into Excel (1 Viewer)

RBetz

New member
Local time
Yesterday, 21:14
Joined
Jun 20, 2008
Messages
5
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.)
 

neileg

AWF VIP
Local time
Today, 03:14
Joined
Dec 4, 2002
Messages
5,975
Have you tried Tools/Analyze/Documenter
 

RBetz

New member
Local time
Yesterday, 21:14
Joined
Jun 20, 2008
Messages
5
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.
 

DCrake

Remembered
Local time
Today, 03:14
Joined
Jun 8, 2005
Messages
8,634
Simple Software Solutions

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.

Code:
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:
 

DCrake

Remembered
Local time
Today, 03:14
Joined
Jun 8, 2005
Messages
8,634
Simple Software Solutions

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:
 

Attachments

Mattster

Registered User.
Local time
Yesterday, 19:14
Joined
Jul 9, 2009
Messages
21
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
 

nhtuan

New member
Local time
Yesterday, 19:14
Joined
Dec 23, 2010
Messages
9
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!
 

djknorr75

New member
Local time
Yesterday, 21:14
Joined
Jan 27, 2011
Messages
3
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.

Code:
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
 

accessfever

Registered User.
Local time
Yesterday, 19:14
Joined
Feb 7, 2010
Messages
101
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.
 

djknorr75

New member
Local time
Yesterday, 21:14
Joined
Jan 27, 2011
Messages
3
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
 

Attachments

djknorr75

New member
Local time
Yesterday, 21:14
Joined
Jan 27, 2011
Messages
3
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
 

pagevm

New member
Local time
Yesterday, 22:14
Joined
Mar 18, 2011
Messages
2
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
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 03:14
Joined
Sep 12, 2006
Messages
13,892
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.
 

DCrake

Remembered
Local time
Today, 03:14
Joined
Jun 8, 2005
Messages
8,634
Dave
If you need someone to beta test it for you then send me a copy. Will abide by your IPR.
 

pagevm

New member
Local time
Yesterday, 22:14
Joined
Mar 18, 2011
Messages
2
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?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 03:14
Joined
Jul 9, 2003
Messages
11,864
On my setup it needed this reference:
Microsoft Office 12.0 Access database engine object library
 

nugai

New member
Local time
Yesterday, 19:14
Joined
Sep 4, 2014
Messages
1
Although this thread seems to be a bit dated, I encountered a problem that relates to it:

I'm helping a friend converting a MS Access 2010 database into a webapp. I managed to export the data directly from MS Access to MySQL using an ODBC connector, and Metadata about the tables and fields via Database Documenter to an Excel spreadsheet that I converted to CSV format.

What I'm struggling with is exporting administrator-defined data that is visible in Design View, such as default values and whether or not a field is indexed (as shown in the marked-up screenshot below).

I'm new MS Access and VBA (I'm really only learning it for helping my friend), but managed to get the script shown in post #10 above running. However, this script seems to produce about the same output I managed to get from Database Documenter -- and not the Design View data I'm looking for.

Can anybody please help and provide some insights (where is Design View data stored) and/or guidance for exporting the default values and index information?

Any help would be greatly appreciated.
 

Attachments

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom