ADO Update Records

[Scott]

I'm trying to update the current record on a form in my ADP database. I'm
getting an error saying "operation is not allowed when the object is closed"
on my code below. Can some edit my code so the update sql will update the
record?

CODE *********

Dim rst As ADODB.Recordset
Dim intCounter As Integer

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "UPDATE myTable SET myTable.adminID = '1' WHERE
myTable.userID=" & [Forms]![frmUsers]![userID]
rst.close
Set rst = Nothing

 

[RoyVidar]

I'm trying to update the current record on a form in my ADP database.

I'm getting an error saying "operation is not allowed when the object
is closed" on my code below. Can some edit my code so the update sql
will update the record?

CODE *********

Dim rst As ADODB.Recordset
Dim intCounter As Integer

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "UPDATE myTable SET myTable.adminID = '1' WHERE
myTable.userID=" & [Forms]![frmUsers]![userID]
rst.close
Set rst = Nothing

You are opening a recordset, it seems, with an action query. I'm not
sure action queries would return a recordset to close. Try executing
on the connection in stead.

dim strSql as string
strSql = "UPDATE myTable SET myTable.adminID = '1' " & _
"WHERE myTable.userID=" & [Forms]![frmUsers]![userID]
currentproject.connection.execute strsql,,adexecutenorecords+adcmdtext

 

[Douglas J Steele]

You can't use an UPDATE statement with a Recordset object. Updates don't
produce recordsets.

Try using a Command object instead.

Dim cmd As ADODB.Command
Dim strSQL As String

strSQL = "UPDATE myTable SET " & _
"myTable.adminID = '1' " & _
"WHERE myTable.userID=" & _
[Forms]![frmUsers]![userID]

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = strSQL
cmd.Execute
Set cmd = Nothing

 

[Scott]

thank you for setting me straight.

You can't use an UPDATE statement with a Recordset object. Updates don't
produce recordsets.

Try using a Command object instead.

Dim cmd As ADODB.Command
Dim strSQL As String

strSQL = "UPDATE myTable SET " & _
"myTable.adminID = '1' " & _
"WHERE myTable.userID=" & _
[Forms]![frmUsers]![userID]

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = strSQL
cmd.Execute
Set cmd = Nothing


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to update the current record on a form in my ADP database. I'm
getting an error saying "operation is not allowed when the object is closed"
on my code below. Can some edit my code so the update sql will update the
record?

CODE *********

Dim rst As ADODB.Recordset
Dim intCounter As Integer

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "UPDATE myTable SET myTable.adminID = '1' WHERE
myTable.userID=" & [Forms]![frmUsers]![userID]
rst.close
Set rst = Nothing