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