Bulk Uploads using ADO Command Parameters

I’ve been doing some stuff with SQL Server just recently – first time ever. Its been a bit of a shock really. I’m strictly an Oracle guy so I’ve been careful to keep an open mind. It took me a long time to work out how to create an outer join!
My task has been to pull data from SQL Server, manipulate it then send it on to an Oracle database. Excel fits nicely between.

Here’s a little sample I’ve written for MS Access to demonstrate my approach.
In Access I’ve got a two column table called Employee. Columns EmployeeID and EmployeeName.
My Excel Worksheet has two columns EmployeeID and EmployeeName with a column Header in Row 1.

Be sure to read the comments to this post – I’ll write the reasons behind my approach and code.

‘ This requires a Reference to Microsoft ActiveX Data Objects 2.x Library
Const cConnection = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
           “Data Source=C:Documents and SettingsOwnerMy Documentsdb1.mdb;”
Const cSQL = “insert into employee (employeeid, employeename) values (?, ?);”
Enum EmployeeTableColumns
    cEmp_EmployeeID = 1
End Enum
Const cEmp_FirstRow = 2
Sub upload_employee()
    Dim i As Long, lngLastRow As Long, blnCommit As Boolean
    Dim con As ADODB.Connection, cmd As ADODB.Command
    On Error GoTo e1
    Set con = New ADODB.Connection
    con.Open cConnection        ‘Open connection to the database

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = con  ‘Set up our command object for exceuting SQL statement
   cmd.CommandText = cSQL
    cmd.CommandType = adCmdText
    cmd.Parameters.Append cmd.CreateParameter(“iEmployeeID”, adNumeric, adParamInput)
    cmd.Parameters.Append cmd.CreateParameter(“iEmployeeName”, adVarChar, adParamInput, 50)
    On Error GoTo e2
    With ActiveSheet
        lngLastRow = .Cells(Rows.Count, cEmp_EmployeeID).End(xlUp).Row
        For i = cEmp_FirstRow To lngLastRow
‘            Application.StatusBar = Format((i – cEmp_FirstRow) / (lngLastRow – cEmp_FirstRow), “0.0%”)
‘            DoEvents
           cmd(“iEmployeeID”).Value = .Cells(i, cEmp_EmployeeID).Value
            cmd(“iEmployeeName”).Value = .Cells(i, cEmp_EmployeeName).Value
            cmd.Execute Options:=adExecuteNoRecords
    End With
e2: ‘Application.StatusBar = False

    If Err.Number Then
        MsgBox Err.Description, vbCritical, “Error”
        blnCommit = False
        blnCommit = MsgBox(“Success, Commit?”, vbQuestion + vbOKCancel) = vbOK
    End If
    On Error GoTo e1
    If blnCommit Then con.CommitTrans Else con.RollbackTrans
e1: If Err.Number Then
        MsgBox Err.Description, vbCritical, “Error”
    End If
    Set cmd = Nothing
    If Not con Is Nothing Then
        If Not con.State = adStateClosed Then con.Close
        Set con = Nothing
    End If
End Sub
Posted in Uncategorized

6 thoughts on “Bulk Uploads using ADO Command Parameters

  1. Hi!

    I’m using Parameters for a series of reasons:
    – No need to concatenate the values into SQL string. eg. “insert into … ” & myval & “, ” & myotherval & “…
    – Since I’m not concatenating, I don’t need to convert special characters like single and double quotes into escaped pairs
    – My SQL string can live as a Const at the top of my code – nice and visible
    – Some databases can cache pre-processed query plans so it take less time to run
    – Avoid SQL injection attack (ok, maybe not from Excel)

    You might also notice the use of:
    Enum EmployeeTableColumns
    cEmp_EmployeeID = 1
    End Enum
    Const cEmp_FirstRow = 2

    If I ever want to change the position of the Excel columns it’s just a matter of switching the order within the Enum.
    I tried function lookup based on column header text and others approaches but this is the one I’ve settled on – It’s XL2000+

    cEmp_FirstRow allows me to increase the header rows without too much trouble.

    With my Oracle app, I used adCmdStoredProc instead of adCmdText and wrote a packaged stored procedure.
    I don’t know how to write Stored Procedures in Access.

    Options:=adExecuteNoRecords is a performance boost when you know that no records are going to be returned.

    I commented out the Application.StatusBar bit. It’s a progress meter, but if you want speed then you may not want it running.

    I’d be open to suggestions for improving my error handling.
    I truly believe I’m horrible at it and seek your advise.


  2. The error handling should be structured as follows, where the bits in square brackets are your code:

    Sub upload_employee()

    Dim bCommit As Boolean

    On Error Goto ErrConnectionError

    [Set up connections/commands]

    On Error Goto ErrDataError

    [For..Next loop to populate data]

    bCommit = MsgBox(…


    If bCommit Then …


    [close the command, connection etc.]

    Exit Sub

    [Optionally send error report to central error log routine]
    Resume CommitRollback

    [Optionally send error report to central error log routine]
    Resume CloseConnections

    End Sub

    For (much) more information, see Chapter 12 – VBA Error Handling in Professional Excel Development, http://www.oaltd.co.uk/ProExcelDev.

  3. “I donít know how to write Stored Procedures in Access.”

    It’s fairly similar syntax to SQL Server but MS Access/Jet can only execute one statement. So, at the simplest level:

    Sub TestProcs()
    Dim con As Object
    Set con = CreateObject(“ADODB.Connection”)
    With con

    Dim i As Long
    For i = 0 To 1

    .ConnectionString = Array( _
    “Provider=SQLOLEDB.1;Data Source=(local);” & _
    “Initial Catalog=Northwind;” & _
    “User Id=sa;Password=;”, _
    “Provider=Microsoft.Jet.OLEDB.4.0;” & _
    “Data Source=C:Tempo
    .Execute _
    “CREATE PROCEDURE Proc1 (” & _
    ” @LastName VARCHAR(20),” & _
    ” @FirstName VARCHAR(10))” & _
    ” AS INSERT INTO Employees (LastName, FirstName)” & _
    ” VALUES (@LastName , @FirstName);”
    .Execute _
    “Proc1 ‘Delete’, ‘Me’;”
    .Execute _
    “Delete FROM Employees WHERE” & _
    ” LastName=’Delete’ AND FirstName=’Me’;”

    End With
    End Sub



  4. And once you have a PROC, you can use ADOX to create your Command object, with parameters, defaults, etc, for you:

    Sub TestCmd()
    Dim Cat As Object
    Set Cat = CreateObject(“ADOX.Catalog”)
    With Cat
    .ActiveConnection = _
    “Provider=Microsoft.Jet.OLEDB.4.0;” & _
    “Data Source=C:Tempo
    Dim Cmd As Object
    Set Cmd = .Procedures(“Proc1?).Command
    End With
    With Cmd
    .Parameters(“LastName”).Value = “Delete”
    .Parameters(“FirstName”).Value = “Me”
    .ActiveConnection.Execute _
    “Delete FROM Employees WHERE” & _
    ” LastName=’Delete’ AND FirstName=’Me’;”
    End With
    End Sub



Leave a Reply

Your email address will not be published. Required fields are marked *