S
Scott Lichtenberg
I just read Arvin Meyer's and Pat Hartman's replies on the ADP Strengths
thread which discussed using MDBs instead of ADP for SQL projects. Taking
that as a starting point, I have some questions regarding the best
methodology to use when creating a client server application.
I currently have an SQL back end, with Access97 front ends. We've been
using this (quite happily) for about 10 years. We've just upgraded SQL from
6.5 to 2005, so we are looking to bring Access into the new century as well.
One of the issues we ran into when we developed our apps was that we had
terrible locking problems when we linked SQL tables into Access. If we used
a linked table as a recordsource for forms, combo boxes, etc., Access would
read about 100 records, and then hold the table open (with locks) until the
user moved to the end of the recordset. To get around this, we developed a
methodology where we created a copy of the SQL table in Access, then copied
records into it. We used this table for the form's recordsource. When a
record was edited/added, we pushed back the change to the SQL server. Kind
of a Flintstones' version of ADO disconnected recordsets.
Arvin and Pat have settled the issue of ADP vs MDB, but it leaves me with a
couple of questions, the important of which is:
Should I develop forms which use the linked tables and queries as row
sources? If I do this, am I going to run into the same locking issues as I
had in A97? I've noticed that if you open a pass through query against a
table with 10,000 records, Access 2003 does not pull in all the day. There
will be about 100 users for my system, so locking is a major concern.
Thanks in advance for any help.
Scott Lichtenberg
thread which discussed using MDBs instead of ADP for SQL projects. Taking
that as a starting point, I have some questions regarding the best
methodology to use when creating a client server application.
I currently have an SQL back end, with Access97 front ends. We've been
using this (quite happily) for about 10 years. We've just upgraded SQL from
6.5 to 2005, so we are looking to bring Access into the new century as well.
One of the issues we ran into when we developed our apps was that we had
terrible locking problems when we linked SQL tables into Access. If we used
a linked table as a recordsource for forms, combo boxes, etc., Access would
read about 100 records, and then hold the table open (with locks) until the
user moved to the end of the recordset. To get around this, we developed a
methodology where we created a copy of the SQL table in Access, then copied
records into it. We used this table for the form's recordsource. When a
record was edited/added, we pushed back the change to the SQL server. Kind
of a Flintstones' version of ADO disconnected recordsets.
Arvin and Pat have settled the issue of ADP vs MDB, but it leaves me with a
couple of questions, the important of which is:
Should I develop forms which use the linked tables and queries as row
sources? If I do this, am I going to run into the same locking issues as I
had in A97? I've noticed that if you open a pass through query against a
table with 10,000 records, Access 2003 does not pull in all the day. There
will be about 100 users for my system, so locking is a major concern.
Thanks in advance for any help.
Scott Lichtenberg