I use this template for recordsets, works well;

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim strMsg As String

'SQL_Connection
On Error GoTo <function_name>_Error

conn.Open CurrentProject.Connection
Set cmd.ActiveConnection = conn

strSQL = <insert SQL TExt>

cmd.CommandText = strSQL
cmd.CommandType = adCmdText
Set rs = cmd.Execute

If rs.State = adStateClosed Then
 'no connection made
 Goto <function_name>_error
else
 <Connection OK - carry out function>
End if

<function_name>_Exit:
 Set rs = Nothing
 Set cmd = Nothing
 conn.Close
 Set conn = Nothing
 Exit Sub

<function_name>_Error:
 strMsg = "ERROR: " & Err.Description
 If MsgBox(strMsg, vbCritical, "Error:") = vbOK Then
 End If
 Resume <function_name>_Exit