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.

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
    cEmp_EmployeeName
    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.

    Cheers,
    Rob

  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(…

    CommitRollback:

    If bCommit Then …

    CloseConnections:

    [close the command, connection etc.]

    Exit Sub

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

    ErrConnectionError:
    [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
    wnd4.mdb”)(i)
    .Open
    .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’;”
    .Close
    Next

    End With
    End Sub

    Jamie.

    –

  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
    wnd4.mdb”
    Dim Cmd As Object
    Set Cmd = .Procedures(“Proc1?).Command
    End With
    With Cmd
    .Parameters(“LastName”).Value = “Delete”
    .Parameters(“FirstName”).Value = “Me”
    .Execute
    .ActiveConnection.Execute _
    “Delete FROM Employees WHERE” & _
    ” LastName=’Delete’ AND FirstName=’Me’;”
    End With
    End Sub

    Jamie.

    –


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

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