VBA – Using Parameters in a VBA SQL Query To a Database

In this article I will simply use the code from my first guest author Mathieu, to build up some small working solution, that uses parameters:

So, the two classes, provided by Mathieu are here as well  GitHub, named AdoValueConventer.cls and SqlCommand.cls. These are used to make the following query from the code below:

"INSERT INTO Invoices ([InvoiceDate],[AccountType],[CompanyName]) " & "VALUES (? , ? , ?)"

So, what is the idea of the code?

It creates5 entries to a database, consisting of 4 columns – ID, Invoice Date, AccountType and CompanyName. ID is autoincrementable Primary Key, so it is automatically generated. The sample data is generated through a loop:

Public Sub Main()
    
    Dim invoiceCollection As New Collection
    Dim i As Long
    Dim Account As Account
    
    For i = 1 To 5
        Set Account = New Account
        Account.CompanyName = "Company " & i
        Account.TypeOfAccount = "Type " & i * 3
        invoiceCollection.Add Account
    Next
        
    AddToDatabase invoiceCollection

End Sub

The Account class, is the one which is responsible to hold the data. The invoiceCollection consists of Account Objects:

Option Explicit

Private m_sTypeOfAccount As String
Private m_sCompanyName As String


Public Property Get TypeOfAccount() As String

    TypeOfAccount = m_sTypeOfAccount

End Property

Public Property Get CompanyName() As String

    CompanyName = m_sCompanyName

End Property

Public Property Let CompanyName(ByVal sNewValue As String)

    m_sCompanyName = sNewValue

End Property

Public Property Let TypeOfAccount(ByVal sNewValue As String)

    m_sTypeOfAccount = sNewValue

End Property

Public Property Get InvoiceDate() As Date

    InvoiceDate = Now()

End Property

Once the data is generated, we pass the invoiceCollection to a database, using the two classes (referred in GitHub):

Public Sub AddToDatabase(invoiceCollection As Collection)
    
    Dim conString As String
    Dim con As New ADODB.connection
    Dim rs As New ADODB.Recordset
    Dim sql As String
    
    conString = "Provider=SQLNCLI11;Server=(localdb)\MSSQLLocalDB;Initial Catalog=InvoiceAutomation;Trusted_Connection=yes;timeout=30;"
    
    con.Open conString
    con.CommandTimeout = 60
    
    Dim Account As Account
    Dim i As Long
    For i = 1 To invoiceCollection.Count
        Set Account = invoiceCollection(i)
        sql = "INSERT INTO Invoices ([InvoiceDate],[AccountType],[CompanyName]) " & "VALUES (? , ? , ?)"
        
        With New SqlCommand
            Set rs = .Execute(con, sql, Account.InvoiceDate, Account.TypeOfAccount, Account.CompanyName)
        End With                
    Next i    
    con.Close
    
End Sub

And this is how it sends 5 sql transactions to our LocalDb.