Solved

ADO - 2 part sql statement

Posted on 2004-08-27
3
218 Views
Last Modified: 2013-11-23
Using ADO connected to a SQL Server database I want to populate a recordset with a 2 part query. In a simplified version, something like this:

rs.Open "Select * Into #Temp From ATable  Select * From #Temp"

What I want returned to the recordset is the contents of the temp table. When I run this I don't get an error but the recordset state is closed. I know that I could add a stored procedure to the database but want to do this through ado if possible.

Any ideas?
0
Comment
Question by:jgv
[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
3 Comments
 
LVL 3

Expert Comment

by:rinksno1
ID: 11918957
as you said you want all the records from the temp table
use


connectionstring="provider=sqloledb.1;uid=sa;pwd=;data source=servername;initial catalog=database1"
rs.ActiveConnection = ConnectionString
rs.CursorLocation = adUseClient

rs.open "Select * from temp", , dOpenStatic, adLockReadOnly

now iterate through the recordset

***********

rinks
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 250 total points
ID: 11918972
Use this:

rs.Open "SET NOCOUNT ON Select * Into #Temp From ATable  Select * From #Temp SET NOCOUNT OFF"
0
 
LVL 12

Author Comment

by:jgv
ID: 11919009
Thanks emoreau, that did it. Now that you pointed out the nocount it makes sense. I've been caught by this in the past; just didn't think of it in this case.
0

Featured Post

Independent Software Vendors: 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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

749 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