Limits on ADODB.Recordset

[JuanPa]

Hi. I'm running a query that basically aims to show the Market Value of a Portfolio Inventory (T for Transactions) based in Historical Prices (P for Prices) on a daily basis:

[FONT=Courier New]
    [/FONT][FONT=Courier New][COLOR=Navy]Dim [/COLOR][/FONT][FONT=Courier New]myConnection [/FONT][FONT=Courier New][COLOR=Navy]As [/COLOR][/FONT][FONT=Courier New]ADODB.Connection
    [/FONT][FONT=Courier New][COLOR=Navy]Set [/COLOR][/FONT][FONT=Courier New]myConnection  = [/FONT][FONT=Courier New][COLOR=Navy]New [/COLOR][/FONT][FONT=Courier New]ADODB.Connection
    myConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=" & ThisWorkbook.FullName & ";" & _
                      "Extended Properties=""Excel 8.0;" & _
                         "HDR=YES"";"
    [/FONT][FONT=Courier New][COLOR=Navy]Dim [/COLOR][/FONT][FONT=Courier New]myQuery [/FONT][FONT=Courier New][COLOR=Navy]As [/COLOR][/FONT][FONT=Courier New]ADODB.Recordset
    [/FONT][FONT=Courier New][COLOR=Navy]Set [/COLOR][/FONT][FONT=Courier New]myQuery = [/FONT][FONT=Courier New][COLOR=Navy]New [/COLOR][/FONT][FONT=Courier New]ADODB.Recordset
    [/FONT][FONT=Courier New][COLOR=Navy]Dim [/COLOR][/FONT][FONT=Courier New]Instruction [/FONT][FONT=Courier New][COLOR=Navy]As [/COLOR][/FONT][FONT=Courier New]String
    Instruction = "SELECT [P$].[Date], " & _
                         "SUM(ROUND([T$].[Shares] * [P$].[AdjustedPrice], 2)) " & _
                  "FROM [T$], [P$] " & _
                  "WHERE [P$].[Ticker] = [T$].[Ticker] AND " & _
                        "[P$].[Date] >= [T$].[Date] AND " & _
                        "[P$].[Date] >= #01/01/2011# AND " & _
                        "[P$].[Date] <= #02/25/2011# " & _
                  "GROUP BY [P$].[Date] " & _
                  "ORDER BY [P$].[Date] "
    myQuery.Open myInstruction, myConnection, adOpenStatic, adLockOptimistic
    [/FONT][FONT=Courier New][COLOR=Navy]Dim [/COLOR][/FONT][FONT=Courier New]myTable [/FONT][FONT=Courier New][COLOR=Navy]As [/COLOR][/FONT][FONT=Courier New]ListObject
    [/FONT][FONT=Courier New][COLOR=Navy]Set [/COLOR][/FONT][FONT=Courier New]myTable = ThisWorkbook.Worksheets("Perfomance").ListObjects("Portfolio")
    myTable.DataBodyRange.Cells(1, 1).CopyFromRecordset myQuery[/FONT]

The code runs smoothly when there're few records in table P (I'm testing 2011 only), but I need the whole data set, which is several thousands rows.
My problem right now is that it looks that there's a limit for a table rows, because when I use a (second stage test) table P with 144K records, the query runs, but the result is a calculation that crops table P to x records.
What is exactly the limit for this stuff? Is it possible to override it?

Thanks a lot,
JuaPa

 

[xenou]

Try using the ACE provider instead of Jet 4.0 provider. ACE is newer with XL2007 and XL2010, and should be "aware" that Excel now has many more rows available. I'm sure with a little googling you should find the correct connection string for the ACE OLEDB provider.

 

[JuanPa]

Thanks, xenou. It looks like I was still living in year 2003. My new code works like a charm:

[FONT=Courier New][COLOR=Navy]    Dim [/COLOR][/FONT][FONT=Courier New]myConnection [/FONT][FONT=Courier New][COLOR=Navy]As [/COLOR][/FONT][FONT=Courier New]ADODB.Connection
    [/FONT][FONT=Courier New][COLOR=Navy]Set [/COLOR][/FONT][FONT=Courier New]myConnection = [/FONT][FONT=Courier New][COLOR=Navy]New [/COLOR][/FONT][FONT=Courier New]ADODB.Connection
    [/FONT][FONT=Courier New][COLOR=Navy]With [/COLOR][/FONT][FONT=Courier New]myConnection
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & ThisWorkbook.FullName & ";" & _
                            "Extended Properties=""Excel 12.0 Macro;" & _
                            "HDR=YES"";"
        .Open
[/FONT][FONT=Courier New]    [/FONT][FONT=Courier New][COLOR=Navy]End With
[/COLOR][/FONT][FONT=Courier New]    ...[/FONT]

 

[xenou]

Good deal.
I'd remove this inconsistency, even if it works:

    With myConnection
        .Provider = "[COLOR="Green"]Microsoft.Jet.OLEDB.4.0[/COLOR]"
        .ConnectionString = "Provider=[COLOR="Navy"]Microsoft.ACE.OLEDB.12.0[/COLOR];" & _
                            "Data Source=" & ThisWorkbook.FullName & ";" & _
                            "Extended Properties=""Excel 12.0 Macro;" & _
                            "HDR=YES"";"

Try instead:

    With myConnection
        .Provider = "[COLOR="Blue"][COLOR="navy"]Microsoft.ACE.OLEDB.12.0[/COLOR][/COLOR]"
        .ConnectionString = "Provider=[COLOR="navy"]Microsoft.ACE.OLEDB.12.0[/COLOR];" & _
                            "Data Source=" & ThisWorkbook.FullName & ";" & _
                            "Extended Properties=""Excel 12.0 Macro;" & _
                            "HDR=YES"";"

You probably don't even need the .Provider line since you've placed it in the connection string (and this is apparently what it's following anyway). So:

    With myConnection
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & ThisWorkbook.FullName & ";" & _
                            "Extended Properties=""Excel 12.0 Macro;" & _
                            "HDR=YES"";"
        .Open

Cheers,
ξ