Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Exporting Access Tables (Access 2000)

    Good day all
    I have a large complicated Access database with limited documentation. In my efforts to back engineer the relationships, I want to export all of the tables to Excel. Does anyone have some code that will export all of the tables into a defined folder? It gets tiresome trying to export in excess of 130 tables; and then doing it again; and again. (sigh)
    Regards
    Don

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Exporting Access Tables (Access 2000)

    Do you mean one table per excel .xls file?

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Access Tables (Access 2000)

    This is DAO code so you have to set the reference to Microsoft DAO 3.6 Oject Library.
    For each table in separate file :
    <pre>Sub ExportAllTables()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Set db = CurrentDb
    For Each tdf In db.TableDefs
    If Left(tdf.Name, 4) <> "MSys" Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    tdf.Name, "C:YourDirectory" & tdf.Name & ".xls"
    End If
    Next
    End Sub</pre>

    If you want all the tables in one file, change the Docmd line to
    <pre>DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    tdf.Name, "C:YourDirectoryYourFileName.xls"</pre>

    Francois

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Exporting Access Tables (Access 2000)

    Pat
    Thanks for the interest. I need the tables exported as separate files. As you will note Francois solved my problem before I could get back to you.
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Exporting Access Tables (Access 2000)

    Francois
    Thanks for the speedy and comprehensive response. I forgot to mention that during the manual exporting activity, one of the tables refused to transfer due to an urecognizable path. I suspect I am dealing with the Access equivalent of a shortcut. This was all refreshed in my memory when the error alert came up. A simple <span style="background-color: #FFFF00; color: #000000; font-weight: bold">On Error Resume Next</span hi> and <span style="background-color: #FFFF00; color: #000000; font-weight: bold">On Error GoTo 0</span hi> surrounding the <span style="background-color: #FFFF00; color: #000000; font-weight: bold">DoCmd.TransferSpreadsheet acExport, ...</span hi> line cured the problem. My friend; you have saved me a pile of effort.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •