Solved

ms access locking record violation

Posted on 2013-11-15
11
1,127 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
11 Comments
 
LVL 44

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 400 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 44

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 44

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

739 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