Import Data from Excel to SQL Server using Ado

[Dima09]

Hi All!

Have anyone ever import data from Excel to SQL Server using ado?

Here is the code that gives me an error: [OLE/DB Provider returned message: Unspecified error]

Dim lngRecsAff As Long
Dim strSQL As StringSet

Conn = New ADODB.Connection
Conn.Open DbConnection()

strSQL = "Select * INTO Purchaser FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\book.xlsx','SELECT * FROM [Sheet1$]')"

Conn.Execute strSQL, lngRecsAff, adExecuteNoRecords

What is missing here?

 

[SkipVought]

I believe that you need to open a connection to the Excel workbook and a connection to the SQL Server database, assuming that you're doing this from MS Access.

http://www.carlprothman.net/Default.aspx?tabid=81

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWise]

is the excel sheet open when you run this command?

 

[Dima09]

I have a run time error for creating duplicate values. How do I have the ADO reveiw each record and if the ID already exists, continue or skip to add the others? Here is the code:

DoCmd.TransferSpreadsheet acLink, 8, "lnkImport", varFileName, True, "Sheet1!a1:g1000"

Set Conn = New ADODB.Connection
Conn.Open DbConnection()

strSQL = "INSERT INTO Purchaser ( LastName, PurchaserID ) SELECT Patient, Patient FROM lnkImport WHERE DOB Is Not Null"

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic