how to solve it : "run-time error '3146' ODBC-call failed"?

[Lilyli]

I am a new programmer. I create a function in MS Access Modules, want to get some properties from a .txt file. I linked a table from SQL Server to MS Access. In Access table, I can open the linked table which is from SQL Server. I want to append those properties which come from .txt to the linked table (dbo_Acadbase). Now I can get those properties from .txt file. When I want to append them to dbo_acadbase, the error message:"run-time error '3146' ODBC-call failed" will appear. If I change the dbo_acadbase to a Access table, I can append data to the Access table. The code is as following, please help me! Thank you in advance!

DoCmd.Hourglass True

Set db = CurrentDb
Set dbo = db.OpenRecordset("dbo_acadbase")

With dbo

.AddNew ' Add new record.
'add properties get from .txt file

!cadno = cad_no
!fileName = fName
!DATE1 = dDate
!dwgNo = dNo
!rev = revs
!Projcode = pCode
!title1 = t1
!title2 = t2
!title3 = t3
!title4 = t4
!sheet = sht
!state = "issued"
.Update ' Save changes.
End With
Loop

'All done
db.Close
DoCmd.Hourglass False

 

[cmmrfrds]

Do you have a primary key defined for the linked table and does it have an unique index in sql server?

 

[Lilyli]

Yes, I do have one: cadno is the primary key in dbo_acadbase. And I created a unique value for cad_no and assign it to CADNO in dbo_acadbase. When I execute this program, a popup box "SQL Server login" will ask me the login ID and password. After entering "sa" for login ID and " " for password, that error message will appear. If I want to debug, ".update" will be highlighted.

 

[cmmrfrds]

I don't use ADO not DAO, but any recordset that you want to update needs to be set that way. Check the options available on the OpenRecordset function. There should be an option for update. Normally, this is done through the cursor or locktype.