Unanswered: IDENTITY column in SQL 2000 and linked tables in MS Access
We have an application written in MS Access. The tables are linked to a SQL 2000 database.
The problem is that sometimes insert a new record in a table freezes and times out after a while without anything has happened.
When installing the application the *mdb file is copied over to the C drive and an ODBC connection is written to the registry.
The application is used by many in the company.
We have problems on tables defined with IDENTITY columns. Can this be our problem and how can we solve it?
The problem is that Access does not work will with large numbers of users. Anything above six to ten simultaneous users can cause problems.
I assume you have a form linked to a table or a view in your SQL Server database. Well, Access likes to copy down the ENTIRE recordset so that you can step through the results. When you have several people who each have loaded their own local copies of the same recordset and then try inserting a new record, I'd guess it plays havoc with the locking.
You might try having each user download a filtered subset of the data, reducing the number of copies of the same record that are spread over multiple terminals.
If it's not practically useful, then it's practically useless.
Which, in my opinion, takes away most of the advantages of using MS Access.
Apart from it being a one stop shop GUI\ report generator that can be distributed with xcopy you mean? A disconnected Access app is by no means the best solution in many situations but I would use one over a linked Access version any day**. And I would use Access over (for example) .NET in many circumstances too. In fact I do.
EDIT - ** Actually I got over excited and fibbed there. A disconnected app might be overdesigned for many applications (e.g. those that are accessed by a very small number of people).