SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Undeleatable record (on linked table)


Undeleatable record (on linked table)

Author
Message
a_ud
a_ud
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 336
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26002 Visits: 17528
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 Modens 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)
a_ud
a_ud
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 336
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!
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26002 Visits: 17528
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 Modens 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)
a_ud
a_ud
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 336
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26002 Visits: 17528
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search