Access 97 crash on Server 2003

If you would run Access 97 application on Server 2003 SP1 or SP2 – you probably experience a crash of Access while pressing a button, whatever. In order to make it running – you have to either switch off Data Execution Prevention (DEP), or add Access to exceptions list in DEP dialog.

Export to Excel - There are too many rows to output

I was just hit by Run-time error 2306: “There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access”, produced by this line:

DoCmd.OutputTo acOutputQuery, "MyQuery", acFormatXLS, , True

Query has about 18000 records, what should not be a problem for Excel. Fortunately I found that replacing acFormatXLS with acSpreadsheetTypeExcel9 did the trick, so this line would work:

DoCmd.OutputTo acOutputQuery, "MyQuery", acSpreadsheetTypeExcel9, , True

Interesting that using acSpreadsheetTypeExcel9 is not documented in online help or object browser, and both constants has quite different values:

Const acSpreadsheetTypeExcel9 = 8
Const acFormatXLS = "Microsoft Excel (*.xls)"

Anyway – it works! If anybody have explanation to this – would be great to learn.

BTW - DoCmd.TransferSpreadsheet will also work in this case.

Export mixed type data to Excel

While we have already knew how to deal with Import or Link mixed type data from Excel, here a trick on export. Today I got a screenshot from customer, who tried to export Access report to Excel and got “Type mismatch” error. Screenshot showed a typical mixed data type column with document numbers, some of them with numbers only, and other started with letters. So export engine made an assumption on a first row that column type is numeric, and later produced the error on non-numeric data. Well, this I can’t say for sure - this was my guess. Anyway – I have opened report’s underlying query and added a space at the end of document number field: Select …. DocNum & “ “ as DocNum, .... And error gone!

Technorati tags: Access, Excel, Export, Error