Could Default Dates Cause Record Locking?

[mlrmlr]

Hi,

I am having major problems with record locking. I read on other posts that dates could be causing this problem. I currently have my date as a default value "Date()". Could this be the problem causing the following error messages?

"You can't go to the specified record". and
"No current record"

Appreciate any help on my dilemma.

 

[Ed2020]

If default date values can cause can cause this issue I've never heard of it before.

Can you provide a little more info? What version of MS Access are you using? Number of users? Is the error occurring in VB code or behind bound forms?

Ed Metcalfe.

Please do not feed the trolls.....

 

[mlrmlr]

Hi Ed,

I am using MS Access 2002. I have a multi-user (approx. 10-15 users) database that resides on a server. The database has been working fine for over a year. About two weeks ago, my users have been receiving the error message
"You can't go to the specified record" and "No current record". I am repairing the database several times a day. I believe multiple users are attempting to write to the same record but I don't know how to check for this.

The errors occur on a bound input form with an "Add Record" button that I created using the wizard.

I am so frustrated and have read multiple posts on the record locking issue. Some of these referenced the default date. I am not experienced enough to explain the reason but it had to do with date.

 

[Ed2020]

mlrmlr,

I very rarely use bound forms, so this could be why I have never come across this problem with default date values causing record locking problems. My gut feeling is that this isn't the problem though.

Have you made any changes to the database between it working correctly and the problems occurring?

A couple of things I'd check/try:

1. Check the Default Record Locking settings under Tools>Options>Advanced. These are normally set to "No locks" and "Open databases using record-level locking" ticked. If I remember correctly Access will lock a page of records if record-level locking is not selected. The number of records this covers depends on the size of each record in the table.

2. If no changes have been made to the database immediately prior to the problem occurring, and the record locking options are correct, I'd suspect an MDB file corruption. Compact & repair doesn't always solve these. Importing all objects into a new MDB file often does the trick.

3. On the off chance that the default value of the field is causing the error try making the default value of the form control = Date() and remove the default value of the field in the table.

4. Is the problem experienced by all users or just some of them? If only some of them it may not be a problem with your database. Perhaps some users have differences in their desktop account profile? Permissions differences?

If none of this solves the problem I'd do one of the following:

1. Replace the bound form with an unbound form. Unbound forms are more work to create but, in my opinion (and I'm sure plenty would disagree with me), are worth the extra effort. I prefer to write my own record locking and data validation routines and then save the data in code.

2. If you don't fancy doing that I would split the database into a frontend and a backend. Put the backend on the server, the frontend on the user's local drive (assuming they have one). Have the bound form saving its records to a temporary table in the frontend first, and then appending the record to the master table. If the append fails you could then specify a certain number of retries before the user is given an error message.

Out of curiosity how big is the table you are saving data to? How many fields? How many records?

Ed Metcalfe.


Please do not feed the trolls.....

 

[mlrmlr]

Ed,

Our workload have increased causing much entries and possibly at the same time causing the record locking.

I originally added an addition checkbox field which I changed to a combobox. This is the only change.

I checked the default record locking and it is set to "Edited Records" and "Open databases using record-level locking". I did not set the Edited Records so I don't know how that was set. I have the following set:

OLE/DDE timeout (sec): 30
Refresh interval (sec): 10
Number of update retires: 2
ODBC refresh interval (sec): 1500
Update retry interval (msec): 250

I have performed the compact and repair numerous times. I created another clean database and imported all of the files over.

I check with our server administrator and verified that there were no permission changes. The record locking issue is occurring with numerous users.

A couple of days ago I split the database and thought that would do it but I just received a call from a user indicating that she received the same "You can't go to the specified record" message. :-(

Unfortunately, I am not that experienced in developing code to write routines. Is it simple to perform your last suggestion, "Have the bound form saving its records to a temporary table in the front end first, and then appending the record to the master table. If the append fails you could then specify a certain number of retries before the user is given an error message."

We currently have 35,000 records in one table. The table has 25 columns.

 

[ProgramError]

In access 2003 theres also an option to open in exclusive mode. Not sure if its in 2002.

Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[mlrmlr]

Hi Ian,

No, it is definately opened in "Shared" mode.

Thanks.