UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Delete Record - SQL Linked Table Error, Access 2013    
 
   
Peekay
post Jan 12 2019, 11:33 PM
Post#1



Posts: 207
Joined: 5-October 10



I have a "delete" command in a procedure. It has always worked fine. This week, I moved all tables to SQL server. Now linked to them. However, when I run the command, I receive an error:

Error 3086
"Could Not Delete from Specified Tables"

Do tables in SQL require certain indexing or Primary keys? What is causing this error?
Go to the top of the page
 
jleach
post Jan 13 2019, 08:24 AM
Post#2


UtterAccess Editor
Posts: 9,991
Joined: 7-December 09
From: St Augustine, FL


Hi - can we see code of the procedure you're running?

--------------------
Go to the top of the page
 
GroverParkGeorge
post Jan 13 2019, 08:24 AM
Post#3


UA Admin
Posts: 35,013
Joined: 20-June 02
From: Newcastle, WA


The most common reason for this problem is that, when you linked to the SQL Server table, you did not indicate which field is the primary key for that table. I do hope your tables all include Primary Keys. This question seems to indicate maybe you didn't? "Do tables in SQL require certain indexing or Primary keys?" Yes, yes all tables need primary keys, regardless of the RDBMS (with a few unique exceptions, I suppose). But then, you would have to designate a "primary key" for a linked Access table anyway to make it updateable.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Peekay
post Jan 13 2019, 10:42 AM
Post#4



Posts: 207
Joined: 5-October 10



OK, that is definitely causing the error. Om changed the sequence, sure enough the procedure stops at the table without a primary key. This is a sibling table. It is the "many" in the join. The primary key does repeat. Thus, it won't let me assign a primary key designation to it. Or will it? I tried making the index = Duplicates OK. It won't allow that to a field with primary key designation, thus won't let me save the change.

The Parent table has a primary key called "KeyCode". It joins to the sibling table (the many) to a value called KeyCode. How do I get a primary key value or designation in the sibling table?
Go to the top of the page
 
GroverParkGeorge
post Jan 13 2019, 12:06 PM
Post#5


UA Admin
Posts: 35,013
Joined: 20-June 02
From: Newcastle, WA


You'll need to define the Primary Key in SQL Server, not in Access. Is that how you are doing it?

If you are getting the error message regarding a duplicate, chances are that the culprit is a Null. Access allows them where SQL Server doesn't. However, the only way to be sure what the issue is would be to evaluate and correct the problem in SQL Server tables using SSMS.

Based on your description is sounds like you either need to identify the fields you want to designate as the PK in the SQL Server table and add it there, or you can add a surrogate, using the Identify property, to use as the PK.
This post has been edited by GroverParkGeorge: Jan 13 2019, 12:08 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
AlbertKallal
post Jan 13 2019, 01:59 PM
Post#6


UtterAccess VIP
Posts: 2,811
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
The Parent table has a primary key called "KeyCode". It joins to the sibling table (the many) to a value called KeyCode. How do I get a primary key value or designation in the sibling table?


Actually, just to be clear – you do not need to get nor grab the PK in that table, but ONLY be sure it has a PK!

If you just add a autonuber PK (primary key) to that table then all should be fine.

Your existing delete code/SQL will THEN work just fine.

All you need to do is add a PK column to that table. It can be a new autonumber ID if you wish. (or as suggested, when linking, access will prompt you for a PK (even if it does not have one).

I would just add a column (say the 20 year default) of ID, and set it as a primary key. This assumes you can modify the SQL server tables. You also have to set the column as “identity” as that gives it the atomatic increasing/incrementing number for each row just like a access autonumbre column.

Once you add the PK (using the SQL manager), then you have to re-link the one table. (right click on the table in access, choose linked table manager. Select the one table, and hit ok to re-fresh the one link.

At this point your existing code should work just fine without any change.

Access is able to work rather fine without a PK (access back end) but linked tables to SQL server for the most part do require a PK – EVEN if you not using it, or your code does not care and never referenced the PK.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
Minty
post Jan 14 2019, 04:29 AM
Post#7



Posts: 299
Joined: 5-July 16
From: UK - Wiltshire


Just to add a little flesh to the advice, there is a reason that you must have primary key, and that is for the ODBC driver to keep tabs on which actual record it is dealing with.
Access locally can handle this, but SQL and other RDMS can't. Without the unique identifier, you can view and query the data, but not modify it.

When you link a table like yours with a FK , you can sometimes create a "surrogate" PK key by using the FK and a datetime stamp, assuming multiple sub records aren't added at the same time.
Go to the top of the page
 
jleach
post Jan 14 2019, 06:52 AM
Post#8


UtterAccess Editor
Posts: 9,991
Joined: 7-December 09
From: St Augustine, FL


Taking Minty's advice a step further, it's considered good practice to include also a ROWVERSION (previously known as a timestamp) field in each of your SQL Server tables.

This is a special column that gets a binary stamp for each change in the data within that row, which Access also uses to know whether it has the most up-to-date version of the record. I've seen many bugs surface in Access due to this field not being included.

So, the Primary Key uniquely identifies a single record among many, and the ROWVERSION field uniquely identifies the version of that record: both of which are needed in most operations when Access is your consuming application.

hth

--------------------
Go to the top of the page
 
Peekay
post Jan 15 2019, 08:43 AM
Post#9



Posts: 207
Joined: 5-October 10



This is outstanding feedback ! Thank you.
I've consulted with our SQL tech. He agrees with you.

Not, I'm creating the shadow PK field that will serve no purpose in any report or query. It will exist in the sibling table to satisfy SQL Server requirement for PK.

Question: He was unsure is SQL Server could handle "AutoNumber" data type.

Do you know if it can ?
Go to the top of the page
 
GroverParkGeorge
post Jan 15 2019, 09:25 AM
Post#10


UA Admin
Posts: 35,013
Joined: 20-June 02
From: Newcastle, WA


Yes, but it is known by a different name and is defined differently from the Access AutoNumber.

SQL Server has a datatype called "int" or integer. You can define the surrogate Primary Key as an int, and then assign to it the Identity property. Set the increment as 1 and the starting value as 1. Add the Primary Key Constraint to it.

SQL
CREATE TABLE [dbo].[tblCustomer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [nvarchar](50) NULL,
CONSTRAINT [PK_CustomerID] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Peekay
post Jan 15 2019, 10:26 AM
Post#11



Posts: 207
Joined: 5-October 10



Is there an easier way to go about this?
Go to the top of the page
 
Minty
post Jan 15 2019, 10:31 AM
Post#12



Posts: 299
Joined: 5-July 16
From: UK - Wiltshire


You can use the table designer in SQL Server Manager if the script is worrying you.

The net effect is identical, you are adding a "Autonumber" or as SQL prefers to call it an Identity Column, that will become your PK for that table.
Attached File  SqlIdentity.PNG ( 16.38K )Number of downloads: 0
Go to the top of the page
 
GroverParkGeorge
post Jan 15 2019, 01:05 PM
Post#13


UA Admin
Posts: 35,013
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, the power of SQL Server does come with greater demands. As Minty points out you can use the UI if that's more comfortable to you.

However, I was thinking your "SQL tech" would be called on to do this. Is that not in the tech's area of expertise?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Peekay
post Jan 16 2019, 07:42 AM
Post#14



Posts: 207
Joined: 5-October 10



Thank you for all the help. I have it working. Doing precisely what I need. I'm successfully adding and editing rows with your recommended changes

Only change I had to make is in every recordset option declaration I had to include "dbSeeChanges"

I'll be putting this production testing later today.

Thanks to all with this solution!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th May 2019 - 06:09 AM