Question in Access Recordset

[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

 

[Douglas J. Steele]

Do you have a primary key defined for the SQL Server table? It won't be
updatable unless you do.

 

[Josh Grameson]

Yes I do.

Do you have a primary key defined for the SQL Server table? It won't be
updatable unless you do.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

 

[Josh Grameson]

BTW,
It does update, only it does not allow you get the value until you CLOSE it
and then query it, whereas the table in Access I get still get the value
without closing it.

Do you have a primary key defined for the SQL Server table? It won't be
updatable unless you do.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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