CopyFromRecordset method slow on some pcs (1 Viewer)

[stepone]

Hi folks,

I have written a VBA procedure (in Excel VBA) to open an access database using the DAO opendatabase method, and then loop through a list of SQL queries, using .copyfromrecordset to copy the query results onto separate tabs in the Excel workbook.

This works great on my pc, and, to run through the list of around 30 queries and copy them all over takes around 30 seconds.

However, when I get the users to run it, it takes a lot longer. It can be anything from 2 or 3 minutes to 10 or more minutes to do exactly the same bit of code.

They've all had new pcs recently and the pc specs are in fact better than my laptop. In all cases the Excel sheet and database sit on the C: drive, so it's not network issue. We are all using Windows 7 and Office 2013 with 4GB of RAM.

We run these jobs every day - it's the bread and butter of the company, so if I could save a few minutes for each one then it will make a massive difference.

Does anyone have any suggestions as to what might be causing this ?

Thanks very much
Pete.

 

[stepone]

Dear All,

Just wanted to say that I tried Compact and Repairing the database, and the difference was incredible ! I ran the routine on one pc and it took 4 minutes 50 secs. I then did Compact and Repair on the database , and when I ran the routine again it was down to 4 seconds - i.e. a 98% improvement in performance from doing one compact and repair !

I can't quite believe that I did not try that earlier ! :banghead:

Anyway, I thought I would post this finding in case anyone found it useful.

Regards,
Peter.

 

[namliam]

I thought about suggesting that but didnt want to block the thread with such an obvious suggestion that you would have tried already

 

[CJ_London]

the other thing you can do is to decompile the front end then recompile.

If you've not done it before, you'll be surprised how much smaller the front end becomes - I've seen reductions of 90% in the past.

Recommend decompiling on a regular basis whilst in development and a final time before deployment.

The easiest way to decompile is to do the following:

1. create a shortcut to access.exe - this will depend on what version of windows/office but typically something like

"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE"

2. edit the shortcut target to add a space and /decompile at the end and save e.g.

"C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" /decompile

3. move the shortcut to your desktop or some other convenient location.

4. to decompile simply click on the shortcut (which will open access in decompile mode) and select the front end - hold down the shift key to prevent any autostart forms/processes running.

5. Next compact and repair, again holding down shift key to prevent autostart running

6. finally, go to database tools>visual basic and in the vba window, select debug>compile - and you are all finished, just close the db

for other db's repeat steps 4-6

 

[Galaxiom]

The easiest way to decompile is ...

Actually the easiest is to add Shell Context menu entry. I have entries for both Decompile and Compact. Simply right click on the database file and choose the treatment.

The reg file for Access 2010 32 bit on 64 bit Windows with default installation path is attached.

Run it at your own risk.