Too Many Rows To Output Error (2306) 1

[StayAtHomeMom]

Hello,

I am using VBA code to run queries and format them off the click of a button on a form. One of my queries is a quarterly report that has many records (over 29K). When I try to run it, I get:

Run-time error '2306':
There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access.

Here's the code I'm using:

DoCmd.OutputTo acQuery, "RPT302_YTD_Data", acFormatXLS, "C:\Mypathname...", False, "", 0

Workbooks.Open "C:\Mypathname..."


I also have a
DBEngine.SetOption dbMaxLocksPerFile, 35000
statement at the beginning of the subroutine.

Is there anything I can do/change to make this query run and output to Excel?

Thanks for your insight!

 

[vbajock]

IS there any reason you can't use TransferSpreadsheet?

 

[StayAtHomeMom]

vbajock,

I've tried that before, and ended up getting a run time error 3027, "Cannot update. Database or object is read only" which I heard happens because of some Access limitations to Import/Export file extentions. I don't know what else to try. I can't split up the spreadsheet, and it can't go to a .txt file. Any other suggestions???

 

[StayAtHomeMom]

vbajock,

You encouraged me to try TransferSpreadSheet again, and it worked this time!!! I don't know what I was doing wrong before. Thanks for the suggestion!

 

[vbajock]

The row limit for Excel is 65,536 records. You don't look like your anywhere near that, so you must have a coding problem. A lot of times, if your having problems with the export stuff, it helps to set up the export as a macro, then convert the macro to VBA. You get something like this:

Function test1()

On Error GoTo test1_Err

DoCmd.TransferSpreadsheet acExport, 8, "tblCartonMaster", "c:\windows\temp\test.xls", True, ""


test1_Exit:
Exit Function

test1_Err:
MsgBox Error$
Resume test1_Exit

End Function

To this function I add some code to erase any spreadsheet that exists and to give me a little extra error handling:

Function test1()
On Error GoTo test1_Err
'have kill the file, cause Excel will just add the new ss to the existing ss as a new workbook

KILL "c:\windows\temp\test.xls"


DoCmd.TransferSpreadsheet acExport, 8, "tblCartonMaster", "c:\windows\temp\test.xls", True, ""


test1_Exit:
Exit Function

test1_Err:
'YOU TRAP AN ERROR HERE IF YOU CAN"T KILL THE FILE
if err = 53 then
'the file doesn't exist, so resume processing at next line
Resume Next

else
'tell me whats wrong

MsgBox Error$
Resume test1_Exit

End if


End Function

You could cut and paste the second function, plug in your values in the TransferSpreadsheet line and let me know what happens.