Question in Access Recordset

  • 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
 
Do you have a primary key defined for the SQL Server table? It won't be
updatable unless you do.
 
Yes I do.

Douglas J. Steele said:
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!)



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...
 
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.

Douglas J. Steele said:
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!)



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