Get ADO to update edits

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Get ADO to update edits

Post by 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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78350
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get ADO to update edits

Post by 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.
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Get ADO to update edits

Post by 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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78350
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get ADO to update edits

Post by HansV »

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

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Get ADO to update edits

Post by 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.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Get ADO to update edits

Post by 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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78350
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get ADO to update edits

Post by HansV »

I guess the second .Update is an oversight - only one is needed.
Best wishes,
Hans

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Get ADO to update edits

Post by Jan Karel Pieterse »

On the dot Hans.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com