DB Sharing Issue: Access 2010 (1 Viewer)

[QTNM]

I am wondering if I could get some input on this issue.

I originally created a simple access database that tracks the flow of documents in Access 2003. This database is located on a network share and 6 users access the database (via shortcut to their desktop) intermittently. One of the users accesses the database every day as she enters the bulk of the documents received, whereas the other users open the database for search functionality and the occasional entry.

We recently upgraded our PC's to Office 2010. Since this upgrade we are now experiencing an issue with users not being able to open the database at the same time. This was not an issue before the upgrade.

Something I do suspect is, upon opening of the database, an entry form is opened and on the Form_Open action "DoCmd.GoToRecord , , acNewRec" command is set. This is to make it easy for the individual entering, they can go ahead and start entering right away.

Now, I understand that if two simultaneous sessions of the database is opened, it's confusing to which new record would be opened.

In Options >> Client Settings >> Advanced - Default Open mode = Shared and Default Record locking = No Locks.

When the second user tries to open the database, an error message appears: Runtime error '2046' The Command or action 'GoToRecord' isn't available now.

I'm curious as to why we didn't have this issue with the previous Access 2003 version?

Can anyone think of a solution, or a workaround? I still want to maintain that the form opens to a new record upon opening of the database.

Thanks - your suggestions are much appreciated.

QT

 

[Banana]

I would much strongly recommend that you do not open database on a network share. Instead, split the database and give users their copies of the front-end. This is considered a best practice since Access' inception. I don't know why it's now an issue with 2010 when it wasn't with 2003, but personally I'm glad it's now issue because it's far more risky to use the database like that.

When it's split, it'll be much more stable and you won't have problems with users sharing same data.

 

[QTNM]

Is it a bad practice to have the DB open to a form that is set to the new record? I thought this may have been the issue. When multiple users try to open the database it's trying to access the same *new* record?

As for splitting the database, you are absolutely right. [My bad] I did some extensive reading on this issue and this was a recurring mention. When I first created the database there were quite a few changes being made to the forms/reports, etc.

I will proceed with splitting the database, but wanted some input on whether I should change the way the form opens - is it ok to open to a new record. I was hoping to make it easier for the data entry folks.

Your suggestions are much appreciated,

QT

 

[QTNM]

Indeed, I split the database and copied the front end to each of the user PC's and so far I hear no grunting .... excellent.

Banana - thanks for chiming in!

QT