April 19, 2013 at 12:45 pm
Help much needed here!
I've got an Access DB as front-end and a SQL back-end. When trying to delete-modify a record in Access (table just linked), I get the usual error 'The record as been changed by...' > Drop changes. I take the error is on the table, not on the Access form.
The table that populates the form is also non-updateable in Access, but I can't find WHY is not updateable. If anyone sees some clue pls let me know, even if it's not about the updateability.
Table script attached:
CREATE TABLE [dbo].[tblVolunteer](
[VolunteerID] [varchar](20) NOT NULL,
[PartID] [int] NOT NULL,
[IDChecksum] [varbinary](20) NULL,
[Barcode] [nvarchar](50) NULL,
[SiteID] [varchar](10) NULL,
[Class] [varchar](50) NULL,
[Year] [varchar](10) NULL,
[Title] [varchar](10) NULL,
[Initials] [varchar](10) NULL,
[ForeNames] [varchar](50) NULL,
[Surname] [varchar](50) NULL,
[DOB] [datetime] NULL,
[Sex] [varchar](1) NULL,
[Type] [varchar](10) NULL,
[Addr1] [varchar](50) NULL,
[Addr2] [varchar](50) NULL,
[Town] [varchar](50) NULL,
[County] [varchar](50) NULL,
[PostCode] [varchar](50) NULL,
[ConsentDate] [datetime] NULL,
[Leader] [bit] NULL,
[MobTel] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Status] [int] NULL CONSTRAINT [df_tblVolunteer_Status] DEFAULT ((0)),
[Date] [datetime] NULL,
[Reason] [int] NULL CONSTRAINT [df_tblVolunteer_Reason] DEFAULT ((0)),
[Comment] [varchar](400) NULL,
[UserName] [varchar](50) NULL,
[Password] [varchar](20) NULL,
[Flag] [bit] NOT NULL CONSTRAINT [df_tblVolunteer_Flag] DEFAULT ((0)),
[Accept] [datetime] NULL,
[PrintSelect] [bit] NOT NULL CONSTRAINT [DF_tblVolunteer_PrintSelect] DEFAULT ((0)),
[Created] [datetime] NOT NULL CONSTRAINT [df_tblVolunteer_Created] DEFAULT (getdate()),
[CreatedBy] [varchar](50) NOT NULL CONSTRAINT [df_tblVolunteer_CreatedBy] DEFAULT (suser_name()),
[Modified] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL,
CONSTRAINT [aaaaaVolunteer_PK] PRIMARY KEY NONCLUSTERED
(
[VolunteerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tblVolunteer] WITH NOCHECK ADD CONSTRAINT [ck_tblVolunteer_IDChecksum] CHECK ((hashbytes('SHA1',[VolunteerID]+CONVERT([varchar](7),[PartID],(0)))=[IDChecksum]))
GO
ALTER TABLE [dbo].[tblVolunteer] CHECK CONSTRAINT [ck_tblVolunteer_IDChecksum]
GO
ALTER TABLE [dbo].[tblVolunteer] WITH NOCHECK ADD CONSTRAINT [ck_tblVolunteer_Sex] CHECK (([Sex]='M' OR [Sex]='F' OR [Sex] IS NULL))
GO
ALTER TABLE [dbo].[tblVolunteer] CHECK CONSTRAINT [ck_tblVolunteer_Sex]
Thanks!
April 19, 2013 at 12:53 pm
What column is defined as the primary key in Access? It is possible that you have setup the linked table using a different column than 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/
April 19, 2013 at 12:59 pm
Hi Sean,
In Access the primary key is VolunteerID, just as in SQL-S. It's just a table linked from SQL-S into Access.
The situation is:
-I can delete records directly in SQL-S.
-I cannot: in the linked table, in a query dependent on the table, or in the Access form.
April 19, 2013 at 1:09 pm
a_ud (4/19/2013)
Hi Sean,In Access the primary key is VolunteerID, just as in SQL-S. It's just a table linked from SQL-S into Access.
The situation is:
-I can delete records directly in SQL-S.
-I cannot: in the linked table, in a query dependent on the table, or in the Access form.
It is an absolutely an issue in Access. I would guess that your form is not just select * from MsTable. There are probably some joins and other stuff going on there. It can be a number of issues. Depending on how your form is setup. Maybe you are using the incorrect recordset, maybe there are aggregates...
If you can at least post your query that the form binds to that would be a start. If you google "access not updateable query" you will find dozens and dozens of people who have wrestled with this. There are just so many possibilities here it is hard to help you figure out the issue.
_______________________________________________________________
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 19, 2013 at 1:10 pm
Seems to be that in the table I've got 2 'bit/binary' fields not initialised.
If you delete them (or initialise them), the update is fine.
Thanks, advice always appreciated anyway!!
April 19, 2013 at 1:16 pm
a_ud (4/19/2013)
Seems to be that in the table I've got 2 'bit/binary' fields not initialised.If you delete them (or initialise them), the update is fine.
Thanks, advice always appreciated anyway!!
Glad you figured out the issue. Can you explain what you mean by initializing a bit column means? Do you mean putting a default constraint on the column?
_______________________________________________________________
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 19, 2013 at 3:22 pm
By not initialiased I meant with no value in them, ie. Null. When trying to connect ad update one of the 'other' fields, that would cause an error.
Deleting (or assigning a value) to these binary field solves the problem. I know you know, anyway.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply