Solved

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

Posted on 2010-08-30
9
1,139 Views
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.
0
Comment
Question by:gerry_ocallaghan
  • 5
  • 4
9 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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)?
0
 

Author Comment

by:gerry_ocallaghan
Comment Utility
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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.
0
 

Author Comment

by:gerry_ocallaghan
Comment Utility
Rorya,
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.

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:gerry_ocallaghan
Comment Utility
Rorya,
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;"""
      .Open
   End With
   strQuery = "SELECT * FROM [Sheet1$];"
   Set rst = New ADODB.Recordset
   rst.Open strQuery, strConn, adOpenStatic, adLockReadOnly, adCmdText
   varData = rst.GetRows
   rst.Close
   Set rst = Nothing
'   cn.Close
   Set cn = Nothing
End Sub
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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.
Rory
0
 

Author Comment

by:gerry_ocallaghan
Comment Utility
Rorya,
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.

Thanks.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
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.


0
 

Author Closing Comment

by:gerry_ocallaghan
Comment Utility
While not resolving the issue, the expert was able to provide an explanation and point me in a better direction.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This is about my first experience with programming Arduino.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

762 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now