Adding record on sql server?

  • Thread starter Thread starter Josh Grameson
  • Start date Start date
J

Josh Grameson

Here is a piece of code that works when I run it against a table that is
linked from another MDB, but when I run it against a table on SQL Server
(linked with ODBC) it does not. And I'm wondering why, and is there away to
get it to work?


****SQL Server Table:
Private Sub Command0_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "[dbo_tblCustomer]", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs.AddNew
rs("name") = "New Name"
rs.Update
MsgBox rs("name")

Here is get an error. Same if I don't do rs.Update.

End Sub


****Linked Access Table
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "[tblCustomer]", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs("name") = "New Name"
rs.Update
MsgBox rs("name")

this works fine here
End Sub
 
run-time error '-2147217887 (80040e21)':

ODBC--call failed


Douglas J. Steele said:
What is the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Josh Grameson said:
Here is a piece of code that works when I run it against a table that is
linked from another MDB, but when I run it against a table on SQL Server
(linked with ODBC) it does not. And I'm wondering why, and is there away to
get it to work?


****SQL Server Table:
Private Sub Command0_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "[dbo_tblCustomer]", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs.AddNew
rs("name") = "New Name"
rs.Update
MsgBox rs("name")

Here is get an error. Same if I don't do rs.Update.

End Sub


****Linked Access Table
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "[tblCustomer]", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs("name") = "New Name"
rs.Update
MsgBox rs("name")

this works fine here
End Sub
Click to expand...
Click to expand...
 
Back
Top