Exporting Excel range to SQL

[The_Kurgan]

I've written code to upload data into SQL from Excel, but it is row-by-row iterative and takes forever when there are many rows, so I started looking for a way to do a range in one pass. The code below was found from a very old post on another site and it looked like something I could manage. After some massaging, I thought I had it, but I'm getting an "Invalid object name 'TempRange'" error on the .Execute line. Any thoughts on what I'm doing wrong with this range?

Sub UpdateTable3()
    Dim cnn As Object
    Dim wbkOpen As Workbook
    Dim objfl As Variant
    Dim rngName As Range
    Workbooks.Open "X:\MyPath\MyExcelFile.xlsm"
    Set wbkOpen = ActiveWorkbook
    Sheets("Data").Select
    Set rngName = Range("A2:E10")  'range defined here
    rngName.Name = "TempRange"   'TempRange defined here
    strFileName = wbkOpen.FullName
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=MYSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SECONDARYTS;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=MYDB"
    cnn.Open
    nSQL = "INSERT INTO Workarea.[ad hoc]"
    nJOIN = " SELECT * from [TempRange]"  'TempRange used in SQL statement
    cnn.Execute nSQL & nJOIN  '<----- ERROR WHILE EXECUTING THIS LINE
    MsgBox "Uploaded Successfully"
    wbkOpen.Close
    Set wbkOpen = Nothing
End Sub

 

[Alex Blakenburg]

Have you tried using the Named Range without the square brackets ?

nJOIN = " SELECT * from TempRange"

 

[The_Kurgan]

Have you tried using the Named Range without the square brackets ?

nJOIN = " SELECT * from TempRange"

Thanks for the reply. I had not, but just now tried it yielding the same result.

 

[Alex Blakenburg]

I consulted my Brains Trust on this one and as I understand it you can't have 2 connection strings for the one SQL statement, and the select statement needs something like XLSM connection shown below.
The connection string in the code will only work for the Insert component, and is looking at your SQL DB for the TempRange in the Select statement
I'm afraid I have not done enough work in this area so hopefully someone else can help you out.

XLSM version from Excel connection strings - ConnectionStrings.com

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;Extended Properties="Excel 12.0 Macro;HDR=YES";

I also added these to your code when I was trying to figure it out:

    Dim strFileName As String
    Dim nSQL As String
    Dim nJoin As String

 

[The_Kurgan]

Thanks Alex. Apologies for the late reply, as I've had to wrestle with IT to set AdHocRemoteQueriesEnabled to TRUE.

Not sure I fully understand. It looks as thought this is the only connection string I used:

cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=MYSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SECONDARYTS;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=MYDB"

Although... I have whittled the code to the following:

Sub UpdateTable3_17_21()
    Dim cnn As Object
    Dim wbkOpen As Workbook
    Dim objfl As Variant
    Dim rngName As Range
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=MyServer;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SECONDARYTS;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=MyDB"
    cnn.Open
    cnn.Execute "Insert into Workarea.[ad shoc] Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=X:\MyPath\MyExcelFile.xlsm', [Data$])"
End Sub

But now on the Execute line, I'm now getting this error:

The OLE DB provider "Microsoft.ACE.OLEDB.12.0'' has not been registered"

Any more thoughts from the brain trust about what I'm missing?

 

[Alex Blakenburg]

Sadly as I understand it, its just not as simple as you are hoping for.
You are hoping to do it in a single SQL statement but that statement can only make one connection at a time and you need two.
You need a connection to Excel for the read / select and another to SQL server / the database for the Insert Into.

I have done some googling myself and the options seem to be to (for the Excel read) either read through the table record by record and cell by cell or read it into the a recordset and follow a similar process.

If your Excel columns match the Database table exactly it requires less code in that you can loop through the fields but most of the code I have seen have an assignment line for each field being exported.

This 6 min video is one of the simpler ones I have seen
It still seems to indicate that explicitly mapping the load field by field is more reliable.