Exporting Access Tables (Access 2000)

Topic

New Reply

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)

Reply | Quote

Replies

Do you mean one table per excel .xls file?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Do you mean one table per excel .xls file?

Reply | Quote

This is DAO code so you have to set the reference to Microsoft DAO 3.6 Oject Library.
For each table in separate file :

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

If you want all the tables in one file, change the Docmd line to

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    tdf.Name, "C:YourDirectoryYourFileName.xls"

Reply | Quote

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 On Error Resume Next and On Error GoTo 0 surrounding the DoCmd.TransferSpreadsheet acExport, … line cured the problem. My friend; you have saved me a pile of effort.

Reply | Quote

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 On Error Resume Next and On Error GoTo 0 surrounding the DoCmd.TransferSpreadsheet acExport, … line cured the problem. My friend; you have saved me a pile of effort.

Reply | Quote

This is DAO code so you have to set the reference to Microsoft DAO 3.6 Oject Library.
For each table in separate file :

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

If you want all the tables in one file, change the Docmd line to

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    tdf.Name, "C:YourDirectoryYourFileName.xls"

Reply | Quote