Access2000 Fails to Open as Multiuser Database

[ortaias]

The Access2000 database is on a remote server. Our office has a mixture
of WindowsXP and Windows2000 as the operating system. We are using
Novell Netware for the office network. I am wondering if the Novell
Network is "confusing" Access???

The check box option to open Access2000 as a multiuser database is
checked. Also "No locks" is checked. When the database is opened we
get the "LBD" file with "access.lockfile.9". When I look at this file,
I see my name and the word "Admin", which seems normal. However, if
another user attempts to open the database, they can't and get a
message that another user is using the program.

According to Micorsoft, Access must be in a shared directory. Since
everyone can read/write to the directory it appears "shared".
Nevertheless, is it possible that the Novell Network has a conflict
which presents Access from being shared?

 

[Guest]

What do you mean by "remote server"? Is it a server on your network or
somewhere more distance like on a WAN in another building, city, or state?

Novell use to have record locking problems with Access back in the day;
however, that shouldn't be a problem anymore. Anyway that was record locking
and not file locking.

You say read/write. How about create and delete? Need them too.

 

[ortaias]

The server is a local network connection.

Users have create/delete permissions on the networked directory.

I brougnt my Flash USB device to work, I will copy the database to my
home network tonight and see if I have multiuser access.

 

[ortaias]

I took the database home and had limited sharing success. First, the
folder that access is in must be fully labeled as shared, read/write
privileges by themselves don't seem to allow multiuser access. Second,
after closing the database, I would get a "can't find" the database
message from windows explorer even though the file was visible.

 

[ortaias]

I took the database home and had limited sharing success. First, the
folder that access is in must be fully labeled as shared, read/write
privileges by themselves don't seem to allow multiuser access. Second,
after closing the database, I would get a "can't find" the database
message from windows explorer even though the file was visible.

 

[ortaias]

The following code derived from "Programming Microsoft Access Version
2002" By Rick Dobson has partially resolved the issue of getting the
database open as a multiuser database.
-------------------------------------------------------------------------------------------------------------------
str1 = "Provider=microsoft.jet.oledb.4.0;" & "data source=P:\Federal
Consistency\consistency.mdb"
Rem -------------------------------------------------------------
Rem initialize dataset
Set cnn1 = New ADODB.Connection
Set rst1 = New ADODB.Recordset
cnn1.Open str1
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
rst1.Open "projectnumqry", cnn1
DoCmd.Maximize

Rem -----------------------------------------------
Rem cleanup code
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
----------------------------------------------------------------------------------------------------------------------------
First it appears that this code needs to be placed whenever a form is
opened which does not make sense. Apparently subsequent forms do not
inherit the record lock property which results in the error message
that the database is "locked". Also I suspect that the cleanup code
should actually be run when the form is closed. Opening the form with
"Me.recordlocks=0" did NOT seem to have an effect.

 

[G. Vaught]

To truly use Access as a multi-user database your database should be split.
The back-end should reside on a server type machine and the front-end
resides on each users machine. If you do not do this, then the database is
subject to corruption and total loss.

Access has a built-in database splitter that will walk you through the
process. However, before you do this, make a copy of the database in its
present form.

 

[ortaias]

Thanks. This is proving to be more complex than implied by the
documentation. I have "fixed-up" opening the ADODB connection code so
that it opens with the database and closes with the database. However,
I am now getting an "object variable or with block variable not set"
error when running a "for each loop", which I think is being triggered
from the "refrences" dialog box. I am going to play with the
references settings today to see if that can be resolved.

 

[ortaias]

To truly use Access as a multi-user database your database should be split.
The back-end should reside on a server type machine and the front-end
resides on each users machine. If you do not do this, then the database is
subject to corruption and total loss.

Access has a built-in database splitter that will walk you through the
process. However, before you do this, make a copy of the database in its
present form.

I have split the database. It is doing much better. I have been able
to get two users to access it. One of the forms contains a filtered
set of records, apparently all these records become locked and the
second user can not edit them. I will work on resolving this issue.
Thank you for pointing out that this approach was necessary. From
reading the documentation, I did not get the impression that this was a
necessary action. Thank you.

 

[ortaias]

The problem appears to be coming from the following: "Me.RecordLocks =
0". I had this code embedded in the intro-screen and it locked all the
records. Removing it allowed a second user to get to the intro-screen.

Would the ADODB recordset be a better way to go, in terms of managing
the "open" records for a multi-user environment?

 

[Tony Toews]

The problem appears to be coming from the following: "Me.RecordLocks =
0". I had this code embedded in the intro-screen and it locked all the
records. Removing it allowed a second user to get to the intro-screen.

Ah, very interesting. Thanks for the update.

Would the ADODB recordset be a better way to go, in terms of managing
the "open" records for a multi-user environment?

I doubt it very much as I can't see there being a difference between
ADO and DAO when it comes to record locking. Also many of your
record locks would be as the result of bound forms which have nothing
to do with ADO or DAO.

I would ensure though that when opening a recordset in VBA that you
open it as read only unless update is required.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[ortaias]

Thanks for the reply. In experimenting with opening access as a
multiuser database several oddities have occured.

One, I have a database of old projects that has a minimum of forms and
programing since the data is obsolete. It opens successfully as a
multiuser database, just as one would expect!!!! (This database is not
split).

Second, the database which is the subject of this post will not work as
a multisuser database unless it is SPLIT. When split it is working as
expected except for a minor glitch where the second person receives the
database locked message once, but after going to another screen and
then returning to the original screen can enter the form.

The reference book I am reading, which I do not have today so I can't
cite, discusses how records can be locked by "page" or by "record". As
I have experienced, "Me.recordlocks=0" unexpectedly locked all the
records in the table. I will have to experiment a bit more with my VBA
code to see if there is something that is causing a lock on the
recordset.

 

[Tony Toews]

Thanks for the reply. In experimenting with opening access as a
multiuser database several oddities have occured.

One, I have a database of old projects that has a minimum of forms and
programing since the data is obsolete. It opens successfully as a
multiuser database, just as one would expect!!!! (This database is not
split).

There are some things that make sharing an FE MDB than others. I
suspect filtering a form and/or using a Where clause on a called
forms. These and other things make Access think you've updated the
form and Access wants to save it.

Second, the database which is the subject of this post will not work as
a multisuser database unless it is SPLIT. When split it is working as
expected except for a minor glitch where the second person receives the
database locked message once, but after going to another screen and
then returning to the original screen can enter the form.

But you're stilling sharing the FE? What's the exact message?

The reference book I am reading, which I do not have today so I can't
cite, discusses how records can be locked by "page" or by "record".

Correct. But there are some interesting quirks in implementing
record locking. I don't quite recall the details though.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[ortaias]

But you're stilling sharing the FE? What's the exact message?

Each person has their own FE. The exact message "The table
'consistency' is already opened exclusively by another user, ot it is
already open through the user interface and cannot be manipulated
programmatically."

What is quirky is that if I open the second form (FORMB) first, that is
the message the second user gets when they attempt to access FORMB. If
I close FORMB, the second user can then open FORMB and so can I.
Addtitionally if the second user opens FORMB first, I do NOT get the
error message that the 'consistency' table is locked. (FORMA is a form
that simply displaces all the forms that the users can access, such as
FORMB.)

FORMB is opend by clicking on a comand button. The following code is
executed when FORMB opens.
-----------------------------------------------------------------------------------------------------------------
Forms![pending4frm].Filter = "[status] <10"
Forms![pending4frm].FilterOn = True
Forms![pending4frm].OrderBy = "[projectnum]"
Forms![pending4frm].AllowAdditions = False
Set Me.Recordset = Forms![pending4frm].Recordset
Rem Me.RecordLocks = 0 'This line seems to cause a problem. Entire
recordset activated not the filter.
-----------------------------------------------------------------------------------------------------------------------
You indicated that using a filter may have a potential for locking the
entire recordset. I have not had the time to check this theory out
yet. However, I had attempted a while back to create a recordset based
on a SQL statement to test this for this possiblity, but I was not
sucessfull in getting the recordset to work.

Correct. But there are some interesting quirks in implementing
record locking. I don't quite recall the details though.

The book I am using is "Programming Microsoft Access Version 2002" by
Rick Dobson. Working with Multiuser Databases is covered in Chapter 12,
page 661. Locking records programmatically is discussed begining page
667. I have more communing to do with this book to determine why I am
having this sporadic failure.

 

[Tony Toews]

Rem Me.RecordLocks = 0 'This line seems to cause a problem. Entire
recordset activated not the filter.

Yes, that line could be your problem. Someone else reported that
being a problem. Although it is Remmed so it shouldn't be executed.

You indicated that using a filter may have a potential for locking the
entire recordset.

No. My comment with Filters was with respect to sharing MDB/MDEs.

The book I am using is "Programming Microsoft Access Version 2002" by
Rick Dobson. Working with Multiuser Databases is covered in Chapter 12,
page 661. Locking records programmatically is discussed begining page
667. I have more communing to do with this book to determine why I am
having this sporadic failure.

Well, I haven't done any work with record locking as I haven't needed
to.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm