Microsoft.ACE.OLEDB.12.0 Not working

[jimbojimbo]

Hi,

I am trying to query some data in an excel sheet using the Micosoft.ACE.OLEDB.12.0 it works fine when there are less than 65536 rows of data, however, when there is more than this it bugs out saying that it cant find the object. Any ideas how to get this to work for large data sets. I am using excel 2010

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
sourceRange As String, TargetRange As Range, HeaderRow As Boolean)
Dim rsData, rsData1, rsData2, rsData3, rsData4, rsData5, rsData6, rsdata7, rsdata8, rsdata9, rsdata10, rsdata11 As ADODB.Recordset
Dim szConnect As String
Dim szSQL, szSQL1, szSQL2, szSQL3, szSQL4, szSQL5, szSQL6, szSQL7, szSQL8, szSQL9, szSQL10, szSQL11 As String
Dim lCount As Long
Dim rscon As ADODB.Connection

szConnect = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & SourceFile & ";" & "Extended Properties=""Excel 12.0;HDR=Yes"";"

szSQL = "SELECT top 5 [Country],[admin1name], sum([TIV ($USD)]) as 'TIV' FROM [" & SourceSheet$ & "$" & sourceRange$ & "] where[Peril] = 'Earthquake' Group by [Country],[admin1name] ORDER BY SUM([TIV ($USD)]) desc ;"
Set rscon = New ADODB.Connection
rscon.Open szConnect
Set rsData = New ADODB.Recordset
rsData.Open szSQL, rscon, adOpenForwardOnly, adLockReadOnly, adCmdText

' Check to make sure we received data and copy the data
If Not rsData.EOF Then

'Add the header cell in each column if the last argument is True
If HeaderRow Then
For lCount = 0 To rsData.Fields.Count - 1
TargetRange.Cells(1, 1 + lCount).Value = _
rsData.Fields(lCount).Name
Next lCount
TargetRange.Cells(6, 1).CopyFromRecordset rsData
Else
TargetRange.Cells(6, 1).CopyFromRecordset rsData
End If


Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If
' Clean up our Recordset object.
rsData.Close
Set rsData = Nothing
rscon.Close
Set rscon = Nothing

[/PHP]

 

[RoryA]

If I remember rightly, this works if you don't specify a range but just use a sheet name?

 

[jimbojimbo]

Thanks for the reply, but that doesnt seem to work either. Have now changed it to the below:

szSQL = "SELECT top 5 [Country],[admin1name], sum([TIV ($USD)]) as 'TIV' FROM [" & SourceSheet & "] where[Peril] = 'Earthquake' Group by [Country],[admin1name] ORDER BY SUM([TIV ($USD)]) desc ;"

The error message i get is "The microsoft access database engine could not find the object 'Test'. Make sure the object exists...."

 

[Mike7952]

Try this, there needs to be a $ sign after sheet name

szSQL = "SELECT top 5 [Country],[admin1name], sum([TIV ($USD)]) as 'TIV' FROM [" & SourceSheet & "$] where[Peril] = 'Earthquake' Group by [Country],[admin1name] ORDER BY SUM([TIV ($USD)]) desc ;"

 

[jimbojimbo]

Thankyou so much, that works perfectly now.