Can't share Back End - Opened Exclusively by...

[dgunner71]

All,

I have an application (developed in Access 2007) that I am just finishing. It developed as with the database split into front and back ends on a single machine with a single user. This works great - one user, same machine. I've turned on encryption for the backend (i.e. the back end has a password)

As I'm preparing to deploy I've installed the front end on another computer and linked to the back end.

This also works (painfully slow however - even with a gigabit switch - but this probably another post) when there is a single user logged in.

When there are 2+ users logged in (different front ends, same back end) I am getting an odd exclusive open error. It seems like the system is locking the table. If someone has the customer's table open and I try to open a customer record, I get the error. If they close there customer record so there is nothing open, then I can open the record just fine.

Also - this may help with troubleshooting - it appears that the .ldb file will appear when a user opens a record and disappear when the record is closed.

All record locks appear to be set correct (pic attached).

As always, any assistance is greatly appreciated.

Gunner

[imgnothumb]http://bytes.com/attachments/attachment/7340d1385749923/capture.png[/imgnothumb]

[dgunner71]

To clarify - the .ldb file that is appearing/disappearing is for the back end. The front end .ldb files are constant.

Thanks again.

Gunner

[zmbd]

First, sounds like you used an older mdb (acc2003) version as the lock file should be "*.laccdb" if you are using any older datatables this may cause you issues.

Second, we'll need to see how the recordset was created.
Is the form bound to the tables.

and yes the encryption can slow things down... the use of the older ACC2003 file format may be complictaing things here too.

The recordlevel locking should handle this... there may be something in the table relationships too so that if the form opens in such away as to allow potential for editing cross tables things can get locked.

Just a few different variables.

[dgunner71]

zmbd -

Sorry for misspeaking before - the locking files are indeed .laccdb files. (I'm so used to 2003)

As a general rule of thumb, the forms I'm using are bound to tables. I have some that are bound to queries but most are direct to the table. The Customer form that was presenting a problem earlier is bound directly to the [tblCustomers]. Do you recommend that they be bound to queries instead?

Also, to clarify your input regarding RecordLevel Locking, this should be set as shown, correct (Open db using record level locking)?

On an interesting note, when I was having the trouble today (my first trial run), the database was showing "No Record Locks" in the advanced properties. However, I made a copy of the database and it seems like it now opens itself with "Edited Record" checked. (I did not change it, but sure enough, that's how it opens). In any event, this seems to have corrected the problem.

I believe there is some minute downside to using this ("Edited Record"). I know that I had it set in an earlier database (2003) but turned it off to alleviate some problem. I just can't remember what that problem was. Can you offer any advice on this?

Thanks again.

Gunner

[zmbd]

Hmmm,
There is a performance hit for the lock edited record. With the weirdness of having it checked marked when you re-opened the file makes me wonder, once again, are you using tables and objects within an older database file?

As for table v. query:
I've usually used queries for my forms, it was the way I was shown by the SQL/Oracle programmers (they use views, same thing I believe) even if the query is a simple select that mimics the table. Only in my DBA front-ends do I ever have any form directly linked to a table and that is because I want that table locked. Ofcourse, the DBA front-end will not open the back-ends in exculsive unless everyone has closed their frontends (^-^)

Now, IF and ONLY IF I understand this correctly: By basing the form on the query, the form loads, queries, and is done with the table unless changes need to be made either thru edit or new record addition. The record is lockedupon write for an edit and submit for the new record. This is why often forms, or subforms, and controls must be re-freshed or re-queiried in order to get new information added either by the Local user or the other network users.

(I hope I have that right as I've been using that paradiem for many many many many years).

[dgunner71]

zmbd,

I checked and both the front and back ends are indeed MS Access 2007 files.

Also, thanks for the great info on tables v. queries - this makes perfect sense and will likely help to prevent (or alleviate) the issue - I'll get these all changed over.

I'll close this question for now - I'll see if I still have issues when I implement the new program.

My plan is to deploy this database next week with all users logging into the server of RDP with both the front and back ends on the same machine. I hope this will minimize the performance issues I was seeing when running the program with the back end on different computer than the front end. For the record, however - this should be doable, correct? (I've only deployed with all users on the server)

Thanks again,

gunner