Currently using MS Access 2000 and SQL Server Express. Using the
current DAO OpenRecordset code listed below, however I keep getting
the error message....
3254: ODBC --Cannot lock all records

The code below runs in a MS Access frontend while the table is linked
using an ODBC connected to an SQL Server (backend). The table
(located in SQL) only has one row and one column within it . I want
to try and stop any other request (other frontend clients) from being
able to access the record while someone is getting the case number (a
unique number).

This processed worked while all the tables were located in MS Access,
but does not work since the tables have been upgraded. While in MS
Access, all new request to access the table was given an chance to get
a number once the previous client call was completed [would loop them
in the error section until the table was released]

However just not sure if the SQL table is setup wrong, or I need to
use a different type of option within the DAO openRecordset command.
Can't upgrade the system to ADO, not enough time....

Would be thankful to any help that could be provided.....Here is the
code:

Use ADO instead of ODBC when interfacing between Access and Sql Server.

Disclaimer: There are people in this NG who seem to have a problem
either with using ADO or with me suggesting to use ADO over ODBC. My
reply here is based on years of experience using ADO instead of ODBC for
manipulating data on a sql server from Access. I have had significantly
less problems with ADO than with ODBC (and even less than that with
ADO.Net except that ADO.Net only works in a .Net environment).

Here is some sample usage (need to make a reference in Tools/References
to Microsoft ActiveX Data Objects 2.x Library -- need 2.5 or higher):

The code below runs in a MS Access frontend while the table is linkedusing an ODBC connected to an SQL Server (backend). The table(located in SQL) only has one row and one column within it . I wantto try and stop any other request (other frontend clients) from beingable to access the record while someone is getting the case number (aunique number).

This processed worked while all the tables were located in MS Access,but does not work since the tables have been upgraded. While in MSAccess, all new request to access the table was given an chance to geta number once the previous client call was completed [would loop themin the error section until the table was released]

However just not sure if the SQL table is setup wrong, or I need touse a different type of option within the DAO openRecordset command.Can't upgrade the system to ADO, not enough time....

Would be thankful to any help that could be provided.....Here is thecode:

Out of curiosity:
* If you think ADO is superior when working with a SqlServer backend,
would you not use ADP, which is a more pure ADO environment?
* If using MDB + ADO, how do you use bound forms? Me.RecordSource =
rs?

Thanks,

-Tom.
Microsoft Access MVP

>Use ADO instead of ODBC when interfacing between Access and Sql Server.

Disclaimer: There are people in this NG who seem to have a problemeither with using ADO or with me suggesting to use ADO over ODBC. Myreply here is based on years of experience using ADO instead of ODBC formanipulating data on a sql server from Access. I have had significantlyless problems with ADO than with ODBC (and even less than that withADO.Net except that ADO.Net only works in a .Net environment).

>>
Out of curiosity:
* If you think ADO is superior when working with a SqlServer backend,
would you not use ADP, which is a more pure ADO environment?
* If using MDB + ADO, how do you use bound forms? Me.RecordSource =
rs?

Thanks,

-Tom.
Microsoft Access MVP
<<

Also based on years of experience, I have had more issues with ADP's
than with mdb's. If I have a large project that uses an ADP - I will
just migrate the whole thing over to .Net. But for smaller projects
which still use a sql server for the backend and Access for the
frontend, I find that using an mdb is easier to develop than the ADP -
plus you are in the native Access environment and only need ADO for
pulling/pushing and carrying out data edits.

The mdb is more like a pseudo .net app in that with ADO you are using
disconnected recordsets like in .Net -- except that instead of doing
everything in memory you do have I/O on the disk. But mdb tables
function pretty much the same as .Net dataTables - again - except for
the part where the mdb tables are persistent (written to the disk) and
the .Net tables are all in memory only. And with this configuation -
you can still use DAO within the mdb. There is no DAO with the ADP.