Cannot execute a cmd.CommandText in ADO 2

[maupiti]

Access 2003 and SQL 2000 Server.

All tables resided in the SQL 2000 Server.
All tables are linked using ODBC.
The user interface is Access 2003

Run-Time error '3709'
The connection cannot be used to perform this operation.
It is either closed or invalid in this content

The yellow highlight is at the code below
cmd.Execute , , adAsyncExecute

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

Private Sub User_Login_Using_SQL_String()
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

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 FLAG


DoCmd.Close acForm, "Main_Login"
DoCmd.OpenForm "Main_Switch_Board"
End If
End If
End If

RecordSet_User_Account.Close

End Sub

 

[JoeAtWork]

I don't see you setting the ActiveConnection property of your Command object anywhere. Add:

Set cmd.ActiveConnection = CurrentProject.Connection

Also, I would recommend setting the CommandType property, or you may get another error:

cmd.CommandType = adCmdText

 

[Golom]

I think that you need

Set cmd = New ADODB.Command
[COLOR=red]Set cmd.ActiveConnection = cnThisConnect[/color]