Solved: Export all Access tables to CSV

Hi All,

I have not used Access before.

I have an access mdb database file which contains many tables.

I have it stored as "C:\DS\SQLDataFundamentals.mdb"

I would like to extract all tables from this mdb file and store them in the same folder (i.e. ""C:\DS\") as a CSV file and also an Excel 95 file with the same name as the table

e.g. If a table in "SQLDataFundamentals.mdb" is called "DUAL", I would like to export this to both "C:\DS\DUAL.csv" and "C:\DS\DUAL.xls")

Could anyone please show me some code that i could run to allow this to happen?

Any help appreciated.

kind regards

Good Evening.

Access has two built in Functions that allow you to do exactly what you want quite easily.

If you refer to the MsAccess help file, you will find information on the following three topics that will give you a more detailed information so you can modify the code as needed.

AllTables Collection
TransferSpreadsheet
TransferText

That being said, the code below will do exactly as you requested. Note that there are hidden Tables that are used my MsAccess. Their names begin with "MSys" and can easily be eliminated by this simple conditional statement.

Code:

---

Option Compare Database
Public Sub ExportAll()
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    For Each obj In dbs.AllTables
        If Left(obj.Name, 4) <> "MSys" Then
            DoCmd.TransferText acExportDelim, , obj.Name, obj.Name & ".csv", True
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, obj.Name, obj.Name & ".xls", True
        End If
    Next obj
End Sub

---

Take Care.
Scott

Quote:

---

Originally Posted by Demosthine

Good Evening.

Access has two built in Functions that allow you to do exactly what you want quite easily.

If you refer to the MsAccess help file, you will find information on the following three topics that will give you a more detailed information so you can modify the code as needed.

AllTables Collection
TransferSpreadsheet
TransferText

That being said, the code below will do exactly as you requested. Note that there are hidden Tables that are used my MsAccess. Their names begin with "MSys" and can easily be eliminated by this simple conditional statement.

[vba]
Option Compare Database
Public Sub ExportAll()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
If Left(obj.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , obj.Name, obj.Name & ".csv", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, obj.Name, obj.Name & ".xls", True
End If
Next obj
End Sub
[/vba]
Take Care.
Scott

---

Scott,

Thank you for your great code. It works fantastically.

Only one thing though. I pasted it in the module of the "C:\DS\SQLDataFundamentals.mdb", and ran it.

It outputted the *.CSV and *.xls files to a default My Documents Folder as opposed to the folder in which the mdb file is saved.

Could youn please explain how to amend to output in the same folder as the mdb file, for future use of this great VBA code?

thanks and regards

Evening again.

You can add the current Database's Path to the Output File using Application.CurrentProject.Path.

Take care.
Scott

Quote:

---

Originally Posted by Demosthine

Evening again.

You can add the current Database's Path to the Output File using Application.CurrentProject.Path.

Take care.
Scott

---

Hi Scott,

Thanks for your prompt reply.

I tried the following, as per your suggestion:

Option Compare Database

Code:

---

Public Sub ExportAllTables_to_CSV_and_XLS()

    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData

    For Each obj In dbs.AllTables

        If Left(obj.Name, 4) <> "MSys" Then
       
            DoCmd.TransferText acExportDelim, , obj.Name, Application.CurrentProject.Path & obj.Name & ".csv", True
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, obj.Name, Application.CurrentProject.Path & obj.Name & ".xls", True
           
        End If

    Next obj

End Sub

---

My main mdb file is located in:

"C:\Users\Username\Documents\PROGRAMMING\SQL\Datasets\SQLFUN2002.mdb"

If I run the above code from the mdb file, it exports all the CSV/ XLS files to:

"C:\Users\Username\Documents\PROGRAMMING\SQL" i.e. one level above where the mdb file is currently stored.

It's pretty close, but just for my understanding for future use, how would I export, in this case to the "C:\Users\Username\Documents\PROGRAMMING\SQL\Datasets" folder?

Thanks again for your patience and great help :).

regards

I think you need a backslash between the path and object name. Try this:

Code:

---

Dim strFolder As string
strFolder =  Application.CurrentProject.Path & "\"

---

Then substitute

Code:

---

strFolder & obj.name[/vba] where you have[vba]Application.CurrentProject.Path & obj.Name

---

Quote:

---

Originally Posted by hansup

I think you need a backslash between the path and object name. Try this:
[vba]Dim strFolder As string
strFolder = Application.CurrentProject.Path & "\"[/vba]
Then substitute
[vba]strFolder & obj.name[/vba] where you have[vba]Application.CurrentProject.Path & obj.Name[/vba]

---

This works very nicely hansup, thank you :friends:.

Thank you for the advice provided. I tried the code as well:

Code:

---

Option Compare Database
Public Sub ExportAll()
Dim strFolder As String
strFolder = Application.CurrentProject.Path & ""
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
If Left(obj.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, "Table1 Export Specification", obj.Name, strFolder & obj.Name & ".csv", True
End If
Next obj
End Sub

---

This exports the first table, but not the second one with a table name of "users". The error message is "The Microsoft Access database engine could not find the object 'users#csv'.". Your assistance will be highly appreciated.

This 13 yo thread is closed.

Please open another thread with your question.