Export all Access tables to CSV

[LuciaLotter]

I tried the code as per a previous thread to export all tables in an Access database to text files:

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.


I added the export specification (export as tab delimited) to the DoCmd.TransferText command as without it, the following error was displayed: 3441 Text file specification field separator matches decimal separator or text delimiter. I changed my region settings from:
Decimal symbol (.), Digit grouping symbol (,) and List separator (,) to
Decimal symbol (,), Digit grouping symbol (space) and List separator (,)
and various other combinations, but it still did not work.


Your assistance will be highly appreciated. I use Office 365 and Windows 10 Pro.

[Aussiebear]

Late but....

Sub ExportAllTablesToText()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strFolderPath As String
    Dim strFileName As String
    Set db = CurrentDb
    '  IMPORTANT: Set your desired export folder path here  
    strFolderPath = "C:\ExportedTables\" 
    ' Make sure this folder exists or create it.
    ' Create the folder if it doesn't exist
    If Dir(strFolderPath, vbDirectory) = "" Then
        MkDir strFolderPath
    End If
    ' Loop through each table in the database
    For Each tdf In db.TableDefs
        ' We only want to export user-created tables, not system tables
        If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) <> "~" Then 
            ' Exclude system tables and temporary tables
            strFileName = strFolderPath & tdf.Name & ".csv"
            DoCmd.TransferText acExportDelim, , tdf.Name, strFileName, True
            Debug.Print "Exported table: " & tdf.Name & " to " & strFileName
        End If
    Next tdf
    Set tdf = Nothing
   Set db = Nothing
   MsgBox "All tables exported successfully!", vbInformation
End Sub

[Aussiebear]

and to export to a csv file ( code should be extremely similar I believe)

Sub ExportAllTablesToCSV()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strFolderPath As String
    Dim strFileName As String
    Set db = CurrentDb
    ' IMPORTANT: Set your desired export folder path here
    strFolderPath = "C:\ExportedTables\" 
    ' Make sure this folder exists or create it.
    ' Create the folder if it doesn't exist
    If Dir(strFolderPath, vbDirectory) = "" Then
        MkDir strFolderPath
    End If
    ' Loop through each table in the database
    For Each tdf In db.TableDefs
        ' We only want to export user-created tables, not system tables
        If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) <> "~" Then 
            ' Exclude system tables and temporary tables
            strFileName = strFolderPath & tdf.Name & ".csv" 
            ' Explicitly setting .csv extension
            ' acExportDelim for delimited text (CSV is a type of delimited text)
            ' True ensures the first row includes field names (headers)
            DoCmd.TransferText acExportDelim, , tdf.Name, strFileName, True
            Debug.Print "Exported table: " & tdf.Name & " to " & strFileName
        End If
    Next tdf
    Set tdf = Nothing
    Set db = Nothing
    MsgBox "All tables exported successfully to CSV!", vbInformation
End Sub

[arnelgp]

there is an explanation on the code:

Public Sub ExportAll()
    Dim strFolder As String
    strFolder = Application.CurrentProject.Path & "\"  'add the Backslash
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    For Each obj In dbs.AllTables
        If Left(obj.Name, 4) <> "MSys" Then
            ' arnelgp
            '
            ' You cannot export the Linked textfile to the same folder
            ' since it will delete the Original textfile, therefore
            ' you will get the error.
            ' Just export it to a Subfolder (Text subfolder)
            '
            If InStr(1, DLookup("foreignName", "MsysObjects", "Name='" & obj.Name & "'") & "", "#") > 0 Then
                On Error Resume Next
                MkDir strFolder & "Text"
                On Error GoTo 0
                DoCmd.TransferText acExportDelim, , obj.Name, strFolder & "Text\" & obj.Name & ".csv", True
            Else
                DoCmd.TransferText acExportDelim, , obj.Name, strFolder & obj.Name & ".csv", True
            End If
        End If
    Next obj
End Sub

[Aussiebear]

In that case I shall go with yours arnelgp.

[Gasman]

there is an explanation on the code:

Public Sub ExportAll()
    Dim strFolder As String
    strFolder = Application.CurrentProject.Path & "\"  'add the Backslash
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    For Each obj In dbs.AllTables
        If Left(obj.Name, 4) <> "MSys" Then
            ' arnelgp
            '
            ' You cannot export the Linked textfile to the same folder
            ' since it will delete the Original textfile, therefore
            ' you will get the error.
            ' Just export it to a Subfolder (Text subfolder)
            '
            If InStr(1, DLookup("foreignName", "MsysObjects", "Name='" & obj.Name & "'") & "", "#") > 0 Then
                On Error Resume Next
                MkDir strFolder & "Text"
                On Error GoTo 0
                DoCmd.TransferText acExportDelim, , obj.Name, strFolder & "Text\" & obj.Name & ".csv", True
            Else
                DoCmd.TransferText acExportDelim, , obj.Name, strFolder & obj.Name & ".csv", True
            End If
        End If
    Next obj
End Sub

Arnel,
I though I would try it, but get this error in the picture.


However the table is called KeithDupes.
I cannot see where the # is coming from?

[Gasman]

OK, I *think* it is down to that table being linked to an Excel worksheet.

Need to check for $ as well as # ?

I could only get tables to work?

[arnelgp]

Link excel is fine on my test, the only problem is the linked Text (csv or text).
just tested it again and i did not get any errors.

[Gasman]

Nope, definitely worksheets with me. Office 2019.
Regardless, I would only want to backup my tables, should I use it. The linked tables are already backed up (after a fashion) being outside the DB?
That it does, so thank you.