• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1157
  • Last Modified:

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

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.
  • 5
  • 4
1 Solution
Rory ArchibaldCommented:
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)?
gerry_ocallaghanAuthor Commented:
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.
Rory ArchibaldCommented:
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.
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

gerry_ocallaghanAuthor Commented:
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.

gerry_ocallaghanAuthor Commented:
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
Rory ArchibaldCommented:
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.
gerry_ocallaghanAuthor Commented:
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.

Rory ArchibaldCommented:
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.

gerry_ocallaghanAuthor Commented:
While not resolving the issue, the expert was able to provide an explanation and point me in a better direction.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now