I am using an Access query to call up a member’s info. This query has been used for years to handle this person.
I need to total the sums of a few rows into one and delete the old row. When I have made my changes, I am told that I am trying to save changed information after someone else changed things and it drops my changes (now I have saved them).
I can paste them in, but any attempt to delete the other rows tells me “The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.”
There are no other users changing this data but me. Rebooting access or the OS does not help. My collegue can make the changes while I cannot, but it is my job so I can’t run to him every time. But it shows that it has to be my machine. It is windows XP, using an Oracle database. Access 2003 SP1.
This happens regularly and I need to get it fixed. I am new to mucking around with Access and am new to Oracle as well. I suspect my machine is making two connections to the DB, but do not know how to fix it.
Just a quick way of doing this without code, try the following:
First, close your database and look in the location where the Access Database is stored. If there is a file with an .ldb extension, delete it. Open the database and try your process again, as this might fix it.
If it doesn’t, try this:
Make a backup copy as this will alter the data in your original table and prior to running the process, ensure the original data table is closed.
Change your query to a Make Table Query
Store your data in the new table with the new field names
Now that you have your calculated data in your new table create a delete query
Usethe criteria that you used to select your data in the make table query
Once you have run the delete query, create an append query and append the data to the original table
Make Table –>> Run Delete Query –>> Run Append Query
The newly created table will be overwritten each time you run this process
Let me know if you have any questions. If this fixes your issue, I can provide you with the information to automate these steps.
Thanks Chris,
is the .ldb file recreated each time? Other people do use this DB to run queries… just I am the only one at this time.
This is a state DB so I am cautious being that the venue and position are new to me.
What I did find after writing this yesterday is that I cannot make changes with either my account or my fellow workers on my machine, but he can from his. So it would seem to be machine specific.
I have made notes and will have to rerun the process next week so will see if it helps then.
Yes, the .ldb is created whenever you open an Access Database on whatever machine you are on, it stands for locked databse. If Access doesn’t close cleanly, the .ldb might still exist, which could potentially cause you problems next time you open Access.
If you are opening the database and running this query while someone else is using it and not using a form, you might have problems updating a table.
Three possibilities to ensure the table is not locked:
Ensure no one is in the database prior to your next test
Split your database, and have a front end database with a graphical interface that users can interact with.
Create a button on a form and run the process by clicking the button.
If you need to have control over users exiting, you could include a time event and add a table. In the table, you could have a true/false datatype and have the database check if the value of the field is set to true/false whichever value you prefer, and once the value meets the critearia for database shutdown, add a message box to let the users know the database will shut down in x minutes. The database then shuts down which should be cleanly this way.
To ensure you are the only accessing the database add some code like the following:
Private Sub cmdGetUser_Click()
Dim myLogon As String
myLogon = GetLogonName()
MsgBox myLogon
If myLogon <> "loginName" Then
MsgBox "I don't know you. Application will now close."
DoCmd.Quit
Else
MsgBox "Welcome Username."
End If
End Sub
Once you have done what you need to do, change the database field in the table so the time does not cause shut down, and then everyone can start working on the database again.