ADO Insert Query

[bs0d]

I've managed to query my MS Access database through Excel using the ADO method.

I'd like to learn how the syntax differs for an INSERT query; specifically when inserting values from a form.

Here is the code I use for a SELECT query:

Set myConnection = New ADODB.Connection
Set myResults = New ADODB.Recordset
FilePath = "L:\Production - Historical\db\prod_history.accdb"

With myConnection
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open "Data Source = """ & FilePath & """"
End With

Dim sSQL As String
   sSQL = "SELECT [a_Field] FROM [a_Table] GROUP BY [a_Field];"

With myResults
    .Source = sSQL
    .ActiveConnection = myConnection
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .Open
End With

varData = myResults.GetRows
Sheets("SHEET_1").myComboBox.List = Application.Transpose(varData)

myResults.Close

And here is the insert query i'd like to execute:

Dim sSQL As String
   sSQL = "INSERT INTO [tblComments] ([WH_IDX], [CommentDate], [Comment], [User]) " _
 & "VALUES (" & WH_KEY & "," & frmAddComment.txtDate.Value & "," & frmAddComment.txtComment.Value & "," & frmAddComment.txtUser.Value & ");"

 

[Jon von der Heyden]

I use the command.execute method.

See here: Execute Method (ADO Command)

You can write your SQL to the CommandText property of of the command object, and then execute it. No recordset involved ...

 

[bs0d]

Here's a shot:

Set myConnection = New ADODB.Connection
FilePath = "L:\Production - Historical\db\prod_history.accdb"

With myConnection
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open "Data Source = """ & FilePath & """"
    .CommandText = "INSERT INTO [tblComments] ([WH_IDX], [CommentDate], [Comment], [User]) VALUES (" & Well_KEY & "," & frmAddComment.txtDate.Value & "," & frmAddComment.txtComment.Value & "," & frmAddComment.txtUser.Value & ");"
    .Execute
End With

If CBool(myConnection.State And adStateOpen) = True Then
    myConnection.Close
    Set myConnection = Nothing
End If

I get an "Argument not optional" error on the .Execute line... What am I missing?

 

[bs0d]

One more try:

Set myConnection = New ADODB.Connection
FilePath = "L:\Production - Historical\db\prod_history.accdb"

Dim sSQL As String
sSQL = "INSERT INTO [tblEngineerComments] ([WH_IDX], [CommentDate], [EngComment], [Engineer]) VALUES (" & Well_KEY & "," & frmAddComment.txtDate.Value & "," & frmAddComment.txtComment.Value & "," & frmAddComment.txtEngineer.Value & ");"

With myConnection
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open "Data Source = """ & FilePath & """"
    .Execute sSQL, adExecuteNoRecords
End With

If CBool(myConnection.State And adStateOpen) = True Then
    myConnection.Close
    Set myConnection = Nothing
End If

I get a "no value given for one or more parameters" error, which still points to the .Execute line. -- Thoughts?

 

[bs0d]

Sorry for the multiple posts, but editing is not allowed after 10 minutes.

I resolved the issue, which (with the new code) was actually an error in the SQL statement. I needed quote around the string values, so the insert query now looks like this:

sSQL = "INSERT INTO tblComments (WH_IDX, CommentDate, [Comment], [User]) VALUES (" & W_KEY & "," & frmAddComment.txtDate.Value & "," & Chr(34) & frmAddComment.txtComment.Value & Chr(34) & "," & Chr(34) & frmAddComment.txtUser.Value & Chr(34) & ");"

 

[Jon von der Heyden]

Sorry I went offline. So I take it all is well and it works? Great! And you did a great job pulling it together. Well done!