Don't understand Microsoft's work around

[tgavin]

I have a database with about 25 not very computer literate users. They are
not all on at one time. On just one (very important) form, they sometimes get
the message "Currently locked; can not update." This is a long form with a
subform in the middle of it. They generally lose the record and that includes
a 200+ memo they have written.

Microsoft says it is a known bug and give a work around but I don't
understand the work around. I don't know where to put an code and I don't
know how to change from the Jet engine to

The work around...

To work around this problem, use one of the following methods:

Use Microsoft OLEDB Provider for ODBC Drivers (MSDASQL) with the Microsoft Access Driver, instead of with Microsoft OLE Provider for Jet 4.0.
Use the ADODB.Recordset object for subsequent edits, instead of an ADODB.Connection obect, or an ADODB.Command object.
Use client-side cursor to access the data. For example:

Dim cn As New ADODB.Connection
cn.CursorLocation = adUseClient
-or-
Dim rs As New ADODB.Recordset
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient

Your help would be greatly appreciated. Thanks.

 

[Tom Wickerath]

Hi tgavin,

You didn't provide a URL for the article that you referenced (perhaps KB
331594: http://support.microsoft.com/kb/331594?). I'm fairly certain that
Microsoft Access cannot use the OLEDB provider to connect to a JET (ie. .mdb)
database. This KB article appears to apply to other application software,
including Visual Basic 6.0 mentioned in the section "Steps to Reproduce the
Behavior", which can use the OLEDB provider to communicate with a JET
database. So, if you are using Access for your application portion (ie.
queries, forms, reports, etc.), then I'm pretty sure this article does not
apply.

Only one person can edit a given record at a time, so are you saying that
this problem occurs when two or more people are in the database, but are
editing *different* records?

Is your application split into two .mdb files, ie. a Front-End.mdb (FE) and
a Back-End.mdb (BE)? Is a copy of the FE.mdb installed on each user's local
hard drive, so that no two users can ever be sharing the same FE?

Split the Database
http://www.access.qbuilt.com/html/gem_tips1.html#SplitDB

Here is an article that provides lots of suggestions as well:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

Also, for JET databases, I suggest isolating fields of the Memo, Hyperlink
and OLE Object data type into a separate table. You can do this by creating a
1:1 relationship, where you join two primary key fields in a relationship
(although one of them will be considered the foreign key, so the direction
you drag is important for 1:1 relationships). Or, possibly your business
rules will allow for a more standard 1:M (one-to-many) relationship, also
allowing you to isolate the Memo field to a table of it's own, but allowing
many child memo records for each parent record.

The following quote is one reason I like to separate these data types to an
isolated table:

"A limitation to record-level locking is that users will not be notified
when another user is locking the record.†“Also, record-level locking is not
enabled for Memo data types."
Source: http://support.microsoft.com/kb/275561, under the title:
"Record-level locking".

Note: In some tests I remember running a long time ago, I did not see
evidence of this limitation, but, hey, Microsoft says it so it is worth
considering.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[tgavin]

Tom, thanks. I will try making it 2 tables...I never would have thought of
the memo field as a problem.

It is already split, we wrote code to put a separate, new copy of the front
end on each users desktop and the remote desktops each time they log on to
try to solve this problem. And they are usually creating a new record
whenever they go into it, plus the records are filtered for social worker
since they are not suppose to be able to see cases that are not their's, so
there is no chance of 2 people working on the same record.

I won't be able to make these changes till the overnight since the db is
always in use, but I will touch base Monday after I see if it works. Any
other ideas would be welcome. I have been posting versions of this and
working on it for months and this is the first answer I have gotten. Thanks!!!

 

[Tom Wickerath]

Gosh, if two users are never editing the same record, then you shouldn't be
getting this error. By isolating the memo field to it's own table, once the
user clicks into a text box, on your form to enter memo text, the main record
should be automatically saved. You might want to implement some code that
prevents a user from attempting to enter the memo text *before* they enter
any other data on the form, so that you don't end up with a RI (Referential
Integrity) violation. [I'm assuming here that all of the relationships in
your database include the Enforce Referential Integrity option checked].

Also, make sure that all PCs used to open your application are properly
updated. See the Best Practices section, on page 1 of a Word document that I
call "Access Links". You are welcome to download a zipped copy from my web
site.

It is already split, we wrote code to put a separate, new copy of the front
end on each users desktop and the remote desktops each time they log on

No need to write your own code, if you are willing to give Access MVP Tony
Toews' free AutoFE Updater utility a try. Check it out, here:

http://www.autofeupdater.com/

and here: http://www.autofeupdater.com/gettingstarted.htm

I hope it works out for you. Please do report your results back.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[David W. Fenton]

See the Best Practices section, on page 1 of a Word document that
I call "Access Links".

Can't you make a PDF of that?

 

[tgavin]

Tom, I thought the problem was solved because peopl did not get back to me,
but it is still happenning. Some people are running with Access 2007, others
with 2003. Can that make a difference? This is driving me bananas.

Gosh, if two users are never editing the same record, then you shouldn't be
getting this error. By isolating the memo field to it's own table, once the
user clicks into a text box, on your form to enter memo text, the main record
should be automatically saved. You might want to implement some code that
prevents a user from attempting to enter the memo text *before* they enter
any other data on the form, so that you don't end up with a RI (Referential
Integrity) violation. [I'm assuming here that all of the relationships in
your database include the Enforce Referential Integrity option checked].

Also, make sure that all PCs used to open your application are properly
updated. See the Best Practices section, on page 1 of a Word document that I
call "Access Links". You are welcome to download a zipped copy from my web
site.

It is already split, we wrote code to put a separate, new copy of the front
end on each users desktop and the remote desktops each time they log on

No need to write your own code, if you are willing to give Access MVP Tony
Toews' free AutoFE Updater utility a try. Check it out, here:

http://www.autofeupdater.com/

and here: http://www.autofeupdater.com/gettingstarted.htm

I hope it works out for you. Please do report your results back.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Tom, thanks. I will try making it 2 tables...I never would have thought of
the memo field as a problem.

It is already split, we wrote code to put a separate, new copy of the front
end on each users desktop and the remote desktops each time they log on to
try to solve this problem. And they are usually creating a new record
whenever they go into it, plus the records are filtered for social worker
since they are not suppose to be able to see cases that are not their's, so
there is no chance of 2 people working on the same record.

I won't be able to make these changes till the overnight since the db is
always in use, but I will touch base Monday after I see if it works. Any
other ideas would be welcome. I have been posting versions of this and
working on it for months and this is the first answer I have gotten. Thanks!!!

 

[Larry Linson]

Neither spam (which I take this to be) nor site reviews are appropriate for
a newsgroup devoted to technical discussion of and questions and answers
about, Microsoft Access database software, which this newsgroup is. But,
just FYI, it's not likely to be a good source for online gamers and people
seeking online fun, either.

Larry Linson
Microsoft Office Access MVP

 

[hamamayj]

yryre

Tom, I thought the problem was solved because peopl did not get back to
me,
but it is still happenning. Some people are running with Access 2007,
others
with 2003. Can that make a difference? This is driving me bananas.

Gosh, if two users are never editing the same record, then you shouldn't
be
getting this error. By isolating the memo field to it's own table, once
the
user clicks into a text box, on your form to enter memo text, the main
record
should be automatically saved. You might want to implement some code that
prevents a user from attempting to enter the memo text *before* they
enter
any other data on the form, so that you don't end up with a RI
(Referential
Integrity) violation. [I'm assuming here that all of the relationships in
your database include the Enforce Referential Integrity option checked].

Also, make sure that all PCs used to open your application are properly
updated. See the Best Practices section, on page 1 of a Word document
that I
call "Access Links". You are welcome to download a zipped copy from my
web
site.

It is already split, we wrote code to put a separate, new copy of the
front
end on each users desktop and the remote desktops each time they log on

No need to write your own code, if you are willing to give Access MVP
Tony
Toews' free AutoFE Updater utility a try. Check it out, here:

http://www.autofeupdater.com/

and here: http://www.autofeupdater.com/gettingstarted.htm

I hope it works out for you. Please do report your results back.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Tom, thanks. I will try making it 2 tables...I never would have thought
of
the memo field as a problem.

It is already split, we wrote code to put a separate, new copy of the
front
end on each users desktop and the remote desktops each time they log on
to
try to solve this problem. And they are usually creating a new record
whenever they go into it, plus the records are filtered for social
worker
since they are not suppose to be able to see cases that are not
their's, so
there is no chance of 2 people working on the same record.

I won't be able to make these changes till the overnight since the db
is
always in use, but I will touch base Monday after I see if it works.
Any
other ideas would be welcome. I have been posting versions of this and
working on it for months and this is the first answer I have gotten.
Thanks!!!