?
Solved

ms access locking record violation

Posted on 2013-11-15
11
Medium Priority
?
1,325 Views
Last Modified: 2013-11-20
A new test Oracle database was created by supposely duplicating an existing production database.  In this new database, when I try to delete records using queries, I get an error msg that it can not delete due to lock violations.  All these queries work in the production database.  I did go in and delete the tables and relink creating different keys.  Then some records will get deleted but not all.  Also it crashed the ms application and reboots itself.
It seems to be able to do all the other queries ok.
0
Comment
Question by:phiadmin
  • 5
  • 3
  • 2
10 Comments
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 39653295
Is the question about an Oracle (not microsoft product) or Access (microsoft product) database  ?
0
 

Author Comment

by:phiadmin
ID: 39656963
This question is about an Access mdb deleting a link Oracle table.
In the production system it works.  This is a new Oracle database
and I am trying to get info from the Oracle dba if there is anything different
about this database.  Using sqlplus, I am able to delete the from the table.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39657291
This question is about an Access mdb deleting a link Oracle table.

Do you mean deleting data from a linked Oracle table?

In the distant past I, as a new Oracle/SQL DBA, would create a blank database structure and then use wizards and such to import the tables into the blank database. What I failed to realize is that while the data and tables were there, the wizard didn't also import all the indexes, constraints, and other extended setup information of the tables.

You may want to consult with the Oracle DBA if he did a restore, or just import the data.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:phiadmin
ID: 39660819
1. Deleted the link between MS Access and the Oracle database
2. Purge all the records using sqlplus.
3.  Relink the tables and creating the appropriate indexes.

This seems to fix the problem but will still be testing
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 1200 total points
ID: 39661189
A well designed SQL or Oracle DB shouldn't need to have you create Access indexes to be writeable. The exception is when there is a bigint autonumber/identity column. The largest integer (long data type) is 2,147,483,647 in Access 2010 and below. That is the max of the SQL integer data type. That will cause an issue.

So you need to talk to whoever created the Oracle DB and ensure that all tables have a primary key. That will allow you to just link to the tables without creating local indexes.

The reason that this is important is that if you have to  relink the tables in the future, that you don't have to "babysit" it. I have actually created routines that list off tables that I want to write to via VBA.
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 39661561
>>Relink the tables and creating the appropriate indexes.

The access database part should have nothing to do with that - unless you are storing data in both the access and the oracle databases
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39663630
The access database part should have nothing to do with that - unless you are storing data in both the access and the oracle databases

For Access to do any update, insert, or delete operation on the foreign table, the linked table has to have a "primary" key. If the primary key doesn't exist when Access is linking to it, their will be a popup box that asks you what fields you want as the primary key. That will make the table editable.

A good way to see it happen is set up a SQL Express instance and grab the sample database. Then create a table using the SELECT * INTO [TestTableName] FROM <whatever>. Then bring up Access and link to the original table and the table you created. That will show you the issue.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39663633
phiadmin,

Can I ask why the B grade?
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 39663830
>>For Access to do any update, insert, or delete operation on the foreign table, the linked table has to have a "primary" key.

Is that Oracle specific ?  I ask because I can insert/append/delete to linked tables without PK's when the background DB is another provider.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39663947
I know it affects both Oracle and SQL Server using Access. There are some DB that I have worked with in the that I also have the issue. There are some it may not be an issue with. But I'd gamble on saying that the combination of ODBC drivers and DB types have some sort of hidden key that you are not aware of that resolves the issue.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question