Refresh causes error message

[pochta]

I posted this earlier as a failure to update. Actually it does UPDATE the field in Access but it generates an error message.


Global Adodc1 As Object

Public Sub PokeAccess()

frmSqlTest.Adodc1.CommandType = adCmdUnknown

frmSqlTest.Adodc1.RecordSource = "UPDATE Test1 SET Test1.Number=1234 WHERE ID=3"

frmSqlTest.Adodc1.Refresh 'Stops execution here

frmSqlTest.Adodc1.RecordSource = "SELECT Number FROM Test1 WHERE ID=3"

frmSqlTest.Adodc1.Refresh

The code runs and it does update the record in Access but it stops execution and gives me the error mesage "operation not allowed when object is closed". Oddly it does not give that message on the SELECT operation.

How do I Open the object or otherwise avoid the error message?

 

[Golom]

Just guessing but, assuming that "Adodc1" is an ADO Data Control, you have set a RecordSource to an UPDATE statement that doesn't return records. UPDATE changes data in the database but doesn't return anything.

You may instead want to define a connection to the database and run the UPDATE using the connection object's EXECUTE method.

 

[pochta]

Golom,

That makes sense. Can you give me a little detail on how to do it.

It looks like all that is happening here is that SQL statements are being passed to Access. Surely it is not a big deal if you get the syntax right. Perhaps my approach is wrong. Is there a better way?

 

[Golom]

Dim adConn As ADODB.Connection
Dim iAffected As Integer
Set adConn = New ADODB.Connection
adConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Path\db1.mdb;" 
adConn.Execute "UPDATE Test1 SET Test1.Number=1234 WHERE ID=3", _
               iAffected, adExecuteNoRecords

The number of records that were updated will be returned in "iAffected".