Access to Excel Export Limitation

[Dirtrider929]

I am getting a error about a 65,000 limitation for export. Does anyone have a work around for this? I read about changing to export without formatting but I do not think I have it being formatted at all. I included my code below so you can see how I have it set up.

Private Sub Form_Open(Cancel As Integer)

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteChainDisplayHistoryElite"
DoCmd.OpenQuery "qryChainDisplayHistoryElite"
DoCmd.OpenQuery "qryDeleteChainDisplayHistoryMajestic"
DoCmd.OpenQuery "qryChainDisplayHistoryMajestic"
DoCmd.OpenQuery "qryDeleteChainDisplayHistoryMountain"
DoCmd.OpenQuery "qryChainDisplayHistoryMountain"
DoCmd.OpenQuery "qryDeleteChainDisplayHistoryPrestige"
DoCmd.OpenQuery "qryChainDisplayHistoryPrestige"
DoCmd.OpenQuery "qryDeleteChainDisplayHistoryRural"
DoCmd.OpenQuery "qryChainDisplayHistoryRural"

DoCmd.SetWarnings True
Dim reportname As String
Dim theFilePath As String

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblChainDisplayHistoryElite", "S:\BD Input\Access Tables\Display App Results\Elite"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblChainDisplayHistoryMajestic", "S:\BD Input\Access Tables\Display App Results\Majestic"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblChainDisplayHistoryMountain", "S:\BD Input\Access Tables\Display App Results\Mountain"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblChainDisplayHistoryPrestige", "S:\BD Input\Access Tables\Display App Results\Prestige"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblChainDisplayHistoryRural", "S:\BD Input\Access Tables\Display App Results\Rural"

DoCmd.Quit acExit


End Sub

 

[pr2-eugin]

Did you try DoCmd.OutputTo method instead of DoCmd.TransferSpreadsheet?

 

[Rx_]

I prefer the copyfromrecordset - granted, this takes a lot more codeing. But, it allows for ultimate formatting of the final product as well
It is not for everyone, it is an option.
Code segment to a SQL Server View
But, it does take care of the record size limit.

340   strSQLDirHz = ""
350   strSQLDirHz = strSQLDirHz & ""
360   strSQLDirHz = strSQLDirHz & "SELECT vWells_ListX.Area, vWells_ListX.[Well Name], IIf([vWells_ListX].[ID_Pad_Name]>3,[vWells_ListX].[PadNameFinal],Null) AS [Pad Name],  vWells_ListX.Status AS [Well Status], vWells_ListX.State as ST, vWells_ListX.County, vSHLBHL_SHLPriority.[Req_Fin] AS [Status], Wells_SHLBHL.Sec_SHLBHL AS [Sec], Wells_SHLBHL.Twn_SHLBHL AS Twn, Wells_SHLBHL.Rng_SHLBHL AS Rng, Wells_Lease_Type_1.Lease_Type AS [Lease Type], Wells_Lease.LeaseNo AS [Lease Number], Wells_Lease_Type.Lease_Type AS [Dir Hz Lease Type], Wells_Lease_DirHz.Dir_HzPass AS [Dir Hz Pass Through Lease Number], SurfaceOwner.SurfaceOwner AS [Surface Owner], MineralOwner.MineralOwner AS [Mineral Owner], IIf([CA_Req],'Yes','No') AS [CA Required], Wells_Lease.[CA No] AS [CA/PA NO] "
370   strSQLDirHz = strSQLDirHz & " FROM ((((((vWells_ListX LEFT JOIN Wells_Lease ON vWells_ListX.ID_Wells = Wells_Lease.ID_Wells) LEFT JOIN (vSHLBHL_SHLPriority LEFT JOIN Wells_SHLBHL ON vSHLBHL_SHLPriority.ID_SHLBHL = Wells_SHLBHL.ID_SHLBHL) ON vWells_ListX.ID_Wells = vSHLBHL_SHLPriority.ID_Wells) LEFT JOIN MineralOwner ON Wells_Lease.MineralOwnerID = MineralOwner.MineralOwnerID) LEFT JOIN SurfaceOwner ON Wells_Lease.SurfaceOwnerID = SurfaceOwner.SurfaceOwnerID) LEFT JOIN Wells_Lease_DirHz ON Wells_Lease.ID_Wells = Wells_Lease_DirHz.ID_Wells) LEFT JOIN Wells_Lease_Type ON Wells_Lease_DirHz.HZ_Type_Num = Wells_Lease_Type.ID_Lease_Type) LEFT JOIN Wells_Lease_Type AS Wells_Lease_Type_1 ON Wells_Lease.ID_Lease_Type = Wells_Lease_Type_1.ID_Lease_Type "
380   strSQLDirHz = strSQLDirHz & " WHERE (((vWells_ListX.ID_Area) " & ID_Area & ")) "
390   strSQLDirHz = strSQLDirHz & " ORDER BY vWells_ListX.Area, vWells_ListX.WName, Format([WNumber],'000'), vWells_ListX.WSection;"
 
 
400       Debug.Print " sql string =  " & strSQLDirHz   ' for test purposes
410   Set strDataDirHz = CurrentDb.OpenRecordset(strSQLDirHz, dbOpenSnapshot, dbReadOnly)      '
Set strDataDirHz = CurrentDb.OpenRecordset(strSQLDirHz, dbOpenSnapshot)
 
420   If ObjXL Is Nothing Then
430       Set ObjXL = New Excel.Application
440       ObjXL.EnableEvents = False
460   Else
470       Excel.Application.Quit
480       DoEvents
490       Set ObjXL = New Excel.Application
500       ObjXL.EnableEvents = False
520   End If
530   On Error GoTo PROC_Error
540     ObjXL.Visible = False                                                              ' ******* change for production
        'ObjXL.Visible = True   ' for debug to see excel visible
550     ObjXL.Workbooks.Add
560     intWorksheetNum = 1
570     intRowPos =1       
580        ObjXL.Worksheets(intWorksheetNum).Name = "FedWellsAudit"                  
590   intRowPos =  6                 ' Sets starting Row for data in Excel - reference fields to this ' I don't like headers on row 1
600   DoEvents
610   ObjXL.DisplayAlerts = False            ' 'Turn off Display Alerts
620   ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset strDataDirHz
630   DoEvents
640   intMaxRecordCount = strDataDirHz.RecordCount - 1                                                      ' - use for max rows returned in formatting later
           'Debug.Print "max record count is " & intMaxRecordCount
                                            ' ------- Create Header in new Excel based on Query
650       intMaxheaderColCount = strDataDirHz.Fields.Count - 1
660       For intHeaderColCount = 0 To intMaxheaderColCount
670     If Left(strDataDirHz.Fields(intMaxheaderColCount).Name, 3) <> "xxx" Then  ' Future use - adding xxx in cross tab queries for fields to exclude
680   ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos - 1, intHeaderColCount + 1) = strDataDirHz.Fields(intHeaderColCount).Name    ' Relative to intRowPos
690     End If
700       Next intHeaderColCount
710       Debug.Print "Columns created count is " & intHeaderColCount
720       ObjXL.Rows((intRowPos - 1) & ":" & (intRowPos - 1)).Select

 

[Dirtrider929]

Thank you both for the reply. I took the suggestion from another site and linked a blank spreadsheet directly to the query for export. I will try both of your suggestions so that I have them for future reference.