Exporting data from Microsoft Access table file subsets to plain text

Question

I have a problem in exporting data from Microsoft Access. I have an Access file made of several rows and columns, like this:

Every row contains a subset of data, tha could be observed by clicking on the "+" button:

I would like to export all these data (main table & subsets), but there's a problem. When I click on External Data -> Export -> Text file, Ms Access exports only the main table data (1st Figure), and completely omits the data of the subsets (2nd Figure).

How could I export all these data (main table & subsets)?

Thanks

Answer 1

Microsoft Access does not know how you want the files exported when you use the standard "export" text function.

Here is a trivial piece of code that you can add to a Module, and run from a Command Button on a form, or directly from the Immediate Window:

Public Sub ExportTables(ByVal DestinationPath As String)

    Dim a As Long

    For a = 0 To CurrentDb.TableDefs.Count - 1
        If Not (CurrentDb.TableDefs(a).Name Like "MSys*") Then
            DoCmd.TransferText AcTextTransferType.acExportDelim _
                , _
                , CurrentDb.TableDefs(a).Name, DestinationPath & "\" & CurrentDb.TableDefs(a).Name & ".csv" _
                , True
        End If
    Next a

End Sub

Add a Module to your Access database, and paste in the code above. Press "CTRL-G", enter ExportTables "C:\temp", then press Enter - this will export all tables in the database into C:\TEMP in comma-separated-values format (.CSV) ready for import into Excel or other databases.