Consulting

Results 1 to 9 of 9

Thread: Solved: Export all Access tables to CSV

  1. #1
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location

    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

  2. #2

    Lightbulb AllTables and TransferX

    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.

    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
    Last edited by Aussiebear; 05-20-2022 at 07:47 PM. Reason: Changed Code tags
    You don't understand anything until you learn it more than one way. ~Marvin Minsky

    I never teach my pupils; I only attempt to provide the conditions in which they can learn. - Albert Einstein

  3. #3
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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

  4. #4
    Evening again.

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

    Take care.
    Scott
    You don't understand anything until you learn it more than one way. ~Marvin Minsky

    I never teach my pupils; I only attempt to provide the conditions in which they can learn. - Albert Einstein

  5. #5
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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

    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
    Last edited by Aussiebear; 05-20-2022 at 07:48 PM. Reason: Changed Code tags

  6. #6
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    I think you need a backslash between the path and object name. Try this:
    Dim strFolder As string
    strFolder =  Application.CurrentProject.Path & "\"
    Then substitute
    strFolder & obj.name[/vba] where you have[vba]Application.CurrentProject.Path & obj.Name
    Last edited by Aussiebear; 05-20-2022 at 07:49 PM. Reason: Changed Code tags

  7. #7
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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 .

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

    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.
    Last edited by Aussiebear; 05-20-2022 at 07:50 PM. Reason: Changed code tags

  9. #9
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    This 13 yo thread is closed.

    Please open another thread with your question.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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