Access 2007 vs 2010 performance

Hello1

Registered User.
Local time
Today, 13:08
Joined
May 17, 2015
Messages
271
I run into a weird problem.
Im building my front-end in .accdb with Access 2007 and the back-end is a mixture of SQL Server and .mdb. However, in this particular case only the .mdb database is being used.
Running a simple append query from VBA, (Im copying records from the back-end .mdb table into a temporary front-end table) takes Access 2007 about 9 seconds to finish, while running the same one in Access 2010 takes about 2 minutes and 23 seconds (approximately 95k records being copied).
I tried compact and repair on both, front and back end but same.
Tried this suggestion too 1. Set your Default record locking setting to Edited record, then set the Open databases by using record-level locking to no but no luck.
Any ideas?


EDIT:
I have to correct myself. The query itself does take longer but the real time consumer is the report formatting.
Its a report in question but before running it I run the mentioned queries to prepare it.
Also, indexing the fields which are used in criteria and for sorting didnt help either. I added them into both tables the .mdb and the temporary one.

Thanks!
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:08
Joined
Sep 12, 2006
Messages
15,614
Definitely make sure the slow version can see the printer correctly You can get problems if it can't.
 

Hello1

Registered User.
Local time
Today, 13:08
Joined
May 17, 2015
Messages
271
Im not quite sure I understood. Im only opening it in print preview to check it out and the printers seem to be alright, I mean I can see them they are available.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:08
Joined
Feb 19, 2002
Messages
42,981
Using make table queries bloats the FE and is rarely needed for a report. Have you tried binding the report to the query that selects the data for the make table?
 

Hello1

Registered User.
Local time
Today, 13:08
Joined
May 17, 2015
Messages
271
Using just a query is way too slow in my case. The backend has more than 6 million records and I take data from another table also, depending on the date.. So thats why the use of the temporary table. The union query would be just too slow when it merges data from a .mdb database and a SQL Server one.
 

Hello1

Registered User.
Local time
Today, 13:08
Joined
May 17, 2015
Messages
271
For now I have put back Access 2007 because its way faster, up to 20 times in this case. When I find more time I might try more options with Access 2010
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:08
Joined
Feb 19, 2002
Messages
42,981
I have lots of apps with large BE's. Are the tables indexed as they need to be? Jet/ACE automatically create a hidden index for each FK. With SQL server, you need to make your own. You should also index columns that are frequently used as criteria. Sometimes creating a view speeds up a join.

I agree that joining tables from the Jet/ACE BE or FE to SQL server will normally be slow but if your criteria is on the large server side table, only the requested rows should be returned.

Be careful to avoid UDF and VBA functions. They can interfere with Access' ability to "pass through" a query. If you have the correct permisions, you can monitor the traffic between Access and the server to see what queries it is sending to the Server.

FYI, rather than buying specific versions of Access, it is probably better to go with the flow and use the O365 subscription so you are always working with the current version.

Also, why is the BE an .mdb and not an .accdb?
 

Users who are viewing this thread

Top Bottom