Solved

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

Posted on 2010-08-30
9
1,149 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Simple Linear Regression

688 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