Get ADO to update edits

[agibsonsw]

Hello. Access 2003.
I'm using ADO to update records in an Access table of another database on my computer:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Users\Andrew\Documents\Staff DAO ADOa.mdb;"
strSql = "tblStaff"
cn.Open strConn
rs.Open strSql, cn, adOpenStatic, adLockOptimistic, adCmdTable
I can only get it to work with the last 3 argument as shown and for a table data source.

Why is it that only this combination works? Why not adOpenDynamic? How can I get it to work if strSql = "SELECT F1,F2 FROM tblStaff"?
Thanks for any guidance, Andy.

[HansV]

If you want to use a SQL string, you must change adCmdTable to adCmdText.
You should then be able to change adOpenStatic to adOpenDynamic if you wish.

[agibsonsw]

Thanks very much.
It also seems that, when using a Jet database, a client-side cursor is generally needed to perform edits? The fact that I'm not on a network might also influence
this requirement? Ta, Andy.

[HansV]

I'm sorry, that is unfamiliar territory to me. As I mentioned before, I always use DAO with Jet databases.

[Jan Karel Pieterse]

You could use code like this:

Code: Select all

Sub Demo()
    Dim oADOconn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Set oADOconn = New ADODB.Connection
    oADOconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=""c:\MyPath\MyDatabase.mdb"";"
    Set oRS = New ADODB.Recordset
    oRS.Open "SELECT * FROM MyTable WHERE Id=-1", oADOconn, adOpenKeyset, adLockOptimistic, adCmdTable
    With oRS
        .AddNew
        .Fields(1).Value = 1
        .Fields(2).Value = 2
        .Update
        .Update
        .Close
    End With
    Set oRS = Nothing
    oADOconn.Close
    Set oADOconn = Nothing
end sub

Remark: I filter the data by filtering an Id field, which I deliberately set to a value that does not exist in the table, so the recordset contains no records when the SQL is executed.

[agibsonsw]

Thank you. I had to amend adCmdTable to adCmdText (otherwise I receive a 'syntax error in from clause' error message).
I appreciate that the dummy value returns an empty recordset - ideal if I'm only adding a record.
Why two Updates? Ta, Andy.

[HansV]

I guess the second .Update is an oversight - only one is needed.

[Jan Karel Pieterse]

On the dot Hans.