Export More than 65,000 Records from Access Macro Query

McCullough, Julius 0 Reputation points
2023-04-20T17:34:14.1033333+00:00

Hi all, I am trying to export this query called Policy Snapshot. I have a macro that runs this query and outputs the file in a particular location. The last few times my team and I ran the query, we noticed that the query will not export more than around 65.5k rows of data, but we have more data that needs to be exported--around 70,555. I saw on an older version of this forum that this is because we are trying to "export with formatting and layout". I also saw someone on this forum say to try and use DoCmd.TransferSpreadhseet instead of DoCmd.OutputTo and to use acSpreadsheetTypeExcel12XML as the spreadsheet type instead of acOutputQuery. I tried this and the Macro would not run. Essentially, I am just trying to figure out a way to have Access export more than 65,000 rows of data into one file and it does not have to be formatted. I know there is a way to a tell Access to export 65,000 rows twice so that we can get all 70,000 records of data, although it will be in two separate excel files, the joining of these files should be simple. I'm just not to sure how to set up that code to get Access to export the remaining code on a separate excel file after it exports the 65,000 rows of data.DoCmd.OutputTo

Reporting database accesss

70,000

Access
Access
A family of Microsoft relational database management systems designed for ease of use.
334 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
849 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ken Sheridan 2,756 Reputation points
    2023-04-25T20:57:17.77+00:00 
    You might like to try the following function.  This exports the result table of a query to a delimited text file by writing the data to the file row by row.  You can then open the file with Excel and save it as an Excel workbook: 

Function ExportToText(strQuery As String, _
                    strExportTo As String, _
                    strDelim As String, _
                    blnQuoteText As Boolean, _
                    Optional blnHasFieldNames As Boolean = True)
                    
' Accepts:
' strQuery - Text - name of query to be exported
' strExportTo - Text- path to file to export to
' strDelim - Text - delimiter character(s) to separate fields
' blnQuoteText - Boolean - True to enclose text or memo data in quotes
' blnHasFieldNames - Boolean - (optional) - True (default) to export field names as first line

   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim qdf As DAO.QueryDef
   Dim prm As DAO.Parameter
   Dim fld As DAO.Field
   Dim n As Integer
   Dim strPrintList As String
   Dim strQuote As String
   
   Set dbs = CurrentDb
   Set qdf = dbs.QueryDefs(strQuery)
   
   ' evaluate query's parameters, if any
   For Each prm In qdf.Parameters
       prm = Eval(prm.Name)
   Next prm
    
   Set rst = qdf.OpenRecordset
   
   If Dir(strExportTo) <> "" Then
       If MsgBox("Overwrite " & strExportTo & "?", vbQuestion + vbYesNo, "Export Query") = vbYes Then
           Kill strExportTo
       ElseIf MsgBox("Append rows to " & strExportTo & "?", vbQuestion + vbYesNo, "Export Query") = vbNo Then
           Exit Function
       End If
   End If
           
   With rst
       If Not (.BOF And .EOF) Then
           Open strExportTo For Append As #1
           
           If blnHasFieldNames Then
                ' include column headings in text file
                For n = 0 To qdf.Fields.Count - 1
                     strPrintList = strPrintList & strDelim & qdf.Fields(n).Name
                Next n
                ' remove leading delimiter
                strPrintList = Mid$(strPrintList, Len(strDelim) + 1)
                Print #1, strPrintList
                strPrintList = ""
           End If
           
           Do While Not .EOF
               For n = 0 To qdf.Fields.Count - 1
                   Set fld = .Fields(n)
                   strQuote = IIf(blnQuoteText And (fld.Type = dbText Or fld.Type = dbMemo), """", "")
                   strPrintList = strPrintList & strDelim & strQuote & _
                   .Fields(n) & strQuote
               Next n
               ' remove leading delimiter
               strPrintList = Mid$(strPrintList, Len(strDelim) + 1)
               Print #1, strPrintList
               strPrintList = ""
               .MoveNext
           Loop
           Close #1
       End If
   End With

Exit_Here:
   rst.Close
           
End Function
    0 comments