VBA Excel data to SQL Sever table

D

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top