Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ms access locking record violation

Posted on 2013-11-15
11
Medium Priority
?
1,260 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

916 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