Solved

ms access locking record violation

Posted on 2013-11-15
11
999 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
11 Comments
 
LVL 44

Expert Comment

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

Author Comment

by:phiadmin
Comment Utility
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.
Comment Utility
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
 

Author Comment

by:phiadmin
Comment Utility
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 400 total points
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
>>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.
Comment Utility
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.
Comment Utility
phiadmin,

Can I ask why the B grade?
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
>>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.
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now