DAO.AddNew and ODBC 3146

[Guest]

Summary: I am reading default records from one SQLServer table and adding
them (with modifications) to a secnond SQL Server table. I get only one
record added and then I get an ODBC 3146 error on the ".Update"

Here's my Code:
Set rsObj = dbObj.OpenRecordset("SELECT * FROM TLCOWNER_TLC_RULES",
dbOpenDynaset, dbSeeChanges)
Set rsObjTemplate = Me.Recordset
Do While Not rsObj.EOF
With rsObjTemplate
.AddNew
!ContractCode = modInit.ContractCode
!RULE_CODE = rsObj.Fields("RULE_CODE").Value
!RULE_TYPE = rsObj.Fields("RULE_TYPE").Value
!RULE_DESC = rsObj.Fields("RULE_DESC").Value
!SR = False
!PR = False
!PA = False
!Rule_Created_TS = rsObj.Fields("RULE_CREATED_TS").Value
!Rule_Selected = Now
.Update
.Bookmark = .LastModified
End With
rsObj.MoveNext
Loop

My table is defined as:
ContractCode nchar 6
SR bit 1
PR bit 1
PA bit 1
Rule_Code nvarchar 50
Rule_Desc nvarchar 250
Rule_Type nvarchar 10
Rule_Created_TS datetime 8
Rule_Selected datetime 8

 

[Guest]

OK Sorry. I fixed the error by changing my primary key from ContractCode to
an autoincrement ID field? I'm not sure why that helped....

 

[Roger Carlson]

This article http://support.microsoft.com/default.aspx?scid=kb;en-us;Q278696
might be a good thing to keep in mind when using linked SQL Server tables in
Access. I'm not sure it's related to your problem, but I note that you do
have bit fields in your table.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L