Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jhalla
Starting Member
2 Posts |
 Posted - 2006-09-26 : 11:48:01
|
| Hi, I've a bit of a problem that I'm stuck on.I have a system that has the tables on an SQL Server with the front-end on a Microsoft access database. They are connected via odbc and the tables are linked to the access front-end. The odbc connects to the sql server via a user created for the purpose that only has rights to this database. This user has rights set so that they can access/alter any data in the database.When my users try to alter data on some of the linked tables they get "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." This does not happen to every table only on some of them. This happens even when there is only one user accessing the system. If I log into the enterprise manager I can alter those tables/ records without any problem (as some articles I’ve read say if the problem is connected to a corrupt record its not possible to then alter that record and sql server should give me an error message instead).I have tired.- Compact and repairing the access front-end.- Deleting the links in the access front-end then re-linking them.- Copying the objects from the access to a new blank access db.- I have tried altering the account the odbc uses to login to the SA account.- I have asked the server admin to do the 'compact and repair' on the s.q.l server, shrink I believe it is, as some articles suggest this could solve the problem.- The admin has also tried this on the transaction files (so he has told me).None of this has worked.I'm stuck. I don't have any training on SQL server (nor will I be able to have any as I gather the training budget may be needed to fill some finance holes). If anyone has any idea of the answer to my problem or could point me in a direction that I could try investigating I would be very grateful. I have asked the admin if it’s anything to do with transactions, he has told me he does not know how to see those but he will try to find out if we can. Am I shooting in the dark or does this sound like a transaction/process locking problem? Is there something I should be looking for?Thanks in advance. |
|
|
nigeldavenport
Starting Member
12 Posts |
Posted - 2006-09-26 : 12:11:48
|
| I have had exactly this problem this week - the only way I have been able to get over this is as follows:On the tables that give the error message run a maketable query in access making sure you select the * option. Then create a delete query within access for the records held within the table on the sql server. Delete all the records. Create an append query on the table that you have just deleted the records from and append the records from the maketable query you made previously.Best to backup the database first to be safe. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Jhalla
Starting Member
2 Posts |
Posted - 2006-09-27 : 05:12:07
|
| nigeldavenport - Thanks I will look at trying that after I'm sure the back-up have worked. The people in charge of this server took my dev enviro where I was developing this and made that the live rather than leaving me my dev and creating a new live version. But why has this happened? will doing this stop this from happening again?X002548 - I can't make this an access data project as it interlinks with a number of system. It took me weeks to convince them that rather than having 4 or 5 access databases dotted around all having to be kept up to date with each other we should have the one central db but users needed a interface to manage the data so the access front-end. I do not bind any of my forms/ controls. Ever (always vba). Nor do I do select *. As this problem is happening even when I open the table data window instead of via forms/ controls I don't think that's an issue.- James |
|
|
|
|
|
|
Cannot update tables linked to access front-end