Query is outputting slow only when using transferspreadsheet method or append query

I have a query that runs fast enough in if I'm just viewing it. It's returning about 29K records. in 20-30 seconds. I can view any record, etc. Tables have indexes. My problem is when I try to output it using transfer spreadsheet or make the query an append query it's taking 30+ minutes to run. I haven't had this problem before. I've done more complex queries and they haven't given me this trouble. Could bitlocker be causing this problem?
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
If you move the application to another machine that does not have BitLocker, does the issue go away? If so, then you could perhaps conclude that BitLocker is the culprit.

Is this a split application - that is, you have a Backend (Tables only) and a FrontEnd (everything else)? If it is, try moving the Backend to the local machine temporarily and see it that speeds up your process. If it does, then the network is the bottleneck and you'll need to take steps to improve the speed and reliability.

Converting to an Append query often causes performance issues since Access needs to do quite a bit of work behind the scenes to insure the query can be executed. Essentially, Access checks all Indexes and Foreign Key fields to insure they comply with all "rules", and also checks things like Defaults, field lengths/types etc before running the append.

Can you give more information about the table? Number/type of fields, for example? Any Memo, Attachment or OLE fields?
stopher2475Author Commented:
I moved the backend to my desktop. I'll try it on another machine when I get home tonight. I was going to bring in my personal machine and try that. No memo or attachment fields I see but I will take your suggestion and look through the tables. I might have missed something.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I moved the backend to my desktop.
Any change after you did that?
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

stopher2475Author Commented:
No it's still real slow. The odd thing is I can view the query without much trouble. It only takes a long time when I try to output it to excel with copyrecordset or I use an append version of the query. Target table only has 2 indexed fields. Could the indexes be corrupted or something. I tried compacting and repair. I'm trying to copy it to a personal laptop now because my home desktop has a newer 64bit version of office that isn't playing nicely. May have to install access first. Will update on progress soon.
stopher2475Author Commented:
Tried to run it on my personal laptop. Still really slow. Not sure why. It's not a very complicated query.
I finally go it runnable by doing updates on 2 of the tables for some lookups rather than joins. That let me remove 2 tables from the query and now the thing outputs to excel in 10 seconds rather than 30 minutes. Here's something I've noticed. When it outputs to excel I have a whole lot of the columns that are numbers stored as text. Would that have an effect on output speed to excel?
I also had a join field getting truncated so I had 29K records with no match which I think slowed it down.
I recommend that you instantiate an Excel.Application object and use the CopyFromRecordset Range method to transfer your data.  There are other methods I've detailed in my Fast Data Push to Excel article (http:A_2253.html ), but most people find the recordset approach the simplest.
stopher2475Author Commented:
I am using the CopyFromRecordset method. Sorry I had put TransferSpreadsheet by mistake.
My final solution was to load the add blank fields to the table I was joining two tables to in the final query and doing updates rather than joins. It runs in a minute or so and doesn't hang for45 min. I guess it's just more efficient than 2 outer joins.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.