Output to Excel causes Access crash

[scottyjohn]

Hi all,
I have 2 different queries which populate forms to display the results. On the results forms for each of these queries there are buttons which use the "output to" function to export the data to Excel. The first query returns about 7000 rows and runs fine when you export to Excel. The second returns 13500 records and runs fine. BUT when you try to export that form to Excel, it just hangs the Access interface and wont complete. The buttons call macros which have the following values..
OUTPUT TO
QUERY
qryGLASGOWAGENTSKILLS
MICROSOFT EXCEL (*.xls)
AutoStart Yes.

The queries and macros and forms are all practically identical, any ideas why this is happening?

John
ski_69@hotmail.com

 

[jimlee]

I had this problem, I found that 'transfer database' was quicker than 'output to'

jimlad

 

[jimlee]

sorry, i meant 'transfer spreadsheet' !

jimlad

 

[scottyjohn]

Hi,
The problem is that "transfer spreadsheet" does nopt allow the user to specify the output file name and then display the results in Excel automatically.

John
ski_69@hotmail.com

 

[easyit]

You can very well transfer data to excel with transferspreadsheet and give it name& location:
with a macro in designmode look left below on the window:
it says filename etc

In VBA:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyTabelOrQueryName", "MypatnhName_&Filename" , True
If you want to give your users the ption to specify a filename and location you must use VBA.

My question to you is Are you sure Access Hangs? Because there are limitations on the number of records that you can export to excel, due to compatibility with previous excel versions. Does your form use VBA? What is the code? Do you export the form or the queries?

 

[OhioSteve]

I tried something similar and it worked fine.

I the first two things to check would be query type and size. I think Excel still caps worksheets at about 70,000 rows. Also, verify that it is a select query, not an action query.

 

[easyit]

Excel does, but Access doesn´t:

http://support.microsoft.com/default.aspx?scid=kb;en-us;291951

Also,

I have had seevral cases where exporting was very (looked like a hanging program) slow. Changeing to VBA solved those problems.

 

[scottyjohn]

Hi guys,
Got it working (kind of) in that in the macro I now use a second function to open the excel document after its written. Not ideal as it still has to save the file to a hardcoded location beforehand.
The query which it calls is fairly straightforward....

SELECT [PERSON].[EMPLOYEE_ID], [SKILL].[NAME], [SKILL_LEVEL].[LEVEL_]
FROM (PERSON INNER JOIN SKILL_LEVEL ON [PERSON].[DBID]=[SKILL_LEVEL].[PERSON_DBID]) INNER JOIN SKILL ON [SKILL_LEVEL].[SKILL_DBID]=[SKILL].[DBID]
ORDER BY [PERSON].[EMPLOYEE_ID];

And it does only return 13500 records so nowhere near the XL limit. Im not really familiar with VB, and have been meaning to learn for a while! Looks like I should hit the books :



John
ski_69@hotmail.com

 

[OhioSteve]

In passing, I would note that there is a downside to opening the document in Excel. My users got confused when I did that. If excel opened immediately, then they could not differentiate between the DB and Excel. They thought that they could change a cell in Excel and that would change the DB. So I purposefully hardcoded it to go to a specific location (the root of "c") and not to open Excel. Then they understood that the spreadsheet was a separate output file. But perhaps your users are much smarter than mine.