Access2000 won't open as a multisuer database

[Steve R.]

The Access2000 database is on a server located on our office network. 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 they 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/create/delete to the directory it appears "shared".
Nevertheless, is it possible that the Novell Network has a conflict
which prevents Access from being shared?
--------------------------------------------------------------------------------------
I took the database home (to my home network) 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.

 

[Steve R.]

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
----------------------------------------------------------------------------------------------------------------------------
1. It appears that this code needs to be placed whenever a form is opened which does not make sense. With this code multiple users can get to the main screen and can enter some of the other screens. Apparently subsequent forms do not inherit the record lock property which results in the error message that the database is "locked".

2. I do not understand why this code is providing a partial solution. In theory it should not be needed. But since it is needed, why is it working? Looks me as opening and connection and immediatly closing that connection.

3. I suspect that the cleanup code should actually be run when the form is closed. This would involve making the variables public.

4. Opening the form with "Me.recordlocks=0" did NOT seem to have an effect. I would have expected setting this property value on the form open event would have allowed multisuer access, but it didn't.

5. Any thoughts on what may be happening?

 

[WrHn85]

Hi,

I can't offer any help on the coding part, but there is also a weird situation (& posts on the web) where the "open" dialog box of Access automatically keeps opening the same way you last told it to. If someone opens the db by going into Access & clickin on the file they last used, and if they have ever clicked on the "open exclusive" even once, every time they go in that way it will keep opening exclusive (unless someone gets in before them and it can't).

 

[Banana]

Just checking- did you split the database?

 

[Steve R.]

NO. The database has not been split. The database is located on a shared folder. I am getting the impression that this is the "wrong" way to use ACCESS as a multiuser database. Which may explain my difficulty in getting it work as a multuser database. The "correct" way appears to require a "front" end and a "back" end, the back-end being on a database server.

In terms of creating an ADODB connection, as shown above, I have modifed it so that it apparently opens with the database and closes with the database. However, my verification code, to print the fields is failing, apparently I may have an incorrect seeting for "references" which I am going to check on today. However, assuming that I am incorrectly implementing ACCESS then this "improvement" is moot.

THE ATTACHED CODE WAS DELETED SINCE IT MAY BE FAULTY AND PROVED UNNECESSARY

 

[Banana]

You can't share database among users if it's not split. This is to help to manage record locking. Read up on how to split and front end and back end.

Until then, we'll look at the ado connection.

 

[Steve R.]

The Database has been split. I am able to get two users to access the database at the same time, but a few minor issues remain, which I need to think about. One of the first screens contains/displays a filtered set of records. All these records appear locked. When the first user opens this screen, the second user can not edit any of the records, essentially read only. In theory, only the record being edited should be locked. This is much better than before and gives me something to grind away at.

I have DELETED all the ADODB code stuff. From what I can tell this is unneccessary.

Question. Does each user need to have an Access "Front End" or can you have one "Front End" which is accessed by a short-cut?

Thank you for your help. I would never have uncovered the significance of this approach by myself.

 

[Banana]

Each user is supposed to have their own copy of Front End, and all copies of Front End should be linked to single Back End. If two users open same copy of Front End, of course the records will be locked.

Why did you delete the ADODB codes? Seems unrelated to me.

 

[KeithG]

Question. Does each user need to have an Access "Front End" or can you have one "Front End" which is accessed by a short-cut?

Each user should have a copy of the FE on there computer.

 

[Steve R.]

You guys are fast. I just finished that experiment and saw that it failed. So in retrospect, it was a dumb idea. But part of the learning process, just confirmed the obvious.

As for the ADODB code, I was trying a Plan B approach since Plan A was not working. My Database did not seem to accept "Me.recordlocks=0" so I was trying to use the ADODB equivalent "rst1.LockType = adLockOptimistic".

The only trouble with both Plan A and Plan B were that they were based on a faulty approach, by me, to implementing a multiuser setup. Hopefully, I am pointed in the correct direction now.

 

[Steve R.]

Over the past couple of days, I have experimented with why the database would not open as a multiuser database. I was getting the message that another user had "locked" the database.

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.

A second form is then used to view a subset of the database (pending projects, completed projects,etc.) When this code was added to the second form it again locked ALL the records and the second user was not able to get into the second form. I attempted to fix this by using Set Me.Recorset = Forms![edit4frm].Recrordset immediately after Me.RecordLocks = 0. But that did not work.

I have removed Me.RecordLocks = 0 from the second form. Access to the database is much better, but is erratic sometime. If I enter the second form first, the other user cannot access it. However, if they enter it first, I can still access it. If I close the second form, the other user does have access to it. Well it is getting better.

Based on the RecordCount property, the code Set Me.Recordset = Forms![edit4frm].Recordset does provide a subset of the entire recordset so I am wondering why the database is reporting that all records are locked?

I also experimented with the ADODB recordset. I was able to get a filtered recordset. However, I have not experimented with it in terms of other users accessing the databse. Would the ADODB recordset be a better way to go???

FYI: The database is two years old now as a single user database. The database tracks my projects. Now I am trying to make it accessible to others, especially our secretary since she opens and closes the files.

 

[Banana]

1) Did you split your database?

2) Recordlocking makes only sense if it is very likely that two users will be editing the same record at same time. If that is not what you are likely to encounter, then you can dispose of recordlocking.

 

[Steve R.]

Yes, the database has been split.
Chances of two people using the same record at the same time: Remote.
Would ADODB offer enhanced functionality?

 

[llkhoutx]

Two possible solutions:

1. Close the database, be sure all users are out, and then delete the database's locking file. It has a "ldb" extension.
2. You might have left a recordset definition open before closing the database. Identify it and close it. Be sure that your database closes all recordset definitions after using them.

 

[Maritza]

Why is this happening?

I had an Access 2000 DB on a shared folder, approximately 25 users, not split, the users had no problems sharing and accesing the db. At any particular time I could have four or five users inputting or updating data. Suddently we upgrade our computers to 2003 and we can only open the db once using the shortcut. Why?

Is this related to the problem you are experiencing?

Maritza

 

[Steve R.]

Reply to IILKHOUTX:
1. No problem with the "LDB" files. They are automatically deleted when the database closes.
2. Closing the dataset definitions is something that I have I have not fully thought out. The database is closed though Docmd.close. However, you raise an interesting question, that is that the recordset used by a particular form should be closed when the form itself is closed.

Reply to Maitza:
Depending on what you get as an error message, the problem we are experiencing could be the same. I was getting the error message that the database was locked by a user for exclusive use despite the fact that the database was supposed to open in a multiuser state. The problem appears to have resulted from Me.RecordLocks = 0. Deleting this line has allowed a second user to access the database. Still have some minor quirks to resolve. Based on this experience, multiuser access appears to require a bit of tweaking.

 

[llkhoutx]

"LDB" files is not deleted every time the datbase closes, i.e. when a recordsdet is not closed when it is no longer used.

 

[Steve R.]

Based on the feedback and experimenting, it appears that the code I have written has the effect of unintentionally locking the recordset in a manner that I have not yet been able to discern. (I have another database that has virtually no code and two people can access it at the same time.) At the moment the database is opening as a multiuser database except for a small quirk. If I open FORMB first - a second user can not gain access and that user is informed by Access that the underlying table has been opened for exclusive use or through the user interface and can not be manipulated programatically. But if I close the form the second user can gain access which means the recordset is now unlocked. Furthermore, when I reopen FORMB while the second user is in that form I have access which means that the recordset is still unlocked. Also if the second user is the first person to open FORMB, I can still open FORMB.

On the microsoft.public.access newsgroup I ran accross an article by Tom Wickerath titled "Implementing a Successful Multiuser Access/Jet Application". There are many links to followup on.

A quick read seems to imply that a multiuser database should be opened through an ADO connection. I had previously tried this but it failed, so I may give it another try.

The code I had:
Rem establish ADODB connection to initialize dataset
str1 = "Provider=microsoft.jet.oledb.4.0;" & "data source=P:\Federal Consistency\consistency.mdb"
Set cnn1 = New ADODB.Connection
Set rst1 = New ADODB.Recordset
cnn1.Open str1
rst1.CursorType = adOpenKeyset
rst1.CursorLocation = adUseClient
rst1.LockType = adLockOptimistic
rst1.Open "select * from [projectnumqry] where val([projectnum])= '" & Me![List7] & "' ", cnn1
Set Forms("edit4frm").Recordset = rst1
----------------------------------------------------------
The code above seemed to work, but my form opened with error messages, which I didn't have time to resolve since the form (edit4frm) works correctly as the database is currently configured. Yes, the database is split.