Microsoft Access read-only back end intermittent

[jonnyboy]

Hello,

I'm having an intermittent problem with the back-end of my database.

On an intermittent basis, the back-end tables will become read-only. This only happens when the back-end is located on a network share.

It isn't a permissions issue because the problem occurs only intermittently when logged in as the same user.

If I open the back-end database on its own and then the front-end client (on the local drive) this problem goes away.

Once the back-end has been made 'read-only', I have to open it on its own and close it again to make the tables editable.

Any ideas?

Jon

[RuralGuy]

Have you tried using a Persistant Connection yet?Microsoft Access Performance FAQ

[jonnyboy]

I've solved it! I read up on using a persistent connection. Thanks RuralGuy!
I have the database open a hidden form linked to a dummy table in my database. This form is always open in the background, and it's the same as having the back-end database open at the same time.

I still have a minor problem if I close the database and then open it again quickly afterwards; this tends to make the back-end read-only. I guess this might be to do with record locks.

Any idea on how to stop this, perhaps by checking locks on the back-end and waiting if it is being closed at that moment? I wouldn't know how to do this...

[RuralGuy]

You can check for the .ldb file before opening the BE.

[jonnyboy]

Hello again,

The persistent connection seems to have improved performance somewhat, but I still occasionally have the problem with the back-end becoming read-only.

I'm not completely clear about what might be causing this. The only way to make the back-end writeable again is to open the back-end on its own.

Once this database is deployed, this isn't an acceptable solution.
Can anyone help?

[RuralGuy]

I do not understand how the BackEnd can switch to read only if you keep it open with a persistant connection.

[jonnyboy]

Hello again,

Thanks for replying again, RuralGuy.

You're right, it's a weird situation! Since I've implemented the persistent connection, it seems to happen only now when I first open the database (before I did this, it could happen at any moment).

I start up the client (which is located on the local hard disk) and at the bottom of every form it will say 'This recordset is not updateable'.

If I then start up the back-end on its own, the first time I do so it will tell me that the database has been opened as read-only. However, when I then close the back-end, it becomes writeable again and the client works fine (until the back-end spontaneously decides that it's read-only again!)

I'm really pulling my hair out on this one, so any further ideas are much appreciated!

[RuralGuy]

Are you checking for the absense ot the .ldb file before opening the BackEnd?

[jonnyboy]

Hello again,

I wouldn't expect the ldb file to be absent in all cases: the database might have two or three people accessing it simultaneously, which works fine. Another user having the database open doesn't turn the back-end read-only.

The back-end seems to go read-only spontaneously, although it tends to happen when the database is first opened.

[RuralGuy]

I didn't realize this was a multi-user environment. Does everyone have the same persistant connection FrontEnd?

[jonnyboy]

Hi,

Sorry for the late reply - the development has taken a different direction but I've got back to this line now!

Yes, all front-end deployments open a connection to a table in the back-end in a hidden form.

The problem is happening less frequently but it still occasionally rears its head and stops all users from editing the data.

[RuralGuy]

I wonder if you have a flakey NIC somewhere?

[jonnyboy]

Anyone else have any insights? The damn thing reared its ugly head again today!

[RuralGuy]

How are you determining that the BackEnd is read only? Does your system throw an error?

[jonnyboy]

Yes, the status bar at the bottom says 'This recordset is not updatable' and users cannot add new records or edit existing ones.