Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rating: Thread Rating: 15 votes, 5.00 average. Display Modes
Old 07-17-2008, 02:24 PM   #1
RBetz
Registered User
 
Join Date: Jun 2008
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
RBetz is on a distinguished road
Post How to export a table definition into Excel

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.)

RBetz is offline   Reply With Quote
Old 07-18-2008, 04:40 AM   #2
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
Have you tried Tools/Analyze/Documenter
__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 07-18-2008, 06:51 AM   #3
RBetz
Registered User
 
Join Date: Jun 2008
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
RBetz is on a distinguished road
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.

RBetz is offline   Reply With Quote
Old 07-23-2008, 04:03 AM   #4
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
Sorry, can't help you.
__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 07-24-2008, 06:00 AM   #5
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 328 Times in 209 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
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:
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 07-25-2008, 03:17 AM   #6
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 328 Times in 209 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
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:
Attached Files
File Type: zip Documenter.zip (17.7 KB, 3181 views)
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 08-19-2008, 06:53 AM   #7
Wittier
Registered User
 
Join Date: Aug 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Wittier is on a distinguished road
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/out...llection-tool/

Cheers

Wittier is offline   Reply With Quote
Old 07-09-2009, 09:41 AM   #8
Mattster
Registered User
 
Join Date: Jul 2009
Location: Vancouver
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Mattster is on a distinguished road
Re: How to export a table definition into Excel

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
Mattster is offline   Reply With Quote
Old 12-23-2010, 01:48 AM   #9
nhtuan
Newly Registered User
 
Join Date: Dec 2010
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
nhtuan is on a distinguished road
Re: How to export a table definition into Excel

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!
nhtuan is offline   Reply With Quote
Old 01-27-2011, 09:42 AM   #10
djknorr75
Newly Registered User
 
Join Date: Jan 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
djknorr75 is on a distinguished road
Re: How to export a table definition into Excel

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
djknorr75 is offline   Reply With Quote
Old 01-27-2011, 12:14 PM   #11
accessfever
Newly Registered User
 
Join Date: Feb 2010
Posts: 101
Thanks: 2
Thanked 0 Times in 0 Posts
accessfever is on a distinguished road
Question Re: How to export a table definition into Excel

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.
accessfever is offline   Reply With Quote
Old 01-27-2011, 12:56 PM   #12
djknorr75
Newly Registered User
 
Join Date: Jan 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
djknorr75 is on a distinguished road
Re: How to export a table definition into Excel

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
Attached Images
File Type: png References.PNG (20.6 KB, 870 views)
djknorr75 is offline   Reply With Quote
Old 01-27-2011, 01:12 PM   #13
accessfever
Newly Registered User
 
Join Date: Feb 2010
Posts: 101
Thanks: 2
Thanked 0 Times in 0 Posts
accessfever is on a distinguished road
Re: How to export a table definition into Excel

It worked like a charm! Thanks very much!
accessfever is offline   Reply With Quote
Old 01-27-2011, 01:19 PM   #14
djknorr75
Newly Registered User
 
Join Date: Jan 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
djknorr75 is on a distinguished road
Re: How to export a table definition into Excel

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
djknorr75 is offline   Reply With Quote
Old 03-18-2011, 08:20 AM   #15
pagevm
Newly Registered User
 
Join Date: Mar 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
pagevm is on a distinguished road
Re: How to export a table definition into Excel

Quote:
Originally Posted by nhtuan View Post
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

pagevm is offline   Reply With Quote
Reply

Tags
table definition

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Appending an Excel file to an Access table adrian.stock22 General 7 12-12-2008 02:47 PM
create a progress bar in VB JohnLee Modules & VBA 3 01-08-2008 07:29 AM
Export table to EXCEL with required formatting Teleinternnor General 1 01-09-2007 09:33 AM
Pasting to Table from Excel Ode Tables 1 02-15-2006 02:48 PM
Can I export table data to Excel In a set Format? chewy General 53 04-21-2003 07:23 AM




All times are GMT -8. The time now is 07:33 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World