Hi

The problems is the GO after SET IDENTITY_INSERT.. ON

I suggest you do this in a stored procedure.

John

"For example John Smith" <someone@microsoft.com> wrote in message
news:3f533f94$0$10781$afc38c87@news.easynet.co.uk. ..
> An update on this, it's now failing with the message:
>
> Error 544 - [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
> explicit value for identity column in table 'zstblApplicationFunction'
when
> IDENTITY_INSERT is set to OFF. (ODBC.QueryDef)Error 3146 - ODBC--call
> failed. (DAO.QueryDef)
>
> In Profiler, the SQL trace shows:
>
> <---------------------------------------------------
> SET IDENTITY_INSERT "dbo"."zstblApplicationFunction" ON
> go
> exec sp_executesql N'INSERT INTO "dbo"."zstblApplicationFunction"
>
("ApplicationFunctionID","SortOrder","ParentApplic ationFunctionID","Applicat
>
ionFunctionName","DisplayName","Hyperlink","FormHe lpID","IsMenuItem","IsSubM
>
enuItem","IsEnforced","SysIsInsert","SysIsSelect", "SysIsUpdate","SysIsDelete
> ","SysIsExecute") VALUES
> (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P1 2,@P13,@P14,@P15)',
N'@P1
> int,@P2 int,@P3 int,@P4 varchar(50),@P5 varchar(50),@P6 varchar(8000),@P7
> int,@P8 bit,@P9 bit,@P10 bit,@P11 bit,@P12 bit,@P13 bit,@P14 bit,@P15
bit',
> 1, 3, NULL, 'SECURITYMENU', 'Security', NULL, NULL, 1, 0, 0, 0, 1, 0, 0, 0
> go
> IF @@TRANCOUNT > 0 ROLLBACK TRAN
> go
> ---------------------------------------------------->
>
> If I copy and paste that into Query Analyser it inserts the record. When
> Access issues these commands to the server it fails. Odd.
>
>