CODEWITHSUNDEEP
excelvbams-accessadodb
brainac
brainac

Reputation: 410

How to connect to Excel named range in ADODB SQL query

I need to move data from Excel sheet to database. To do this, I create ADODB Connection and I am able to execute such an SQL query: INSERT INTO myTable SELECT * FROM [Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].[Shee1$A1:C100]

My problem is that the range cannot point further than 255 columns, i.e. column IU. I want to try using named range instead, but I cannot find suitable notation. All examples I found connect directly to the workbook, and use either SELECT * FROM [Sheet1$] reference, or SELECT * FROM myRange as an example of named range. I tried things like 

[Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].[myRange]
[Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].[myRange$]
[Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].myRange
[Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb;Name=myRange]

, but without success.

What is the proper way to use named range here? Would it even help working around column number limitation?

I expected [Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].[myRange] to work, but it throws the following error: 'The Microsoft Access database engine could not find the object 'myRange'. Make sure the object exists (...)'

I can work it around by copying data from source sheet to temporary one, and have it within 255 column limit, but it would be great do it right way.

Upvotes: 3

Views: 4278

Answers (4)

Louis Lietaer
Louis Lietaer

Reputation: 31

I found this link sql on range

I have tested most of the examples without any issues. I will add a new one working also a sheet named range

strQuery = “SELECT * FROM [Sheet1$mynamedrange]“

Upvotes: 0

SteBar
SteBar

Reputation: 31

I had the same issue and the solution is pretty easy, even though it works only for named ranges at Workbook level. The connection has to be done to the Workbook (i.e. [Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb]). Then in the Query just type: SELECT * FROM [myRange] (Please note the square brackets and the absence of $ sign)

Upvotes: 1

Dmitry Bryantsev
Dmitry Bryantsev

Reputation: 1

You need to use a construction:

[Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].[$myRange]

Upvotes: 0

Ryan Wildry
Ryan Wildry

Reputation: 5677

Not sure if you are going to find a solution for connecting to a named range. I took a look at getting this to work, and I had no luck either, I suspect it's not included in the schema past 255 column, but could be wrong.

I thought you might as well have an efficient solution that doesn't rely on looping for adding data to Access. It's more code than just doing an insert, but I hope it fits your specific problem.

I was able to do an insert of ~2500 records (all integers) in about 3 seconds, so it is fairly quick.

Option Explicit

Private Function GetDisconnectedRecordset(TableName As String) As ADODB.Recordset
    Dim conn As ADODB.connection: Set conn = getConn()
    Dim rs   As ADODB.Recordset: Set rs = New ADODB.Recordset

    With rs
        .CursorLocation = adUseClient ' <-- needed for offline processing
        'Get the schema of the table, don't return anything
        .Open "Select * from " & TableName & " where false", conn, adOpenDynamic, adLockBatchOptimistic
    End With

    rs.ActiveConnection = Nothing
    conn.Close
    Set conn = Nothing
    Set GetDisconnectedRecordset = rs
End Function

'Do an update batch of the data
'Portion used from: https://stackoverflow.com/questions/32821618/insert-full-ado-recordset-into-existing-access-table-without-loop
Sub PopulateDataFromNamedRange()
    Dim conn        As ADODB.connection
    Dim ws          As Excel.Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet2") 'Update to your sheet/wb
    Dim NamedRange  As Excel.Range: Set NamedRange = ws.Range("Test") ' Update to your named range
    Dim NamedItem   As Excel.Range
    Dim rs          As ADODB.Recordset: Set rs = GetDisconnectedRecordset("[TestTable]") 'Specify your table name in access
    Dim FieldName   As String
    Dim Row         As Long
    Dim AddRow      As Long

    'Add Data to the disconnected recordset
    For Each NamedItem In NamedRange
        If Not NamedItem.Row = 1 Then
            Row = NamedItem.Row
            If Not Row = AddRow Then rs.AddNew
            AddRow = NamedItem.Row
            FieldName = ws.Cells(NamedItem.Row - (NamedItem.Row - 1), NamedItem.Column).Value
            rs.Fields(FieldName).Value = NamedItem.Value
        End If
    Next

    'Connect again
    Set conn = getConn()
    Set rs.ActiveConnection = conn

    rs.UpdateBatch '<-- 'Update all records at once to Access
    conn.Close
End Sub

Private Function getConn() As ADODB.connection
    Dim conn As ADODB.connection: Set conn = New ADODB.connection
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ryan\Desktop\Example.accdb"
    Set getConn = conn
End Function

Upvotes: 1

Related Questions