There are very few examples to be found on the Internet of using an ADODB.Command with ADODB.Parameters objects to issue a SQL INSERT statement to an Access table. Here is a working example! :D

Public Function Insert() As Boolean
On Error GoTo Err_Insert

Dim adoCMD As ADODB.Command
Dim strSQL As String

'Define a query to INSERT a new record into the FE temp table
strSQL = "INSERT INTO [tmptblqry_partsimportwizard] ([partnumber],[title],[qtyper],[addpartrecordflg]) " & _
"VALUES (?,?,?,?);"

'Define attachment to database table specifics
Set adoCMD = New ADODB.Command
With adoCMD
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = strSQL
.Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 25, Me.partnumber)
.Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, 50, Me.title)
.Parameters.Append .CreateParameter("p3", adSmallInt, adParamInput, 2, Me.qtyper)
.Parameters.Append .CreateParameter("p4", adBoolean, adParamInput, 2, Me.addpartrecordflg)
.Execute
End With

'Return a good return code
Insert = True

Exit_Insert:
'Clean up the connection to the database
Set adoCMD = Nothing

Exit Function

Err_Insert:
Call errorhandler_MsgBox("Class: clsObjPartsImportWizardTbl, Function: Insert()")
Insert = False
Resume Exit_Insert

End FunctionI found lists of Parameters Types here:

"ADO » Command » CreateParameter"
http://www.devguru.com/technologies/ado/8528.asp

"ADO CreateParameter Method"
http://www.w3schools.com/ado/met_comm_createparameter.asp

And I finally found the list of Type Sizes here:

"Data Type Mapping"
http://www.carlprothman.net/Default.aspx?tabid=97

The example which finally worked, just was a bit more elaborate than I needed was found here:

"Problems using a SQL INSERT command with ADODB"
http://www.tek-tips.com/viewthread.cfm?qid=1042219
Posting by: "fredericofonseca (IS/IT--Management) 25 Apr 05 12:27"

And posting an example of how to issue a SQL UPDATE using the same technology:

Public Function Update() As Boolean
On Error GoTo Err_Update

Dim adoCMD As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strSQL As String
Dim lRecordsAffected As Long

'Define a query to Update a new record into the FE temp table
strSQL = "UPDATE [tmptblqry_partsimportwizard] " & _
"SET [partnumber] = ?, " & _
"[title] = ?, " & _
"[qtyper] = ?, " & _
"[oldqtyper] = ?, " & _
"[addpartrecordflg] = ?, " & _
"[doneflg] = ? " & _
"WHERE [id] = ?;"

'Define attachment to database table specifics
Set adoCMD = New ADODB.Command
With adoCMD
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = strSQL
.Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 25, Me.partnumber)
.Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, 50, Me.title)
.Parameters.Append .CreateParameter("p3", adSmallInt, adParamInput, 2, Me.qtyper)
.Parameters.Append .CreateParameter("p4", adSmallInt, adParamInput, 2, Me.oldqtyper)
.Parameters.Append .CreateParameter("p5", adBoolean, adParamInput, 2, Me.addpartrecordflg)
.Parameters.Append .CreateParameter("p6", adBoolean, adParamInput, 2, Me.doneflg)
.Parameters.Append .CreateParameter("p7", adInteger, adParamInput, 4, Me.id)
Set adoRS = .Execute(lRecordsAffected)
End With

If lRecordsAffected = 0 Then
Update = False
Else
'Return a good return code
Update = True
End If

Exit_Update:
'Clean up the connection to the database
Set adoCMD = Nothing
Set adoRS = Nothing

Exit Function

Err_Update:
Call errorhandler_MsgBox("Class: clsObjPartsImportWizardTbl, Function: Update()")
Update = False
Resume Exit_Update

End FunctionFor getting the Update method to work, I found this posting helpful: (Specifically the warning)

"Using Parameters with an ADO Command"
http://www.xtremevbtalk.com/showthread.php?t=309329#post1337389