April 5, 2013 at 10:30 am
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.
April 5, 2013 at 10:53 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 8, 2013 at 7:02 am
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!
April 9, 2013 at 7:46 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2013 at 9:37 am
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.
April 11, 2013 at 11:48 am
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 | ScoreA1 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply