I'm attempting a simple insertion query to an MS Access database with Data from Excel. I'm using a parameterized query with hardcoded values for testing. Running the query directly in Access works, but attempting to execute it via VBA results in a syntax error. The query is currently inserting every field except for the auto-generated ID in the Access table.
Sub Test_SQL()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim result
Dim id As Integer
Dim year As Integer
Dim month As String
Dim combocode As String
Dim credamt As Double
Dim qty As Integer
Dim itemcode As String
Dim sepbundleamt As Double
Dim prodname As String
'*********query variables********************
year = 2017
month = "July"
combocode = "COMBCOD1"
credamt = 420
qty = 21
itemcode = "ITEMCOD1"
sepbundleamt = 12
prodname = "Test Product"
'********open DB connection and execute insertion******
On Error GoTo ErrorHandler
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
With conn
.Provider = "Microsoft.ACE.OLEDB.12.0;"
.ConnectionString = [redacted]
.Open
End With
With cmd
.ActiveConnection = conn
.CommandType = adCmdText
.CommandText = "INSERT INTO SB_DSLR (Year, Month, ComboCode, CreditAmt, Qty, ItemCode, SepBundleAmt, ProductName) VALUES (?,?,?,?,?,?,?,?);"
End With
With cmd.Parameters
.Append cmd.CreateParameter("Year", adInteger, adParamInput, 4, year)
.Append cmd.CreateParameter("Month", adWChar, adParamInput, 10, month)
.Append cmd.CreateParameter("ComboCode", adWChar, adParamInput, 10, combocode)
.Append cmd.CreateParameter("CreditAmt", adDouble, adParamInput, 10, credamt)
.Append cmd.CreateParameter("Qty", adInteger, adParamInput, 10, qty)
.Append cmd.CreateParameter("ItemCode", adWChar, adParamInput, 8, itemcode)
.Append cmd.CreateParameter("SepBundleAmt", adDouble, adParamInput, 10, sepbundleamt)
.Append cmd.CreateParameter("ProductName", adWChar, adParamInput, 100, prodname)
End With
cmd.Execute
conn.Close
Set cmd = Nothing
Exit Sub
ErrorHandler:
MsgBox (Err.Number & ": " & Err.Description)
If conn.State = adStateOpen Then
conn.Close
End If
Set cmd = Nothing
End Sub
?in your sql statement, I think the mystery will be solved. Double check the data types when you enter the values.