DoCmd.RunSQL is all well and good for simple code, the number of times I've run into problems with unescaped apostrophes and the like was starting to nark a bit. With that in mind I crafted the below, a first attempt at a SQL wrapper for VBA which constructs and executes an arbitrary INSERT INTO query using parameters.
'Fields should be a comma-delimited string Public Sub InsertInto(TableName As String, Fields As String, Values As Variant) Dim qdf As DAO.QueryDef Dim strParams As String Dim i As Long Dim strSQL As String On Error GoTo Err_InsertInto 'Assigns strParams value in format "[p1],[p2],[p3],...,[pN]" For i = 0 To UBound(Values) strParams = strParams & "[p" & i & "]," Next i strParams = Left(strParams, Len(strParams) - 1) 'Strip trailing comma 'Construct SQL strSQL = "INSERT INTO " & TableName & "(" & Fields & ") " & _ "VALUES (" & strParams & ");" Set qdf = CurrentDb.CreateQueryDef("", strSQL) 'Give values to parameters For i = 0 To UBound(Values) qdf.Parameters("[p" & i & "]") = Values(i) Next i qdf.Execute Exit_InsertInto: Set qdf = Nothing Exit Sub Err_InsertInto: Select Case Err.Number Case 13 'Type mismatch i.e. Values is not an array Values = Array(Values) Resume Case Else 'Unhandled Error Set qdf = Nothing 'Release QueryDef object 'Error is unhandled here, force it to 'bubble up' to calling code Dim iErrNum As Long, strErrDesc As String iErrNum = Err.Number strErrDesc = Err.Description On Error GoTo 0 Err.Raise iErrNum, "SQLOps.InsertInto", strErrDesc End Select End Sub
Likely to be called in a manner similar to (this example was used as a successful test case):
InsertInto "tblErrorLog", _ "ErrorNum,Description,User,When,Origin", _ Array(10,"Error's Description",Environ("USERNAME"),Now(),"A Ghost")
So far it seems to be working fine and does indeed alleviate the issues with unescaped apostrophes but I'm wondering whether this can be improved so I could use it as a sort of template for crafting similar functions for other SQL operations e.g. UPDATE SET / SELECT FROM.
Can this code be improved and are there any unforeseen bugs I should watch out for?