3

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.

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
brainac
  • 400
  • 3
  • 12
  • Is the named range dynamic or static? Dynamic won't work with ADO. – Rory Jan 03 '19 at 11:37
  • what is the scope of the name - worksheet or workbook? – buran Jan 03 '19 at 11:38
  • @buran I wanted to name specific range in the worksheet on the fly, then refer it in query, and do it in loop as it is multiple small tables in one sheet. – brainac Jan 03 '19 at 11:43
  • @brainac, when you create a name it's either workbook-level name or woksheet-level name (i.e. it has different scope) and is referred to differently. That is why I ask what is the scope of the name – buran Jan 03 '19 at 11:50
  • @buran I didn't know there are two scopes. Currently it's Workbook scope, but I can change it if necessary. – brainac Jan 03 '19 at 11:55
  • @actually it's not clear - do you run the code in Access or in Excel – buran Jan 03 '19 at 12:14
  • 1
    Years and years ago, Excel was limited to 255 or 256 columns. I *suspect* that trying to connect to Excel may be hitting this limitation: the provider may not be designed to request/see more than that number of columns and/or Excel isn't designed to offer beyond that number of columns. It could also be related to the limitation of 255 fields in an Access table. – Cindy Meister Jan 03 '19 at 12:18
  • @buran Not sure if it matters for ADODB Connection, but it's in Excel. – brainac Jan 03 '19 at 12:18
  • @buran It doesn't matter, really, whether this is running in Excel or Access. I tried accessing a Range starting at address JA:1 via a Word macro and get the same message. The problem is in the provider and/or Excel, not with where the code is executing. – Cindy Meister Jan 03 '19 at 12:20
  • 1
    Using a named range should solve the column number issue (assuming the named range isn't wider than 255 columns itself, and the source workbook is closed). I think we are missing relevant code as to how/when these names are created. – Rory Jan 03 '19 at 12:25
  • My question where it runs from is related to the connection string that was used. – buran Jan 03 '19 at 12:27
  • @Rory I tested with a named Range starting at JA:1, extending across three columns and three rows. It didn't work with the Workbook *open* (other named ranges do), but does with the workbook *closed*. That seems to be key - very good! – Cindy Meister Jan 03 '19 at 12:40
  • Did you see Rory's comment and my response to it? It does work if the workbook is closed. – Cindy Meister Jan 03 '19 at 17:01
  • @CindyMeister, I haven't tested it, as my workbook has to be open, but certainly an interesting finding! Another thing is how stupid it is that it needs closed workbook, but well, not the first time with Excel and VBA. So you say that my first try was a good shot, i.e. [Excel 12.0 Macro;HDR=Yes;Database=C:\MyPath\MyFile.xlsb].[myRange]? – brainac Jan 04 '19 at 10:59
  • Most likely having the workbook open runs into the limitations I mentioned that may be inherent in the Excel application. // I use a different connection approach: 1) IMEX=1 in the connection string and 2) separate connection and sql, using connection.execute to perform the sql action (example: http://www.vb-helper.com/howto_ado_insert.html). I've never specifying the recordset as part of the provider string. – Cindy Meister Jan 06 '19 at 18:05

3 Answers3

1

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
Ryan Wildry
  • 5,612
  • 1
  • 15
  • 35
  • Interesting solution. As I wrote earlier, I have a workaround for this issue, but I found it interesting enough to ask for help here. – brainac Jan 03 '19 at 15:47
1

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)

SteBar
  • 31
  • 1
  • I finished the project long time ago, but I like your input. I didn't test it, but it should work at the first glance. I would to refer the database through square brackets. – brainac Jul 21 '21 at 09:49
0

You need to use a construction:

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