This recordset is not updateable error

[\Pamela via AccessMonster.com\]

Good Morning.

I am getting this error trying to export a query result set to Excel. The
query runs fine itself, but when I export it to Excel, I get the error.
There are over 13,000 rows to export. I had thought I had read there is a
limitation in access but there was a way around this through VB. How can I
tell Access to export all the rows returned in the query?

Thanks!

 

[Guest]

I don't think the number of records is the problem, I've exported 46.500 to
Excel with no problem. Probably does not help you but may stop you looking
for an answer to something that maybe isn't the real problem!

Sheila

 

[Van T. Dinh]

How did you try to export (the data returned by) the Query to Excel?

Did you use the Menu File / Export or VBA code?

The Menu File / Export ... is restricted to 16K rows as it exports to an
earlier format of Excel (Excel 95???) that can only handle 16K rows. With
the VBA TransferSpreadsheet Method, you can specify a later version of Excel
that can handle 64K rows.

I don't think the error message you got was created by the exporting
processing.

Post the relevant details of your source Table(s), the SQL String of your
Query, the method you used to export and if you used VBA code, post the
relevant code. Please state also your Access version + SP and your OS
version.

 

[\Pamela via AccessMonster.com\]

Thanks. The query returns all the rows of a table with 23 columns & 17,o93
records. The query is kicked off from a button on a form. In the database
on the development machine, there are only 12,000 records in the test table,
which is why I assume the problem has to do with the number of records. The
button triggers a function coded in VBA. The VBA is as follows:

Private Function ExportQueryExcel()
Rem Open the selected query in Excel
' Enable error handler.
On Error GoTo Error_ExportQueryExcel

DoCmd.OutputTo acOutputQuery, [QueryList Control], acFormatXLS, , True

Exit_ExportQueryExcel:
Exit Function
Error_ExportQueryExcel:
Resume Exit_ExportQueryExcel
End Function

The query is chosen from a list of values. Version of Access: 2002. Also,
the error appears in the status bar just after the click event, then
following the status bar message "outputting object".

Thanks again. I'm still rusty with VB & am used to working with PL/SQL &
Oracle.

 

[\Pamela via AccessMonster.com\]

Forgot to mention: The table with only 12,000 records exports to Excel fine.

 

[Van T. Dinh]

Clearly, you hit the 16K limit of the earlier Excel file formats. OutputTo
uses a very old Excel file format, Excel 95, I think.

Use the TransferSpreadsheet Method and specify one of the later Excel file
format instead.

Check Access VB Help on the TransferSpreadsheet Method.

HTH
Van T. Dinh
MVP (Access)

 

[Pamela]

Thanks. This worked.