SQL database record locks

[Guest]

Our business uses the SQL based Sage business system. I have written a large
number of queries on the Sage SQL database tables using the ODBC link to
Access. This works fine for most of the tables, but one table (Works Order
Headers) always crashes my query if someone else is entering data.

I have tried to investigate this with our "Systems Partner" and with Sage
themselves but can't make any headway. I don't have any formal training in
writing queries, and most of what I have done works more by accident than
design, so I wonder if there is some better way of accessing the table that
will be more resilient to the odd locked record.

I am using Access 2002 SP3. The Sage tables are linked through a read-only
ODBC datasource. To query them, I simply add the table as a linked table to a
blank (or existing) Access database, and then just query them just as I would
a local table in the Access dbase.

Any ideas?

TIA

 

[[MVP] S.Clark]

Warning: Longshot

Each query has a property called recordset type. Try setting it to
Snapshot. This will instruct access that the query is only used for reading
data, and it may not farkle up. Unless you need write access, then this
won't be of any use to you.

 

[Guest]

Hallelujah!

Works great. Seems a tad slower, but that's a price worth paying.

Many many thanks

Vaughan