Excel 2007- ADO - opening recordset using [Sheet1$] cannot not find records on worksheet

Posted on 2010-08-30
Last Modified: 2013-11-26
This problem is made more intriguing by the fact that it works on the local machine but not on the Windows server where the excel workbook is deployed.

Fist of all I dump the contents of a disconnected ADO recordset (has no connection) onto an excel worksheet [Sheet1$] using the CopyFromRecordset method of the range object.   At this point I also write out my Field Headings.  The results are displayed as expected.

After this I preform an Application.Workbooks.RefreshAll

Now I create a connection to the excel workbook and try to open a new ADO recordset object using "SELECT * FROM [Sheet1$]" (simplified version of a complicated JOIN query - but this simple SQL does not work either).  When I try and dump the results of this recordset onto a new worksheet using CopyFromRecordset method...all that is produced is one row with F1 in the upper left cell of the workbook.  As if it cannot see the data that has just been produced on Sheet1 worksheet.

As I say it works every time on local machine but not on server.  I have tried nearly all the refresh methods of excel, refresh.all, cells.calculate etc.

The only way I have found that works is by using the Save method. HOWEVER I do not want to use this method as it is a big hit on performance.

If anyone can offer some assistance I would be grateful. Thanks.
Question by:gerry_ocallaghan
  • 5
  • 4
LVL 85

Expert Comment

by:Rory Archibald
ID: 33564735
You will have to save it. As a matter of interest, is there a reason you can't simply write the data straight from source to the worksheet rather than using an intermediate recordset (if that's all it's for)?

Author Comment

ID: 33564934
Hi rorya,
The data comes from a locked down sybase backend.  We don't have full access. We basically have four views of non-related data.  Thats why I connect to sybase backend, load the data into a disconnected ADO recordset in excel.  Now I can perform complex SQL on this data, and even join to other worksheets populated from other sources.  There are other reasons but I won't bore you with them but I cannot avoid the disconnected ADO recordset approach.  

I really don't want to use Save method as it takes up to 1 min to complete.   There must be a way to refresh the workbook, so after I populate worksheet A with the results from first recordset, I can then create a new recordset based on worksheet A.

Don't forget this works locally so there must be an alternative fix.

Thanks for your comment though.
LVL 85

Expert Comment

by:Rory Archibald
ID: 33565033
What I mean is, why can't you do a
"SELECT * INTO [Excel 8.0;DATABASE=c:\temp\data copy.xls].[Sheet1] FROM [sybase view]"
type command? That will actually create a file directly without the need for the intermediate recordset and because it bypasses Excel, should be faster. It also means you can then query a closed workbook, thereby avoiding the memory leaks inherent in querying open workbooks.
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.


Author Comment

ID: 33574323
Thanks for suggesting new approach but I have to stick with the original design.
Unless some other expert can offer an alternative way of refreshing the workbook I will go with the Save method.


Author Comment

ID: 33596649
Could you please have another look at this. More details below...
I include some of your own code (i found under another topic) at the end.

I have a workbook that exists and should always have empty worksheets at startup. (a reporting tool)

At run time they're are no records in my [Sheet1$], I populate this sheet on the fly from a disconnected ADO recordset.  I dump the results onto [Sheet1$] with a copyfromrecordset method. Records are visible.  After [Sheet1$] is populated I establish a connection to this activeworkbook using identical code to yours below and everything works as expected.

However if I mark workbook as Shared (under Review>>Share Workbook), and execute exactly the same code I can create the disconnected recordset, dump them onto [Sheet1$]  BUT then SELECT FROM [Sheet1$] cannot see any records.

The only way I can resolve this is by executing the Save method.  However I can't really do this cause this is a shared workbook and I don't want the results to be saved when closing the workbook.  

I would seriously appreciate any help you could provide. Many thanks.

---Rorya's code from other post
   ' Sample demonstrating how to return a recordset from a workbook
   Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String
   Dim varData As Variant
   Set cn = New ADODB.Connection
'   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";" & _
'         "Extended Properties=""Excel 8.0;HDR=Yes;"""
   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
         "Extended Properties=""Excel 8.0;HDR=Yes;"""
   End With
   strQuery = "SELECT * FROM [Sheet1$];"
   Set rst = New ADODB.Recordset
   rst.Open strQuery, strConn, adOpenStatic, adLockReadOnly, adCmdText
   varData = rst.GetRows
   Set rst = Nothing
'   cn.Close
   Set cn = Nothing
End Sub
LVL 85

Expert Comment

by:Rory Archibald
ID: 33596694
1. Don't use Shared Workbooks. Ever. They are unreliable and prone to corruption. They also prevent you from doing what you want to do here!
2. If I didn't mention it in that other post, (I did earlier in this one) it is unadvisable to query open workbooks, since it leads to memory leaks.

Author Comment

ID: 33597164
I don't have an option on the shared workbooks.  Legacy solution.
Thanks for heads up on memory leaks.

I read somewhere that the MS Jet Provider has this isssue with ADO.  I don't suppose you know of any alternative providers I could use in the connection string.

LVL 85

Accepted Solution

Rory Archibald earned 500 total points
ID: 33597981
Then I think you will have to save it, unless you write the data to a different workbook.
Not with OLEDB - you could use ODBC:
      .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\ADO source.xls;DefaultDir=c:\mypath;"
but I'm not convinced it would help due to the Shared workbook.


Author Closing Comment

ID: 33609485
While not resolving the issue, the expert was able to provide an explanation and point me in a better direction.

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This is about my first experience with programming Arduino.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question