I am trying to set up an SQL query of 2 HUGE excel ranges. My code is in the testing stage so the SQL statement is pretty stripped down. However, I am already getting errors.
The following Code errors on the SQL statement if I make the range to 66K rows, It works fine on 65K. Why? My real range will be like 200K
Private Sub AdoTester()
Dim rs As ADODB.Recordset
Dim strConn As String, strSql As String, wkbName As String
Dim TableA As Range
Dim TableB As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = RTSimSht
Set TableA = ws1.Range("RTSimDataDump").CurrentRegion
Set ws2 = CSVDataSht
Set TableB = ws2.Range("CSVavData").CurrentRegion
wkbName = ThisWorkbook.FullName
strConn = "Provider = Microsoft.Ace.OLEDB.12.0;Data Source=" & wkbName & ";Extended Properties=""Excel 12.0;HDR=Yes"";"
strSql = "SELECT * FROM [RTSimMarketData$H9:J65000];"
Set rs = New ADODB.Recordset
rs.Open strSql, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not rs.EOF Then
With ws1
.Range("P1").CopyFromRecordset rs
End With
Else
MsgBox "no records"
End If
rs.Close
Set rs = Nothing
End Sub
also, i'd like to replace the hard keyed range with the range variable I set as TableA. I also set the sheet to a variable ws1. How can I use those instead of the hard keyed sheet name and range?
Bookmarks