ADO Insert Query with Parameter(s)

[bs0d]

I've built an ADO function in VBA to handle queries to an Access database. It's setup to process queries with 0 - 2 parameters.

I'm trying to expand it to handle an Insert as well. When the Insert SQL and parameter are passed to the function, Excel locks up.

Where am I going wrong with handling the INSERT query? Also included is the SQL statement I'm trying to execute. It doesn't have the VALUES part - which isn't necessary in Access? If I have an error in my SQL statement, how might I alter it to work with ADO?

Thanks!

ADO Function:

Public Function adoQuery(sSQL, Optional ParamValue As Variant, Optional ParamValue2 As Variant, Optional Insert As Boolean)

Set myConnection = New ADODB.Connection
Set myResults = New ADODB.Recordset
Dim FilePath As String
    FilePath = "L:\my_db.accdb"

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


If IsMissing(ParamValue) = False Then

'Query with parameter(s):
    Dim cmd As ADODB.Command
    Set cmd = CreateObject("ADODB.Command")
    Dim param As ADODB.Parameter

    cmd.ActiveConnection = myConnection
    cmd.CommandText = sSQL

    Select Case VarType(ParamValue)
        Case Is <= 6
            paramType = adInteger 'number
            paramSize = adInteger
        Case Is = 7
            paramType = adDate  'date
            paramSize = adDBDate
        Case Else
            paramType = adVarChar  'text
            paramSize = adChar
    End Select
    
    Set param = cmd.CreateParameter("param1", paramType, adParamInput, paramSize, ParamValue)
        
        cmd.Parameters.Append param
            Set param = Nothing
            
    If IsMissing(ParamValue2) = False Then   '(2) parameters passed to function:
        Dim param2 As ADODB.Parameter
    
        Select Case VarType(ParamValue2)
            Case Is <= 6
                paramType = adInteger 'number
                paramSize = adInteger
            Case Is = 7
                paramType = adDate  'date
                paramSize = adDBDate
            Case Else
                paramType = adVarChar  'text
                paramSize = adChar
        End Select
        
        Set param2 = cmd.CreateParameter("param2", paramType, adParamInput, paramSize, ParamValue2)
            
            cmd.Parameters.Append param2
                Set param2 = Nothing
     End If

    'IF INSERT, execute differently:
    If Insert = True Then

        'Proceed with INSERT:
        With cmd
           .Execute sSQL, , adCmdText + adExecuteNoRecords
        End With
    Else
        'Ready to go
        Set myResults = cmd.Execute()
    End If
    
    
Else

'Query without parameter:
    With myResults
        .Source = sSQL: .ActiveConnection = myConnection: .CursorLocation = adUseClient: .CursorType = adOpenForwardOnly: .LockType = adLockReadOnly: .Open
    End With

End If

End Function

Function call:

adoQuery, sSQL, myParamValue1, , True

SQL to execute (includes variables from program):

        sSQL = "INSERT INTO tblTempGroupNorm ( Username, Tab_Name, Click_Date, Criteria1, Item_KEY, ReadingDate, Prod1, Prod2, Prod3, TimeDay ) " _
            & "SELECT " & Chr(34) & strname & Chr(34) & " AS Username," _
            & Chr(34) & "Group" & Chr(34) & " AS Tab_Name," _
            & strDate & " AS Click_Date," _
            & Chr(34) & Group & Chr(34) & " AS Criteria1, " _
            & "[Group_Daily].Item_KEY, [Group_Daily].ReadingDate, [Group_Daily].Prod1, [Group_Daily].Prod2, [Group_Daily].Prod3, " _
                & "(SELECT COUNT(Table1A.ReadingDate) " _
                & "FROM [Group_Daily] AS Table1A " _
                & "WHERE [Table1A].[ReadingDate] <= [Group_Daily].[ReadingDate] " _
                & "AND [Table1A].[Item_KEY]=[Group_Daily].[Item_KEY]) AS TimeDay " _
                & "FROM tblProperties INNER JOIN [Group_Daily] ON tblProperties.WH_IDX = [Group_Daily].Item_KEY " _
                & "WHERE ((tblProperties." & db_field & ") = '" & Group & "')" _
                & "ORDER BY [Group_Daily].ReadingDate;"

 

[bs0d]

Got it to work; it was the query slowing things down and locking up Excel.

Still could use some feedback on any improvements or efficiencies to adoQuery function