Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Undeleatable record (on linked table) Expand / Collapse
Author
Message
Posted Friday, April 5, 2013 10:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279
Hi,

I've got a system made up of a SQL 2005 backend and an Access front-end. The front-end only holds the linked tables (plus the code).

Whenever I try to delete a record IN Access (directly in the table itself), Access throws an error, so I have to go to the back-end and delete it manually. I guess the problem is with the definition of the table in SQL, but I can't find what's wrong with it. Some other tables in the back-end look similar but you can delete records normally.


CREATE script attached:
/****** Object: Table [dbo].[tblPAScores] Script Date: 04/05/2013 17:22:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblPAScores](
[VolunteerID] [varchar](50) NOT NULL,
[NoWeek] [int] NULL,
[PA] [numeric](5, 2) NULL,
[Class] [varchar](50) NULL,
[NoPeople] [int] NULL,
[Entered] [datetime] NULL,
[Notes] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF


Any advice on other improvements very welcome. Thanks in advance, a.
Post #1439353
Posted Friday, April 5, 2013 10:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 13,064, Visits: 11,897
Do you have primary key on the table? How did you setup in Access which field(s) make up the primary key?

Also, you should not use the text datatype. It is deprecated. You should instead use (n)varchar(max). Of course if you don't more than 8,000 characters you would be better off limiting the size.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1439368
Posted Monday, April 8, 2013 7:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279
Thanks Sean, I've modified the data type from text to a limited varchar + added an ID column. This last amendment now allows me to delete records, which was exactly what I was looking for.

I still don't get why SSIS doesn't allow deleting on tables with no primary key (when they're linked on Access), it's sth you do all the time in Access and other DDBBs without any hassle.

Thanks!
Post #1439805
Posted Tuesday, April 9, 2013 7:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 13,064, Visits: 11,897
a_ud (4/8/2013)
Thanks Sean, I've modified the data type from text to a limited varchar + added an ID column. This last amendment now allows me to delete records, which was exactly what I was looking for.

I still don't get why SSIS doesn't allow deleting on tables with no primary key (when they're linked on Access), it's sth you do all the time in Access and other DDBBs without any hassle.

Thanks!


Glad that worked for you. Without a primary key there is no way to identify any given row. This is called a heap. I suspect that you had something like a grid that was bound to your table? There is no way ODBC can delete from this because it doesn't have a way of knowing what row is what (primary key). You could still run a delete statement and it would work but through databinding it will not work. This isn't a limitation of Access, the same is true in any RDBMS. The best practice in pretty much any case is to have a primary key on your table.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1440326
Posted Thursday, April 11, 2013 9:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 26, 2014 2:21 AM
Points: 68, Visits: 279
The odd thing (from an Access point of view) is that the row, to me, is identified by the condition.

Essentially what I was doing was used some VBA on Access to run this SQL on one of the tables:

"DELETE from tblScores WHERE VolunteerID='A1' AND NoWeek=3"

on a table tblScores having a structure like this:

VolunteerID | NoWeek   |   Score

A1 1 3
A1 2 5
A1 3 0
A2 1 11
........... etc

so the condition "WHERE VolunteerID='A1' AND NoWeek=3" only identifies one record in Access (but, oddly enough, SSIS rejects this). That's odd to me, but after adding the ID column it worked perfectly.
Post #1441337
Posted Thursday, April 11, 2013 11:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 13,064, Visits: 11,897
a_ud (4/11/2013)
The odd thing (from an Access point of view) is that the row, to me, is identified by the condition.

Essentially what I was doing was used some VBA on Access to run this SQL on one of the tables:

"DELETE from tblScores WHERE VolunteerID='A1' AND NoWeek=3"

on a table tblScores having a structure like this:

VolunteerID | NoWeek   |   Score

A1 1 3
A1 2 5
A1 3 0
A2 1 11
........... etc

so the condition "WHERE VolunteerID='A1' AND NoWeek=3" only identifies one record in Access (but, oddly enough, SSIS rejects this). That's odd to me, but after adding the ID column it worked perfectly.


Access has now way of knowing there is only 1 row "WHERE VolunteerID='A1' AND NoWeek=3" because there is no primary key. If you want to update or delete that row how can the engine know which row it is? It can't use your where clause because that can return more than 1 row and you obviously wouldn't want it to make those changes to all rows that meet the condition. You want that to happen ONLY to the specific row and since there is nothing to uniquely identify the row (primary key or unique index) it is impossible. If the combination of VolunteerID and NoWeek makes a unique combination you could make that the primary key.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1441398
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse