Export all tables in an Access database to CSV

I don’t usually mess around with Access much, but when I do it’s usually to have it act as an intermediary data source while I’m getting data from one format to another.

I had just such a problem to solve today, and the usually excellent out-of-the-box import/export processes let me down a little. Essentially I needed to get the data from each of the tables into separate CSV files - unfortunately I had quite a few tables to do and the UI only allows you to do one at a time.

Here’s the little VBA routine I put together to solve the problem:

 1Public Sub ExportAllTablesToCSV()
 2
 3    Dim i As Integer
 4    Dim name As String
 5    
 6    For i = 0 To CurrentDb.TableDefs.Count
 7        name = CurrentDb.TableDefs(i).name
 8        
 9        If Not Left(name, 4) = "msys" And Not Left(name, 1) = "~" Then
10            DoCmd.TransferText acExportDelim, , name, _
11                "c:\exports\" & name & ".csv", _
12                True
13        End If
14    
15    Next i
16
17End Sub