December 15, 2008 at 9:32 am
I'm pretty new to SQL Server so I apologize if this question doesn't make a lot of sense. I've got an Access 2000 database that was upsized to SQL Server 2000 and I've been having trouble with one of the tables that has an identity column. On the ACCESS side the column is set to AutoNumber and Increment. When a user enters adds records to this table using my access forms there are no issues.
I've got a VBA function that allows users to copy records into this table. It seems like after users run this function, SQL Server forgets that this is an Autonumber column. After the function has been run, if a user trys to add a record on the Access forms they get the error message: ODBC--insert on a linked table 'CUSTOMNEWDISTRIBUTION' failed. [Microsoft][ODBC SQL Server Driver][SQL Server] Explicit value must be specified for identity column in table 'CUSTOMNEWDISTRIBITION' when IDENTITY_INSERT is set to ON (#545).
After a few minutes the error message seems to go away (not sure what clears it) and the user can start entering records again through my Access forms. My VBA function is using the DAO library to add new records to the table.
Thanks for any help you can provide.
December 15, 2008 at 9:43 am
Something the VB routine is doing is causing the command SET IDENTITY_INSERT ... ON to happen for your data copy process. Have you tried to do a trace with SQL Profiler to see where this is happening? Basically it sounds like after the copy is done, you need to make sure to do the command SET IDENTITY_INSERT ... OFF.
December 15, 2008 at 10:20 am
can u paste the portion of the code trying to paste the data... including the part of the code that is setting identity_insert to ON.
Also microsoft reports this as a bug in sql server.
http://support.microsoft.com/kb/828945
You may need to apply patches mentioned there.
December 15, 2008 at 10:55 am
Hi, Here is a copy of my code. I'll ask our dba to run a trace on it to see if it is setting the switch to ON.
Dim dbNewDistrib As DAO.Database
Dim vPayee As String
Dim vCounter As String
Dim vEmployee As String
Dim vLine As Integer
Dim vActivity As String
Dim vAcctCat As String
Dim vStart As Date
Dim vEnd As Date
Dim vSalary As Double
Dim vEffort As Double
Dim vEffect As Date
Dim dbAppointments As DAO.Database
Dim rcdCurrentDistrib As DAO.Recordset
Dim DistLines As Integer
vPayee = Forms![EMPLOYEE INFO]![APPOINTMENTS subform].Form![Employee]
Set dbAppointments = CurrentDb
Set rcdCurrentDistrib = dbAppointments.OpenRecordset("ENCUMBRANCE")
Set rcdNewDistrib = dbAppointments.OpenRecordset("DISTLINESORT", dbOpenDynaset, dbSeeChanges)
strEmployee = "PAYEE LIKE " & vPayee
DistLines = 0
rcdNewDistrib.MoveLast
vLine = rcdNewDistrib![LINE_NBR] + 1
rcdCurrentDistrib.MoveFirst
rcdCurrentDistrib.FindFirst (strEmployee)
Do While Not rcdCurrentDistrib.NoMatch
vEmployee = rcdCurrentDistrib![PAYEE]
vActivity = rcdCurrentDistrib![ACTIVITY]
vSalary = rcdCurrentDistrib![SALARY_PCT]
vEffort = rcdCurrentDistrib![EFFORT_PCT]
vStart = Forms![EMPLOYEE INFO]![APPOINTMENTS subform].Form![Effective_Date]
vEnd = rcdCurrentDistrib![End_Date]
vEffect = Forms![EMPLOYEE INFO]![APPOINTMENTS subform].Form![Effective_Date]
If vEnd >= vEffect Then
rcdNewDistrib.AddNew
rcdNewDistrib![PAYEE] = vEmployee
rcdNewDistrib![LINE_NBR] = vLine
rcdNewDistrib![ACTIVITY] = vActivity
rcdNewDistrib![ACCT_CATEGORY] = vAcctCat
rcdNewDistrib![Start_Date] = vStart
rcdNewDistrib![End_Date] = vEnd
rcdNewDistrib![SALARY_PCT] = vSalary
rcdNewDistrib![EFFORT_PCT] = vEffort
rcdNewDistrib![Effect] = vEffect
rcdNewDistrib.Update
vLine = vLine + 1
DistLines = DistLines + 1
End If
rcdCurrentDistrib.FindNext (strEmployee)
Loop
If DistLines = 0 Then
MsgBox ("None of the current distributions for this employee are still valid as of " & vEffect & " Please enter the new distributions manually.")
Else
MsgBox (DistLines & " Distributions have been copied")
End If
Me.Form.Requery
rcdNewDistrib.Close
rcdCurrentDistrib.Close
December 15, 2008 at 11:26 am
there is no reference to identity_insert being set on.
also apply the patch suggested by microsoft.
December 15, 2008 at 11:40 am
If you aren't sure if there is something setting the property on or off, run a trace (use Profiler) to check and see if this is executing.
I've moved this to the 2000 forum.
December 16, 2008 at 8:57 am
Figured this one out...thanks for the tip on the trace log. When using the DAO library in Access to open a sql server recordset it forces you to use the dbSeeChanges option. When I look at the trace log I can see that dbSeeChanges is setting the IDENTITY_INSERT TO ON, but it doesn't set it back to off. I changed my code to use the DoSQL command instead of using DAO and it is working perfectly.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply