Results 1 to 8 of 8
  1. #1
    trebormac is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2018
    Posts
    2

    VBA code to export table names and their field names to MS Excel

    I have an Access 2016 db with tables linked to an SQL Server db. There are about 200 tables linked in the db. I am looking for some VBA code to copy the table names and their related field names to MS Excel workbook...preferably one sheet for each table.



    Thanks for the help!

    trebormac

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You can do this with no vba code using the database documenter in database tools menu
    Select one or all tables, click options and untick most of the options.
    Run and it will create a report which can be exported to Excel or pdf.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    but if you want, the command to export is:

    docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,vQry, vFile, true,vSheetName

  4. #4
    trebormac is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2018
    Posts
    2
    Somewhat Helpful....Too much data returned, though...its a start. I still prefer some vba cose.

    thanks for your help

    trebormac

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Its possible to do using VBA & I'll look for code later if nobody answers first

    I agree that the full documenter is far too detailed for most purposes
    That's why I said to untick most options
    If you set the options like this:
    Click image for larger version. 

Name:	DBDocumenterOptions.PNG 
Views:	17 
Size:	12.4 KB 
ID:	33197

    then the report should be what you want with one table per page:

    Click image for larger version. 

Name:	DBDocumenter.PNG 
Views:	17 
Size:	13.7 KB 
ID:	33198

    The export works better done to any of PDF, text, HTML or Word.
    Excel less good
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you do any search?

    BING: access vba get table field names

    Returns numerous discussions and sample code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's some older vba that will capture info about your tables and put same into a Table Called Data_Dictionary.
    From this base, you could create a query to select the fields you want and export to excel.
    Good luck.

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : DocumentTables
    ' DateTime  : 2006-06-09 09:29
    ' Author    : jack
    ' Purpose   : To create documentation for all tables in this database
    '             where tableName <>MSys*  or TableName doesn't start with "~"
    '
    '**** Note: This routine builds a new table (data_dictionary) from scratch on each execution ****
    '
    'Fields that are recorded
    '   table_name varchar(250)
    '   table_description varchar(255)
    '   field_name varchar(250)
    '   field_description varchar(255)
    '   ordinal_position NUMBER,
    '   data_type varchar(15),"
    '   length varchar(5)
    '   default varchar(30)
    '
    '
    ' ' Last Modified:
    '
    ' Inputs: N/A
    ' Dependency:   Calls Function FieldType
    '---------------------------------------------------------------------------------------
    '
    Public Sub DocumentTables()
    'Requires function FieldType
    
    10  On Error GoTo Error_DocumentTables
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        Dim strSQL_Drop As String
        Dim strSQL_Create As String
        '==
        Dim idxLoop As index
    
    
        '===
        'SQL to Delete existing copy of this table
    20  strSQL_Drop = "DROP TABLE data_dictionary;"
    
        'SQL to Create the data_dictionary table
    30  strSQL_Create = "CREATE TABLE data_dictionary" & _
                        "(EntryID AUTOINCREMENT PRIMARY KEY,table_name varchar(250),table_description varchar(255), field_name varchar(250),field_description varchar(255)," & _
                        "ordinal_position NUMBER, data_type varchar(15)," & _
                        "length varchar(5), default varchar(30));"
    
    40  Set db = CurrentDb()
    
    50  db.Execute strSQL_Drop, dbFailOnError
    60  DoEvents
    70  db.Execute strSQL_Create, dbFailOnError
    80  DoEvents
    90  Set rs = db.OpenRecordset("data_dictionary")
    
    100 With rs
    110     For Each tdf In db.TableDefs
    120         If Left(tdf.name, 4) <> "Msys" _
                   And Left(tdf.name, 1) <> "~" Then
    
    130             For Each fld In tdf.Fields
    140                 .AddNew
    150                 !table_name = tdf.name
    160                 !table_description = tdf.Properties("description")
    170                 !field_name = fld.name
    180                 !field_description = fld.Properties("description")
    190                 !ordinal_position = fld.OrdinalPosition
    200                 !data_type = FieldType(fld.Type)
    210                 !Length = fld.Size
    220                 !Default = fld.DefaultValue
    
    230                 .Update
    240             Next
    250         End If
    260     Next
    270 End With
    
    280 MsgBox "Tables have been documented", vbInformation, "TABLES DOCUMENTED"
    
    290 rs.Close
    300 db.Close
    
    Exit_Error_DocumentTables:
    
    310 Set tdf = Nothing
    320 Set rs = Nothing
    330 Set db = Nothing
    
    340 Exit Sub
    
    Error_DocumentTables:
    
    350 Select Case Err.number
    
        Case 3376
    
    360     Resume Next    'Ignore error if table not found
    370 Case 3270    'Property Not Found
    
    380     Resume Next
    390 Case Else
    
    400     MsgBox Err.number & ": " & Err.Description
    410     Resume Exit_Error_DocumentTables
    
    420 End Select
    
    End Sub
    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : FieldType
    ' Author    : Jack
    ' Created   : 3/18/2008
    ' Purpose   : To identify fieldtypes in Access.
    '---------------------------------------------------------------------------------------
    ' Last Modified:
    '
    ' Inputs: N/A
    ' Dependency: N/A
    '------------------------------------------------------------------------------
    '
    Private Function FieldType(v_fldtype As Integer) As String
    
    10  On Error GoTo Error_FieldType
    
    20  Select Case v_fldtype
        Case dbBoolean
    30      FieldType = "Boolean"
    40  Case dbByte
    50      FieldType = "Byte"
    60  Case dbInteger
    70      FieldType = "Integer"
    80  Case dbLong
    90      FieldType = "Long"
    100 Case dbCurrency
    110     FieldType = "Currency"
    120 Case dbSingle
    130     FieldType = "Single"
    140 Case dbDouble
    150     FieldType = "Double"
    160 Case dbDate
    170     FieldType = "Date"
    180 Case dbText
    190     FieldType = "Text"
    200 Case dbLongBinary
    210     FieldType = "LongBinary"
    220 Case dbMemo
    230     FieldType = "Memo"
    240 Case dbGUID
    250     FieldType = "GUID"
    260 End Select
    
    Exit_Error_Fieldtype:
    270 Exit Function
    
    Error_FieldType:
    280 MsgBox Err.number & ": " & Err.Description
    290 Resume Exit_Error_Fieldtype
    
    End Function

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If you want another free ready made solution, look at the data dictionary utility from JStreet http://www.jstreettech.com/downloads.aspx
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-09-2018, 09:40 AM
  2. Replies: 2
    Last Post: 02-03-2018, 06:54 PM
  3. Replies: 11
    Last Post: 03-02-2016, 11:01 AM
  4. Export to Excel displaying field names instead of captions
    By kattatonic1 in forum Import/Export Data
    Replies: 3
    Last Post: 04-10-2014, 09:13 AM
  5. Replies: 2
    Last Post: 11-06-2013, 10:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums