Public conn as ADODB.Connection
Public rs1 as ADODB.Recordset

Private Sub cmdOK_Click()
   Dim UserID As Long
   Dim SQL As String
   Dim cmd As ADODB.Command
 
   Set cmd = New ADODB.Command
   cmd.ActiveConnection = conn
 
'1st try - Execute method of the Connection object
'  Gives "Sytax error in INSERT INTO statement"
   conn.Execute "INSERT INTO tblUsers (UserName, Password) " & _
                "VALUES ('" & txtUserName & "', " & Chr(34) & txtPassword & Chr(34) & ")"
 
'2nd try - using a string variable instead of straight text
'  Gives "Sytax error in INSERT INTO statement"
   SQL = "INSERT INTO tblUsers (UserName, Password, Active, Comments) VALUES ('" & txtUserName & "', '" & txtPassword & "', -1, '');"
   conn.Execute SQL, , adExecuteNoRecords
 
'3rd try - using the command object
'  Gives "Sytax error in INSERT INTO statement"
   SQL = "INSERT INTO tblUsers (UserName, Password, Active, Comments) VALUES ('" & txtUserName & "', '" & txtPassword & "', -1, '');"
   Set cmd = New ADODB.Command
   cmd.ActiveConnection = conn
   cmd.CommandText = SQL
   cmd.CommandType = adCmdText
   cmd.Execute
 
'4th try - using a query in the access database
'  Gives "Too few parameters, 2 expected"
   cmd.CommandText = "qryInsertUser"   'This query exists in the database
   cmd.CommandType = adCmdStoredProc
   cmd.CreateParameter "UserName", adVarChar, adParamInput, , txtUserName
   cmd.CreateParameter "Password", adVarChar, adParamInput, , txtPassword
   cmd.NamedParameters = True
   cmd.Execute
 
'5th try - a colleague inerted records using this circumvent way of running the query
'  Gives "Sytax error in INSERT INTO statement"
   SQL = "INSERT INTO tblUsers (UserName, Password) VALUES ('" & txtUserName & "', '" & txtPassword & "');"
   rs1.Open SQL, conn, adOpenForwardOnly, adLockReadOnly
 
'This works perfectly
   rs1.Open "SELECT * FROM tblUsers WHERE UserName = '" & txtUserName & "'", conn, adOpenDynamic
   UserID = rs1!UserID
   rs1.Close
   rs1.Open "SELECT * FROM tblGroups ORDER BY GroupName", conn, adOpenDynamic
   rs1.MoveFirst
   rs1.Find "GroupName = 'Users'"
   conn.Execute "INSERT INTO tblUserGroups (UserID, GroupID) VALUES (" & UserID & ", " & rs1!GroupID & ")"
End Sub

conn.execute "INSERT INTO tblUsers (UserName, Password) VALUES ('Hello', 'World');", conn

'6th try - using a query in the access database
   Dim arr(1)
   cmd.CommandText = "qryInsertUser"   'This query exists in the database
   cmd.CommandType = adCmdStoredProc
   arr(0) = "sdfasdr"
   arr(1) = "kihjui"
   cmd.Execute UserID, arr()

tblUsers
===================
UserID       AutoNumber
UserName     String  (Not NULL, not empty string)
Password     String  (Not NULL, Empty string OK, Default "")
Active       Boolean (Not NULL, default True)
Comment      String  (Not NULL, Empty String OK, Default "")

on error resume next
conn.Execute sql,recaffected
if err<>0 then
  Debug.print err.description
else 
  Debug.print recaffected
end if
conn.close

SET cmd.ActiveConnection = conn