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. For the documentation on ADO (ActiveX Data Objects):
   ADO API Reference

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

4. man, i need to start learning this stuff!

5. “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.

   –

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