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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
' 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 cEmp_EmployeeName 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) con.BeginTrans 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 Next End With e2: 'Application.StatusBar = False If Err.Number Then MsgBox Err.Description, vbCritical, "Error" Err.Clear blnCommit = False Else 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" Err.Clear 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 |
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
For the documentation on ADO (ActiveX Data Objects):
ADO API Reference
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.
man, i need to start learning this stuff!
“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.
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.