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!
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: