Record-Locking and database Performance Issues

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All:

I am using a database as a back-end to a report in Excel.
The excel front-end interprets data using queries (Written in SQL).
These SELECT queries are read and then values are entered into the
appropriate cells.

At first, the process ran extremely fast.
Now, it has sklowed down immensely and I am even receiving record-locked
(Error 3218) errors.

Is there anyway to access the database as read-only when using these queries?
I am looking to speed up the query return and avoid record-locking.

Thanks,
Brian
 
Brian said:
At first, the process ran extremely fast.
Click to expand...

You had few records and/or few users. Now there's a lot more.
Is there anyway to access the database as read-only when using these queries?
Click to expand...

You have several choices:

1) Set up a read-only DSN to the Access database and use that as your data
source.
2) Create a snapshot-type query in Access that displays what you want and
link to it from Excel.
3) Create a snapshot-type recordset (static cursor) in Excel.
I am looking to speed up the query return and avoid record-locking.
Click to expand...

If you avoid the record locking, you'll speed up the query return. Make sure
you have indexes on the join columns, criteria columns and sort columns.
Make sure the back end database is set for record level locking, not page
level locking.
 
Back
Top