Import from XLS to SQL through VB

[vbpadawan]

Is there an easy way to import data from a worksheet into a SQL table through VB? Currently I open the table and the worksheet and loop through the worksheet and add a new record for each row. This is too slow and am looking for alternative ways to do this.
Thanks.

 

[BobRodes]

The reason it's slow is because the link between vb and excel is slow. Interprocess communication requires marshaling which takes up a lot of overhead. As such, it's not the best idea to iterate through something in excel line by line via an Excel.Application object declared in VB.

Alternatives:

Put your code in the Excel spreadsheet, call it from the xlApp object.

Variation on that: set up an ActiveX Dll that exposes a method that takes an item of data and puts it into a SQL table. Write an Excel routine that instantiates the class and passes data while calling the method.

Copy the excel data to an array, pass the array to VB, use the array to populate the SQL table.

The point is to realize that calls between the excel environment and the VB environment are expensive and need to be minimized as much as possible.

Bob

 

[Swi]

I assume you are using ADO to update your SQL table. Why not just use the Excel driver to populate an ADO recordset and update your SQL table from that?

Function ReadExcelData(sfile As String, sSheetName As String) As ADODB.Recordset
On Error GoTo errhandler
Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection
conn.Open "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & sfile
rs.Open "SELECT * FROM [" & sSheetName & "]", conn, adOpenStatic, adLockReadOnly, adCmdText
Set ReadExcelData = rs
Set rs = Nothing
Exit Function
errhandler:
MsgBox Err.Description & " " & Err.Source, vbCritical, "Import Error"
Err.Clear
End Function

Swi

 

[BobRodes]

That's probably a faster solution.

Bob