Solved You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column

[RogerCooper]

I am receiving the error "You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column" when running the following code.

INSERT INTO [CRM Analysis] ( ID, [Table], [Date], CREATED_BY, ASSIGNED_TO )
SELECT dbo_CRM_Task.ID, "Task" AS Expr1, dbo_CRM_Task.CREATE_DATE, dbo_CRM_Task.CREATED_BY, dbo_CRM_Task.ASSIGNED_TO
FROM dbo_CRM_Task LEFT JOIN [Task in CRM Analysis] ON dbo_CRM_Task.ID = [Task in CRM Analysis].ID
WHERE (((dbo_CRM_Task.CREATE_DATE)>=#7/1/2024#) AND (([Task in CRM Analysis].ID) Is Null));

When I run this in the immediate mode, it works correctly. The error only occurs when I use it in code (I have set SQLString to the code I wish to run).

DB.Execute SQLString

Any ideas on how to avoid this error.

 

[GPGeorge]

Show us your existing VBA code, then, please. I don't think the SQL you show corresponds to opening a recordset using OpenRecordset.

My guess is that it needs to be something like this.

Public Sub Populatedates()
Dim intDateCount As Integer
    For intDateCount = 0 To 1094
        CurrentDb.Execute "INSERT into Table1 (StartDate) SELECT #" & DateAdd("d", intDateCount, Date) & "#", dbFailOnError + dbSeeChanges
    Next intDateCount
End Sub

 

[sonic8]

Any ideas on how to avoid this error.

The error message is not fitting your situation exactly, but it still hints to the solution:

DB.Execute SQLString, dbSeeChanges

 

[RogerCooper]

The error message is not fitting your situation exactly, but it still hints to the solution:

DB.Execute SQLString, dbSeeChanges

That is the solution. Thank you for your help.