VBA Excel data to SQL Sever table

[DennisB]

I want to automate sending Excel source data into a SQL Server database.
Here is the code that I wrote but I'm wondering if there is an easier way. I
have an ADO connection to SQL Server with a database called dbbudget.

Sub testCall()
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Dim sFile As String

'==============================================
'this procedure is to load data from an Excel
'file into an SQL Server table
'this results in an ODBC call failed message
'==============================================
sFile = "D:\dbrininger\1MyWork\Projects\Development\FXS
Work\Transactions\YTD_Invoices.xls"
Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=Excel 8.0"

'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=172.28.20.204;Database=dbbudget;" & _
"UID=dbrininger;PWD=test123].ImportTest " & _
"FROM [Invoices$]"

cn.Execute strSQL
cn.Close
Set cn = Nothing
End Sub

 

[Rob van Gelder]

Are you saying this works? If so, I have never seen SELECT INTO used in this way.

A typical insert statment:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)


The pseudocode I use for moving excel rows into a database is as follows:

open excel file
open database connection

for each excel column, create a database parameter

begin transaction
for each row in excel
for each excel column, copy the value to the applicable parameter
execute insert statement (or stored procedure), on error break out of for-next loop
next
if error, then rollback transaction, else ask to commit transaction

close database connection
close excel file

Cheers,
Rob