Using ADO to edit the result of a query

[Mr Banan Qry]

Hi!

I've banged myself into a tough headache and was hoping that someone would be kind and show me some light.

For some reason i've had to make an update on some old VBA projects of mine. In this update i switch all references of DAO to ADO. I know there are some limits to what i can accomplish with ADO (where i would need to use DAO). Anyways in some of these old projects i use an SQL statement to provide me with a limited number of records which i then modify by iterating through the recordset.
I can't get this to work with ADO. It's as if it always expects me to provide a table in the argument.

In short my question is. Is it possible to change the values returned from an SQL statement using ADO

        'This is the DAO version which is exactly what i'm looking to using ADO
        strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))"
        Set objRecordset = pubObjDatabase.OpenRecordset(strSqlCommandText, dbOpenDynaset)
        objRecordset.Edit
            objRcsToDbTable.Fields("Heading") = ProcessForm.txtBoxHeading.Text
        objRecordset.Update

This is what i've done and it doesn't work.

        strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))"
        Set objRecordset = New ADODB.Recordset
        objRecordset.Open strSqlCommandText, pubObjDatabase, adOpenDynamic, adLockOptimistic
        objRecordset.Edit
            objRcsToDbTable.Fields("Heading") = ProcessForm.txtBoxHeading.Text
        objRecordset.Update

I can think of some work arounds like, creating a temp table and insert/update all the records from there or creating an UPDATE SQL statement. However i'm really not too fond of those ideas.

 

[Galaxiom]

ADO doesn't use the Edit method. Simply go to the record, change the values and Update.

BTW Your code would be tidier if you used a With block rather than repeating the object so many times. Referring to the Fields collection can also be avoided by using the bang.

objrecordset!fieldname

 

[MarkK]

Notice that the recordset you attempt to update is not the same one you've placed in edit mode. . .

        strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))"
        Set objRecordset = New ADODB.Recordset
        objRecordset.Open strSqlCommandText, pubObjDatabase, adOpenDynamic, adLockOptimistic
        objRecordset.Edit
           [COLOR="Red"][B] objRcsToDbTable[/B][/COLOR].Fields("Heading") = ProcessForm.txtBoxHeading.Text
        objRecordset.Update

This would be solved using a With block, but you might also want to search on how to "Require Variable Declaration" in VBA modules.

 

[Mr Banan Qry]

Thanks for the response.

I tried to produce a clean example and modified the code directly before posting so it would just show the most interresting part. The other object i refer to did exist in my code so it was declared but it should obviously not have been there.

I don't like the with statement very much since it's not possible to remove if i edit the code at runtime

I should have desribed my issue better. The issue i experience appears on this line of code.

objRecordset.Open strSqlCommandText, pubObjDatabase, adOpenDynamic, adLockOptimistic

 

[Galaxiom]

I don't like the with statement very much since it's not possible to remove if i edit the code at runtime

Editing the VBA code at runtime? You are surely joking aren't you?

I should have desribed my issue better. The issue i experience appears on this line of code.

objRecordset.Open strSqlCommandText, pubObjDatabase, adOpenDynamic, adLockOptimistic

Your ActiveConnection argument seems to be a database. I don't think that is right for an ADO recordset. Normally it would be an ADO Connection Object or a connection string.

For the database I am in I normally use CurrentProject.AccessConnection

 

[Mr Banan Qry]

I use an ADODB.Connection object but i referd to it as "pubObjDatabase" out of old DAO habit since it stores the same values as i put supply to the DAO database object.

Public pubObjDatabase As ADODB.Connection

I do make changes at runtime (not when I've deployed something but while debugging). Sometimes i might incapsulate the code part where an with statement is placed, then i wouldn't be able to indent the code part properly.

Anyways I guess at the end it's more of a preference thing. I prefer to have allot of the objects specified eventhough it's more text. It's more readable in view but i do get that it might be minority's view.

Having said that I really appriciate recomendations on good practise.

 

[Pat Hartman]

Unless your DAO code is causing some problem, why convert it? This is just a make-work project with a lot of pain and no gain. ADO is the current default for Access (ACE). I know for 1-2 versions ADO was recommended but not any longer.