VBA/Microsoft Access Macro - SELECT from all tables, export results to flat files

Question

I am trying to write a macro in Microsoft Access to accomplish the following:

For a list of linked tables (ODBC connection):

-SELECT top 10 records

-export results of each query to either Excel or CSV file of title [table_name] + suffix, i.e., "tablename1_10", "tablename2_10", etc. to a designated folder

======================

UPDATE:

======================

I now have a script, which despite my previous error with the ODBC connection, works when I do not try to declare an ODBC Connection String and declare "qdf.Open". Macro below, with small edits for anonymity:

Sub queryAllTables()
    Dim tables() As String
    tables = Split("<table names>", ",")

    For Each element In tables
        Dim elm As String
        elm = element
        Call sExportTop10(elm, "<folder>", False)
    Next element
End Sub

Sub sExportTop10(strTable As String, strFolder As String, blnExcel As Boolean)
    On Error GoTo E_Handle
    Dim strFile As String
    Dim strSQL As String
    If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
    strFile = strFolder & strTable
    strSQL = "SELECT TOP 10 * FROM [" & strTable & "];"

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    For Each MyQueryDef In CurrentDb.QueryDefs
        If MyQueryDef.Name = "qdfExport" Then
            CurrentDb.QueryDefs.Delete ("qdfExport")
            Exit For
            End If
            Next

    Set dbs = CurrentDb()
    Set qdf = dbs.CreateQueryDef("qdfExport")
    qdf.ReturnsRecords = False
    qdf.SQL = strSQL
    qdf.OpenRecordSet

    If blnExcel = True Then
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qdfExport", strFile & "_10.xlsx", True
    Else
        DoCmd.TransferText acExportDelim, , "qdfExport", strFile & "_10.csv", True
    End If
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    Msg = "Error # " & Str(Err.Number) & " was generated by " _
    & Err.Source & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    Resume sExit
End Sub

Rather than creating the query within the code, just create it manually based on one of the linked tables (so that the connection is correct). You can then modify the SQL to output the data. — Applecore, Jul 15 '20 at 18:28
I've now just run your code on a linked SQL server table. Firstly, please ensure that you declare all variables (and enforcing this by `Option Explicit`). Next, `qdf.OpenRecordset` is not needed - you just need to set the SQL for the query. Apart from that, it ran fine. You may want to look at https://stackoverflow.com/questions/730414/determine-real-cause-of-odbc-failure-error-3146-with-ms-access — Applecore, Jul 15 '20 at 19:39

score 1 · Accepted Answer · answered Jul 15 '20 at 14:06

I would suggest that you create a procedure that does the exporting to Excel/CSV, and then you can call it each time to do the exporting. In addition, you will need a Query called "qdfExport" that you will use to modify the SQL to get the top 10 records. Some VBA would look like:

Sub sExportTop10(strTable As String, strFolder As String, blnExcel As Boolean)
    On Error GoTo E_Handle
    Dim strFile As String
    Dim strSQL As String
    If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
    strFile = strFolder & strTable
    strSQL = "SELECT TOP 10 * FROM [" & strTable & "] ORDER BY 1 ASC;"
    CurrentDb.QueryDefs("qdfExport").SQL = strSQL
    If blnExcel = True Then
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qdfExport", strFile & "_10.xlsx", True
    Else
        DoCmd.TransferText acExportDelim, , "qdfExport", strFile & "_10.csv", True
    End If
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sExportTop10", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

And you would then call it as:

call sExportTop10("tblPrintID","C:\test",False)

Which would export the top 10 records from a table called "tblPrintID" to a CSV file in the folder "C:\test".

In the procedure, I have used ORDER BY 1 in the SQL, which ensures that the data is sorted by the first field, which is where the primary key is always located in my tables. If there is no order in the table, then there is no guarantee what order will be used.

Regards,

I would rather get the name list form the system table, dump to array, loop through it, change sql of query def, output results to csv using either DoCmd.TransferSpreadsheet or DoCmd.QueryOut (i dont recall the exact syntax on the second one). — Doug Coats, Jul 15 '20 at 14:57
It depends - the original question said "a list of linked tables" which may or may not be all linked tables. So there would be another sub that selects the tables, either calling this procedure repeatedly or else using this code within it to do the exporting. — Applecore, Jul 15 '20 at 15:03

VBA/Microsoft Access Macro - SELECT from all tables, export results to flat files

1 Answers1