ADO cannot write to SQL server

[Bernie]

I'm trying to edit data on the sql express server. I can edit data in table
(machine ODBC data source) but I cannot alter data by using VBA.

Public Function fnODBC()
'DAO
Dim ws As Workspace
Dim db As Database
Dim strConnection As String
Dim rs As Recordset
Dim strSQL As String
Dim intid, strNo As Long
Set ws = DBEngine.Workspaces(0)
strConnection = "ODBC=Fuel;DSN=Fuel;UID=sa;PWD=password"
Set db = ws.OpenDatabase("Fuel", dbDriverNoPrompt, False, strConnection)

strSQL = "select * from tabfuel;"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
'While Not rs.EOF
'Debug.Print rs.Fields(3)
'rs.movenext
'Wend

rs.Edit
rs.Fields(1) = 10
rs.Update

End Function

What's the problem? Could anyone assist me?

Rgds

 

[Douglas J. Steele]

Does the table have a primary key (or at least a unique index) defined? ODBC
tables are not updatable unless they do.

 

[Bernie]

Yes it does.

ID / autonumber is unique and primary. Is the syntax of my ADO construction
correct?

 

[Douglas J. Steele]

You're using DAO, not ADO, so your question doesn't really make sense to
me...

The code looks syntactically correct. I do question, though, why you'd use a
recordset to change a value: use a pass-through query instead!

 

[Clifford Bass]

Hi Bernie,

Basic question: What is the error that you get?

Clifford Bass

 

[Sylvain Lafontaine]

First, like others have said, this is DAO, not ADO.

In your case, when workgin with a SQL-Server as the backend, I think that
you need to add the dbSeeChanges option when opening the recordset when the
primary key is an identity (autoincrement) field:

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Alex Dybenko]

Hi,
best approach here - link table to MDB and open linked table for edit
purposes:

Set rs = currentdb.OpenRecordset(strSQL, dbOpenDynaset)

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Bernie]

Hi,
best approach here - link table to MDB and open linked table for edit
purposes:

Set rs = currentdb.OpenRecordset(strSQL, dbOpenDynaset)

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




Dear All

Many thanks for your help. This was a tough nut to crack.

Here comes the code that works as I'm sure other folks will face same
problem. I prefer DAO as it is very simply straight forward. I had to add
'dbChanges' else no edit.

Public Function fnYES()

'DAO
Dim ws As Workspace
Dim db As Database
Dim strConnection As String
Dim rs As Recordset
Dim strSQL As String
Dim intid, strNo As Long
Set ws = DBEngine.Workspaces(0)
strConnection = "ODBC=Fuel;DSN=Fuel;UID=sa;PWD=password"
Set db = ws.OpenDatabase("Fuel", dbDriverNoPrompt, False, strConnection)

strSQL = "select * from tabfuel;"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

'While Not rs.EOF
'Debug.Print rs.Fields(3)
'rs.MoveNext
'Wend

rs.Edit
rs.Fields(1) = 9090
rs.Update

End Function

 

[Brock]

Bernie, I am insterested in how this work for you. I have some code which I
did not write, but I am trying to update to include linked tables from SQL
Server 2005. Since, I am not editing the data, just reading it, I thought
maybe you caould give me your input on this.

The code is as follows:
Function IsInRCATrainList(MyTrain As Integer) As Boolean
'*** Check whether the trian is in our RCA Train List
'Start by Tai...4/15

Dim MyRCATrainList As Recordset
Set MyRCATrainList = CurrentDb.OpenRecordset("Tai_01_My RCA Trains")
With MyRCATrainList
If MyRCATrainList.RecordCount <> 0 Then
.MoveFirst
End If
.Index = "PrimaryKey"
.Seek "=", MyTrain
If .NoMatch Then
IsInRCATrainList = False
Else
IsInRCATrainList = True
End If
End With

MyRCATrainList.Close

'End by Tai...4/15
End Function

Any idea on how I might be able to apply your code in my situation?

Thanks