Operation cannot be performed while exe. asynchronously 1

[maupiti]

Access 2003, and SQL 2000 Server

Run-Time error '3711'. Operation cannot be performed while
executing asynchronously.

////////////////////////////////////////////

The first code below work, but the second one immediately after this fail.

cmd.CommandText = "INSERT INTO Table_User_Log_On ( Name, Date_And_Time_Log_On, Department_Group, Access_Status) Values " _
& "('" & RecordSet_User_Account!Name & "', '" & Now() & "', '" & RecordSet_User_Account!Department_Group & "', '" & RecordSet_User_Account!Access_Status & "')"
cmd.Execute , , adAsyncExecute

'''''''''''''''''''''''''''''''''''''''

ERROR. THE YELLOW HIGHLIGHT AT THE CODE BELOW

cmd.CommandText = "UPDATE User_Account " _
& "SET Last_Access_Date = Now() " _
& "WHERE Name = '" & Forms!Main_Login!User_Name & "';"

/////////////////////////////////////////////

Private Sub Command46_Click()
Call User_Login_Using_SQL_String1
End Sub


Private Sub User_Login_Using_SQL_String1()
Dim cnThisConnect As ADODB.Connection
Dim RecordSet_User_Account As New ADODB.Recordset
Set cnThisConnect = CurrentProject.Connection

Dim cmd As ADODB.Command
Dim Str_SQL As String

Str_SQL = "SELECT * FROM User_Account WHERE User_Account.Name = '" & Forms!Main_Login!User_Name & "'; "

Set cnThisConnect = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection


RecordSet_User_Account.Open Str_SQL, cnThisConnect, _
adOpenKeyset, adLockOptimistic, adCmdText

If IsNull(Me!User_Name) Then
MsgBox "Please enter your user name", vbInformation
Exit Sub
End If

If IsNull(Me!Password) Then
MsgBox "Please enter your password", vbInformation
Exit Sub
End If

''''''''''''' RECORDSET CODE BEGINS ''''''''

If RecordSet_User_Account.RecordCount = 0 Then
MsgBox "The user account does not exist.",
vbExclamation

Else
RecordSet_User_Account.MoveFirst

If RecordSet_User_Account!Name = Me!User_Name Then
If RecordSet_User_Account!P = Me!Password Then

Public_Current_User = RecordSet_User_Account!Name
Public_Access_Status = RecordSet_User_Account!
Access_Status

cmd.CommandText = "INSERT INTO Table_User_Log_On ( Name, Date_And_Time_Log_On, Department_Group, Access_Status) Values " _
& "('" & RecordSet_User_Account!Name & "', '" & Now() & "', '" & RecordSet_User_Account!Department_Group & "', '" & RecordSet_User_Account!Access_Status & "')"
cmd.Execute , , adAsyncExecute


'ERROR. YELLOW HIGHLIGHT AT THE CODE BELOW
cmd.CommandText = "UPDATE User_Account " _
& "SET Last_Access_Date = Now() " _
& "WHERE Name = '" &
Forms!Main_Login!User_Name & "';"


cmd.Execute , , adAsyncExecute
DoCmd.Close acForm, "Main_Login"
DoCmd.OpenForm "Main_Switch_Board"
End If
End If
End If

RecordSet_User_Account.Close

End Sub

 

[Golom]

Why do you need to run the INSERT and UPDATE commands async?

If you just use

cmd.Execute , , adCmdText

does it work then?

 

[maupiti]

Hi Golom, and thank you for your help.

If I use a "cmd.Execute , , adCmdText"
and then a "cmd.Execute , , adAsyncExecute"
on a second statement
as shown below, then it does work.

///////////////////////////////////////////////

cmd.CommandText = "INSERT INTO Table_User_Log_On ( Name, Date_And_Time_Log_On, Department_Group, Access_Status) Values " _
& "('" & RecordSet_User_Account!Name & "', '" & Now() & "', '" & RecordSet_User_Account!Department_Group & "', '" & RecordSet_User_Account!Access_Status & "')"
cmd.Execute , , adCmdText


cmd.CommandText = "UPDATE User_Account " _
& "SET Last_Access_Date = '" & Now() & "' "& "WHERE Name = '" & Forms!Main_Login!User_Name & "';"
cmd.Execute , , adAsyncExecute

/////////////////////////////////////

If I use a "cmd.Execute , , adCmdText"
and then a "cmd.Execute , , adAsyncExecute"
on a second statement as shown below,
then it gives me an error.

Run-Time Error -2147467259
Operation must have an updateable query

What cause it and how do I fix it ?

/////////////////////////////////////

cmd.CommandText = "INSERT INTO Table_User_Log_On ( Name, Date_And_Time_Log_On, Department_Group, Access_Status) Values " _
& "('" & RecordSet_User_Account!Name & "', '" & Now() & "', '" & RecordSet_User_Account!Department_Group & "', '" & RecordSet_User_Account!Access_Status & "')"
cmd.Execute , , adCmdText


cmd.CommandText = "UPDATE User_Account " _
& "SET Last_Access_Date = '" & Now() & "' "& "WHERE Name = '" & Forms!Main_Login!User_Name & "';"
cmd.Execute , , adCmdText

 

[Golom]

Just speculating but there may be some internal properties or parameters being set by your first use of the command object that are interfering with the second attempt to use it.

Why are you using a command object anyway? The Connection Object has an Execute method as well and it should be able to run those commands

Dim SQL As String
SQL = "INSERT INTO Table_User_Log_On ( Name, Date_And_Time_Log_On, Department_Group, Access_Status) Values " _
& "('" & RecordSet_User_Account!Name & "', '" & Now() & "', '" & RecordSet_User_Account!Department_Group & "', '" & RecordSet_User_Account!Access_Status & "')"
cn.Execute SQL,, adCmdText

SQL = "UPDATE User_Account " _
    & "SET Last_Access_Date = '" & Now() & "' "& "WHERE Name = '" & Forms!Main_Login!User_Name & "';"
cn.Execute SQL,, adCmdText