Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

Data table main data

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

Data table subsets

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

share|improve this question
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.