Question about Database Splitting of Access 2010

[databasepnt]

Hi all,

Hope I can find an answer here. My database has successfully split. However, the FE acts like not being split - Only one person can access the database at a time. For example, I am accessing a record - ID 01 by using a form. And as soon as another user is about to use the same form. the record I am accessing appears on the form of the FE of the other user.

Any one has any idea why?

Much appreciate.

 

[Catalina]

Hi all,

Hope I can find an answer here. My database has successfully been split. However, the FE acts like not being split - Only one person can access the database at a time. For example, I am accessing a record - ID 01 by using a form. And as soon as another user is about to use the same form by clicking in the ID field to access another record, the data of the record - ID 01 - that I am accessing appears on the form of the other user.

Any one has any idea why?

Much appreciate.

Do the users each have their own front-end?

Thanks for your question!

Yes. I used the FE of the database after splitting to email to each user. I also tried to put the FE file on a network drive so that each user can copy and paste to each own computer to use. It didn't work either.

Do the users each have their own front-end?

Thanks for your question!

Yes. I used the FE of the database after splitting to email to each user. I also tried to put the FE file on a network drive so that each user can copy and paste to each own computer to use. It didn't work either.

 

[Pat Hartman]

When you open an Access database, Access creates what it calls a "lock" file. It has the same name as the database but with a different extension. For ACE, the extension is .laccdb and for Jet it is .ldb. This file is used to manage concurrent users. If this file cannot be created, Access opens the file in exclusive mode which locks out all other users.

To solve the problem, talk to your network administrator. Each user of the application needs CRUD (Create, Read, Update, Delete) permission for the folder holding the Access database. Usually this is only a problem with the BE since users normally have CRUD permission for directories on their C: drives.

PS - I merged in a duplicate post. Please don't start new posts on the same topic. It waste our time since you may already have an answer on the other thread.

 

[databasepnt]

When you open an Access database, Access creates what it calls a "lock" file. It has the same name as the database but with a different extension. For ACE, the extension is .laccdb and for Jet it is .ldb. This file is used to manage concurrent users. If this file cannot be created, Access opens the file in exclusive mode which locks out all other users.

To solve the problem, talk to your network administrator. Each user of the application needs CRUD (Create, Read, Update, Delete) permission for the folder holding the Access database. Usually this is only a problem with the BE since users normally have CRUD permission for directories on their C: drives.

PS - I merged in a duplicate post. Please don't start new posts on the same topic. It waste our time since you may already have an answer on the other thread.

Hi Pat,

Sorry about that!

Thanks for your answer! I rechecked and found that the .ldb was also created. I also checked the permission and found that all users have all of the rights: full control, modify, read & execute, read, and write; except "Special permissions". Not sure if the things I found are related to what you were mentioning?

Thanks much!

 

[way2bord]

Hi all,

Hope I can find an answer here. My database has successfully split. However, the FE acts like not being split - Only one person can access the database at a time. For example, I am accessing a record - ID 01 by using a form. And as soon as another user is about to use the same form. the record I am accessing appears on the form of the FE of the other user.

Any one has any idea why?

Much appreciate.

This sounds like normal behavior to me?
Anyone who opens the FE form can VIEW the same record. It SHOULD appear on the form of the other user.

Could you clarify the following:

Only one person can access the database at a time.

...as soon as another user is about to use the same form. the record I am accessing appears on the form of the FE of the other user.

These two statements seem incompatible...
Another user cannot use the same form - Every FE has its own unique set of forms.
Another user is supposed to be able to access the record you're using - this is how multiple users access a database at a time...

 

[databasepnt]

This sounds like normal behavior to me?
Anyone who opens the FE form can VIEW the same record. It SHOULD appear on the form of the other user.

Could you clarify the following:
These two statements seem incompatible...
Another user cannot use the same form - Every FE has its own unique set of forms.
Another user is supposed to be able to access the record you're using - this is how multiple users access a database at a time...

Hi Pat,

What happened was that Let say I and another user (2 users) open the two different FE files. I pull the information of record ID - 01. I haven't had a chance to update or done anything with this record yet. The information of the same record ID - 01 on my form is also automatically shown on the form of the other user even that user hasn't entered any record ID to pull the information. Hope this clarifies the situation.

Thanks much!

 

[Pat Hartman]

Even when two users open the same physical .mdb, this doesn't happen since the form opens in memory on each users workstation so they are not tied in any way.

Do you have code running in the form that is causing this? Perhaps a file that saves information regarding what a user was last working on so forms will open to that record?

 

[databasepnt]

Even when two users open the same physical .mdb, this doesn't happen since the form opens in memory on each users workstation so they are not tied in any way.

Do you have code running in the form that is causing this? Perhaps a file that saves information regarding what a user was last working on so forms will open to that record?

Hi Pat,

Thanks much for your question!
I am not sure if there is any. But my FE file has a few simple forms. One main form is to pull a record by record ID then update or clear any data in the form. However I have one table so called 'holding record'. This table will hold the data of the record ID by whichever user has entered in the form. And then the data of this table will be displayed in the form. I think this can be the reason why the same record's information is shown up on the forms of other users? Just to make sure you know the situation, all of the users use their own FE file on their own computers.

Thanks much!!!

 

[saseymour]

Not sure if I should start a new post for this, but I am having a similar issue.

I've split my database into a FE and BE.

The BE is on a shared drive where I have given everyone RWUD privileges.

I've sent copies of the FE database to people via email.

Now, whenever ANYONE opens the FE form, the record locking file shows up for the BE database.

I am using Access 2010, and my FE is a simple navigation form, with 5 tabs / forms that make it up.

I thought the point of splitting the database was to allow concurrent users???

Help please!

 

[databasepnt]

Not sure if I should start a new post for this, but I am having a similar issue.

I've split my database into a FE and BE.

The BE is on a shared drive where I have given everyone RWUD privileges.

I've sent copies of the FE database to people via email.

Now, whenever ANYONE opens the FE form, the record locking file shows up for the BE database.

I am using Access 2010, and my FE is a simple navigation form, with 5 tabs / forms that make it up.

I thought the point of splitting the database was to allow concurrent users???

Help please!

Hi saseymour,

I think I found the answer.
Basically it happened to me because my macro codes of displaying a record by pulling a so called "holding table" which should be owned separately of a FE file instead of being shared.
To fix this problem:
1/ I exported that "hold" table as Excel (Do not change the name when save).
2/ Delete the "hold" table and save the database.
3/ Split the database.
4/ If the split is successfully, I email other users the FE file and the exported table as Excel format.
5/ Other users save the FE file on their computers and open it - depends on the macro, it may give users a warning because of missing the deleted table. Just click OK to ignore it.
6/ Import the exported table from the Excel file - maybe you may need to select "No Primary Key" for this table - depends on your original table. Basically after importing the table should be the same as the original in terms of properties.
7/ After importing, make sure all of the data types of each field must be matched with the original or get error or the macro or the query won't work.

Good luck!!!

 

[Pat Hartman]

Now, whenever ANYONE opens the FE form, the record locking file shows up for the BE database.

That is correct. The lock file is used by Access to manage concurrent users. The first user to open the file causes the lock file to be created. Subsequent users are logged in and the last user to close the database causes Access to delete the lock file. Are you have ing a problem?

 

[Teammom2]

I have a split database with the backend stored in Skydrive. Each user has their own front end and all the records locks are set to edited record on both the back end and on the forms themselves. When multiple users add a record, a duplicate backend is created so that data from each user is going into a separate table. What am I missing???

 

[Pat Hartman]

Skydrive (or similar) cannot be used to store a shared BE. It doesn't support the concurrent file sharing that Access needs.

If your users are not on the same LAN, you will need to use Citrix to allow them to share a database. You can also try converting the BE to SQL Server and hosting the BE on an Azure or SQL Server in the cloud. This method will probably require application changes unless your app is already client/server compliant.

 

[Teammom2]

Thanks Pat-That is kind of what I figured. I kept coming back to the fact that Skydrive is not really a network. After posting my question, I did some more research and found someone had asked a similar question and the reply was the same as yours. But I still had hope, all my testing seemed to work last week but I was having a different issue, I had to keep relinking my front end to my back end. But I think both issues stem from the fact that it just cannot be done on Skydrive or similar.