Solved

ms access locking record violation

Posted on 2013-11-15
11
1,055 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
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create tables in access db (2016)  using vba 13 42
sql statement - 9 25
Access Crosstab Query with Multiple Values 4 32
Update a text value in another table 10 40
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…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

778 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