Solved

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

Posted on 2010-08-30
9
1,147 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
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)?
0
 

Author Comment

by:gerry_ocallaghan
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.
0
 
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.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:gerry_ocallaghan
ID: 33574323
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
 

Author Comment

by:gerry_ocallaghan
ID: 33596649
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
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.
Rory
0
 

Author Comment

by:gerry_ocallaghan
ID: 33597164
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
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.


0
 

Author Closing Comment

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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

726 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