Multi user working on same table - full proof method?

G

Glenn

I have a problem with record locking in a multi-user database (access
2000, DAO)

the (simplified) situation is this:
all users can add and edit their _own_ records in a central table.
the table is stored in the backend.
The table contains a primary autonumber field.
data entry/edit is done using a bound form with a currentuser
restriction on the data, (where user = [Forms]![frm]!user])

Once in a while, one user succeeds in locking out the users from the
table.

I don't yet fully understand the exact circumstances under which this
happens, but this is my guess.

As long as the users are creating new records, there seems to be no
problem.
I think the problem occurs if a user goes back to an existing record and
edits it. presumably the records around it get locked and cause
problems.

once, a user had started a edit on a record and left it in that state
for half an hour, during which every other user got the locked message.

--------
I need a solution so that every user can freely add/access/modify his
own records.
I read about (custom) record locking, but can not see how I can modify
it so that the error would stop. The record-level locking option is set
on the database.

A possible solution offered in this group, is to use a local temporary
table in the frontend which the user could edit - he'd be the only one.
Once the edits finished, the records are transferred to the backend
table.

this seems to be a rather heavy approach, plus the same problems could
occur when two people save at the same time

suggestions are welcome.
thanks
glenn
 
T

Tom Wickerath

I wonder if you are running up against this issue.....

PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60
http://support.microsoft.com/default.aspx?scid=kb;en-us;306435

_________________________________________

I have a problem with record locking in a multi-user database (access
2000, DAO)

the (simplified) situation is this:
all users can add and edit their _own_ records in a central table.
the table is stored in the backend.
The table contains a primary autonumber field.
data entry/edit is done using a bound form with a currentuser
restriction on the data, (where user = [Forms]![frm]!user])

Once in a while, one user succeeds in locking out the users from the
table.

I don't yet fully understand the exact circumstances under which this
happens, but this is my guess.

As long as the users are creating new records, there seems to be no
problem.
I think the problem occurs if a user goes back to an existing record and
edits it. presumably the records around it get locked and cause
problems.

once, a user had started a edit on a record and left it in that state
for half an hour, during which every other user got the locked message.

--------
I need a solution so that every user can freely add/access/modify his
own records.
I read about (custom) record locking, but can not see how I can modify
it so that the error would stop. The record-level locking option is set
on the database.

A possible solution offered in this group, is to use a local temporary
table in the frontend which the user could edit - he'd be the only one.
Once the edits finished, the records are transferred to the backend
table.

this seems to be a rather heavy approach, plus the same problems could
occur when two people save at the same time

suggestions are welcome.
thanks
glenn
 
G

Glenn

Tom,

that could very well be an explanation. thanks very much for that
pointer.
( I really would like to have more luck with the keywords I punch in on
the ms support site :)

Although the record-level option is set on all databases, I do not use
ADO.
I'll put a ADO connection in at the start (and try to do something
useful with it) and let the users take it for a spin.

Over the past week, I searched through numerous google messages & access
websites for a solution or workaround.
Although they are great resources, it takes time and luck to get
results. So direct input like yours is simply invaluable.
Thanks again.
Glenn


I wonder if you are running up against this issue.....

PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60
http://support.microsoft.com/default.aspx?scid=kb;en-us;306435

_________________________________________
Click to expand...
8<
 
T

Tom Wickerath

Hi Glenn,

You're welcome! Please let us know if this solves the problem for you.

Tom
_______________________________________________


Tom,

that could very well be an explanation. thanks very much for that pointer. ( I really
would like to have more luck with the keywords I punch in on the ms support site :)

Although the record-level option is set on all databases, I do not use ADO. I'll put a
ADO connection in at the start (and try to do something useful with it) and let the users
take it for a spin.

Over the past week, I searched through numerous google messages & access websites for a
solution or workaround. Although they are great resources, it takes time and luck to get
results. So direct input like yours is simply invaluable.

Thanks again.
Glenn
_______________________________________________

I wonder if you are running up against this issue.....

PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60
http://support.microsoft.com/default.aspx?scid=kb;en-us;306435
 
G

Glenn

Hello Tom,

Hey, don't forget it's sunday
oh well, you're going to get the most common reward for helpful people:
more questions :)

i've just added the included code to the autoexec macro. tomorrow the
users will use the modified version. I'll keep you informed.

But I bumped into another problem: opening the backend database from
code without hardcoding a password.

You'll easily recognize the code from the KB-document.

Opening the ADO connection reuses the password the user has entered at
the beginning.

But the DAO connection requires the password entered anew as string.
As the .Password property for DAO.User is not readable, I don't see a
way to create a DAO connection with the currentuser credentials, short
of asking the user for the password again.

To get by, I created a (temporary) sys_user just for this.

Do I miss something obvious here?

regards
glenn

==code===
Global ws_DAO As DAO.Workspace
Global db_DAO As DAO.Database
Public Function s_OpenADOConnection()

Dim cn_db As ADODB.Connection
Const c_BE_DB = "F:\BE_DB.mdb"
Const c_BE_SEC = "C:\FE\DB_SEC.MDW"
Const c_sys_user = "sys_user"

'not for develop
If f_CheckGroup(CurrentUser, "Develop") Then
Exit Function
End If

'open permanent background link to BE db
Set cn_db = New ADODB.Connection
With cn_db
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = c_BE_DB
.Properties("Jet OLEDB:System database") = c_BE_SEC
.Properties("Jet OLEDB:Database Locking Mode") = 1
.Properties("User ID") = CurrentUser
.CursorLocation = adUseServer
.Open
End With

DBEngine.SystemDB = c_BE_SEC
Set ws_DAO = DBEngine.CreateWorkspace("Workspace", c_sys_user,
c_sys_user, dbUseJet)
Set db_DAO = ws_DAO.OpenDatabase(c_BE_DB)

cn_db.Close
Set cn_db = Nothing
End Function
 
G

Glenn

8<
Opening the ADO connection reuses the password the user has entered at
the beginning.
Click to expand...
8<
Correction: the ADO also requires the password re-entered.
I had two security mdw files crossed (1 with and without passwords)

glenn
 
T

Tom Wickerath

Hi Glenn,

I don't know the answer to this question. Perhaps someone else can help.

_________________________________________________

Hello Tom,

Hey, don't forget it's sunday
oh well, you're going to get the most common reward for helpful people:
more questions :)

i've just added the included code to the autoexec macro. tomorrow the
users will use the modified version. I'll keep you informed.

But I bumped into another problem: opening the backend database from
code without hardcoding a password.

You'll easily recognize the code from the KB-document.

Opening the ADO connection reuses the password the user has entered at
the beginning.

But the DAO connection requires the password entered anew as string.
As the .Password property for DAO.User is not readable, I don't see a
way to create a DAO connection with the currentuser credentials, short
of asking the user for the password again.

To get by, I created a (temporary) sys_user just for this.

Do I miss something obvious here?

regards
glenn

==code===
Global ws_DAO As DAO.Workspace
Global db_DAO As DAO.Database
Public Function s_OpenADOConnection()

Dim cn_db As ADODB.Connection
Const c_BE_DB = "F:\BE_DB.mdb"
Const c_BE_SEC = "C:\FE\DB_SEC.MDW"
Const c_sys_user = "sys_user"

'not for develop
If f_CheckGroup(CurrentUser, "Develop") Then
Exit Function
End If

'open permanent background link to BE db
Set cn_db = New ADODB.Connection
With cn_db
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = c_BE_DB
.Properties("Jet OLEDB:System database") = c_BE_SEC
.Properties("Jet OLEDB:Database Locking Mode") = 1
.Properties("User ID") = CurrentUser
.CursorLocation = adUseServer
.Open
End With

DBEngine.SystemDB = c_BE_SEC
Set ws_DAO = DBEngine.CreateWorkspace("Workspace", c_sys_user,
c_sys_user, dbUseJet)
Set db_DAO = ws_DAO.OpenDatabase(c_BE_DB)

cn_db.Close
Set cn_db = Nothing
End Function
 
T

Tom Wickerath

Hi Glenn,

You might try this KB article to see if it helps:

HOW TO: Open a Password-Protected Database Through Automation in Access 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;235422

If that doesn't do it, I suggest re-posting as a new question.

Tom
____________________________________________


Hello Tom,

Hey, don't forget it's sunday
oh well, you're going to get the most common reward for helpful people:
more questions :)

i've just added the included code to the autoexec macro. tomorrow the
users will use the modified version. I'll keep you informed.

But I bumped into another problem: opening the backend database from
code without hardcoding a password.

You'll easily recognize the code from the KB-document.

Opening the ADO connection reuses the password the user has entered at
the beginning.

But the DAO connection requires the password entered anew as string.
As the .Password property for DAO.User is not readable, I don't see a
way to create a DAO connection with the currentuser credentials, short
of asking the user for the password again.

To get by, I created a (temporary) sys_user just for this.

Do I miss something obvious here?

regards
glenn

==code===
Global ws_DAO As DAO.Workspace
Global db_DAO As DAO.Database
Public Function s_OpenADOConnection()

Dim cn_db As ADODB.Connection
Const c_BE_DB = "F:\BE_DB.mdb"
Const c_BE_SEC = "C:\FE\DB_SEC.MDW"
Const c_sys_user = "sys_user"

'not for develop
If f_CheckGroup(CurrentUser, "Develop") Then
Exit Function
End If

'open permanent background link to BE db
Set cn_db = New ADODB.Connection
With cn_db
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = c_BE_DB
.Properties("Jet OLEDB:System database") = c_BE_SEC
.Properties("Jet OLEDB:Database Locking Mode") = 1
.Properties("User ID") = CurrentUser
.CursorLocation = adUseServer
.Open
End With

DBEngine.SystemDB = c_BE_SEC
Set ws_DAO = DBEngine.CreateWorkspace("Workspace", c_sys_user,
c_sys_user, dbUseJet)
Set db_DAO = ws_DAO.OpenDatabase(c_BE_DB)

cn_db.Close
Set cn_db = Nothing
End Function
 
G

Glenn

I wonder if you are running up against this issue.....

PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60
http://support.microsoft.com/default.aspx?scid=kb;en-us;306435

_________________________________________
Click to expand...
8<

Hi Tom,

Just to let know what happened next...

Even with the temporary workarounds, problems still occurred. But now
they ALL happened to the same user/pc.
It turned out that the office suite on that pc ( an old one) was still
the original 2000 install without ANY service release.

Which made the KB base doc Q238258 a very likely contender:
PRB: ACC2000: Access Database Does Not Use Record-Level Locking When
Started from a Windows Shortcut
http://support.microsoft.com/?id=238258

to complete the picture, that pc suffered also from an iffy connection
to the LAN, caused by something internal to the machine other than the
NIC card.

I reverted my application back to the original clean code without
workarounds, had it tested by all users during 3 day with exception from
that one pc. No problems since.

My recommendation: have the pc checked or better yet, put out with the
garbage.
It has cost me at least a week of my precious life! Yes, I am a vengeful
user :)

Bye
glenn
 
G

Glenn

Hi Glenn,

You might try this KB article to see if it helps:

HOW TO: Open a Password-Protected Database Through Automation in Access 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;235422

If that doesn't do it, I suggest re-posting as a new question.

Tom
____________________________________________
Click to expand...
8<
Thanks for your help.
Close but no cigar.
This example from microsoft 'cheats' in the same way as all the other
examples I've seen dealing with passwords & connections: the password is
hardcoded, thus known at design time and assumed unchangeable.

For me, a password is just the opposite: A, known only to the user and
B, changeable at all time.
This code and the others fail on both counts.

as you suggested I have posted another message in the access.security
group on this specific subject.

thank you for taking the time to have followed this through. I really
appreciate it.
Glenn
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

two forms, same table 5
Exclusive use only in Vista? 0
Multi User: Record lock while accessing same table 1
Linked tables or Local whats best? 6
ACCESS 2003 SP3: "The database has been placed in a state by user 4
trying to minimize Write Conflicts in a multi-user database 62
Slow in saving record in network, multi-user platform. 1
Multi-user enviroment form design 1

Top