Error Using DAO Recordset in Excel

ejc00

New Member
Joined
Jul 23, 2007
Messages
4
I am trying to SELECT information from a sheet in Excel into a DAO Recordset. When I run the code on one computer, it works perfectly fine. When I run the code on another computer with a similar load and all of the same references checked, I get the following error:

Run-time error 3011: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

What is odd is that the computer which errors out recognizes the sheet if I try to activate or select it, but it doesn't seem to like the syntax [Sheet1$], which is required to run the SQL statement. I have also tried 'Sheet1$', which is acceptable. Neither works.

Here is the code:

Sub ProcessRecordset(Destination As Worksheet)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strFile As String
Dim strSQL As String
Dim wsSource, wsDestination As Worksheet

On Error GoTo 0

strFileName = Application.ActiveWorkbook.Name

Set db = OpenDatabase(strFileName, False, False, "Excel 8.0;HDR=Yes;")

strSQL = "TRANSFORM Sum(Volume) As SumOfVolume"
strSQL = strSQL & " SELECT CorpName, Cat"
strSQL = strSQL & " FROM [Sheet1$]"
strSQL = strSQL & " WHERE Prod = 'Product'"
strSQL = strSQL & " GROUP BY CorpName, Cat"
strSQL = strSQL & " ORDER BY CorpName, Cat, ProdDate"
strSQL = strSQL & " PIVOT ProdDate;"

Set rst = db.OpenRecordset(strSQL)

Destination.Range("A8").CopyFromRecordset rst

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Sub


Any help would be greatly appreciated. It is frustrating that it works perfectly fine on one computer and does not work on another computer that is nearly identical (though clearly different in some way). We have checked the MDAC version to both and applied the most recent Microsoft updates to both computers... this does not fix the issue.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Welcome. I don't know why it fails on one machine. If suitable, instead of the [Sheet1$] reference can you give the source data a defined name and use that instead in the SQL? Such as, in the source file add a defined name such as "tblData" that covers the required data range. [Note, not however a dynamic named range such as using COUNTA or INDEX.] HTH, Fazza
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
With the early binding, are the references are set from VBA for DAO?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
PS. Try closing down and rebooting...
 

ejc00

New Member
Joined
Jul 23, 2007
Messages
4
Have tried rebooting. Regarding the references, I have DAO and ADO both set. I have also now tried doing it using both DAO and ADO, and both work on my computer, but neither works on the other computer I am trying to use the code on. It still has a problem with the reference to [Sheet1$]. Any more ideas???
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Have you saved the file on the second computer before running the code? If not, I think that would lead to an error. Untested.

Maybe is not relevant, but looking at the code it could be tightened up a bit. Such as there is "Dim strFile as String" but strFile is not used in the code; strFileName is used but is not dim'd. "Dim wsSource, wsDestination as Worksheet" dims wsSource as a variant rather than "Dim wsSource as Worksheet" and neither seem to be used anyway.
 

ejc00

New Member
Joined
Jul 23, 2007
Messages
4
Thanks for the assistance, and the advice. I was able to resolve the issue by not writing the data to a temporary sheet within my file. My original code was copying data from a source file into the spreadsheet and then writing that copied data into a recordset. I changed the code to write the data from the source file directly to the recordset, and this eliminated the error. I'm glad to have solved the issue, but it is still odd to me that the spreadsheet didn't seem to recognize the temporary sheet that I was creating when using the recordset syntax (esp. since I was able to set it as an active sheet through VBA using standard VBA syntax).

Thanks again.
 

Forum statistics

Threads
1,181,649
Messages
5,931,214
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top