Results 1 to 25 of 25

Thread: [RESOLVED] Using ADO to retrieve multiple recordsets while executing a query only once

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Resolved [RESOLVED] Using ADO to retrieve multiple recordsets while executing a query only once

    Hi,

    I'm trying to write a query that returns multiple records in one execution. However, when I try to use multiple SQL statements in a single query I get the following error: "Characters found after end of SQL statement."
    Code:
    .CommandText = "SELECT Dutch FROM [sheet1$];SELECT English FROM [sheet1$];"
    Also I can't quite figure out how to use SQL procedures. A "Syntax error in FROM clause." error results when using the following code to generate a query:
    Code:
    .CommandText = "CREATE PROCEDURE MyResults" & vbCrLf
    .CommandText = .CommandText & "AS" & vbCrLf
    .CommandText = .CommandText & "SELECT Dutch FROM [sheet1$]" & vbCrLf
    .CommandText = .CommandText & "SELECT English FROM [sheet1$]" & vbCrLf
    .CommandText = .CommandText & "SELECT German FROM [sheet1$]" & vbCrLf
    The complete program that retrieves one recordset:
    Code:
    Public Sub Main()
    Dim CommandO As New ADODB.Command
    Dim ConnectionO As New ADODB.Connection
    Dim Column As Long
    Dim RecordsetO As New ADODB.Recordset
    Dim Row As Long
    
    ChDrive Left$(App.Path, InStr(App.Path, ":"))
    ChDir App.Path
    
    With ConnectionO
       .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;"
       .ConnectionString = .ConnectionString & "Data Source=.\Numbers.xlsx;"
       .ConnectionString = .ConnectionString & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
       .Open
    End With
    
    With CommandO
       .ActiveConnection = ConnectionO
       .CommandType = adCmdText
       .CommandText = "SELECT Dutch FROM [sheet1$];"
       Set RecordsetO = .Execute
    
       With RecordsetO
          If Not .BOF Then
             Do Until .EOF
                Row = 0
                For Column = 0 To .Fields.Count - 1
                   Debug.Print .Fields.Item(Column),
                Next Column
                Debug.Print
                .MoveNext
                Row = Row + 1
             Loop
          End If
       End With
    
       Set RecordsetO = RecordsetO.NextRecordset
    End With
    
    ConnectionO.Close
    End Sub
    So how do I get the above code to generate multiple recordsets in one execution? Do I need to use a different database driver or another database format?

    I already searched the internet, that's how I learned about SQL procedures and multiple statements in one query for example. But, there is so much information I need some one to point me in the right direction.

    Attachments:
    Numbers.zip - Contains Numbers.xlsx with some random data to have something to work with. It's used in the code above.

  2. #2
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    327

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    maybe something like this..
    Code:
    ' Set total entries display
    EntryCount.Caption = rs.RecordCount
    snippet code of mine..

  3. #3
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    To return multiple recordsets, believe you want to use a UNION clause?
    The fields returned must be the same count and field types
    Code:
    SELECT * FROM TABLE1
    UNION SELECT * FROM TABLE2
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Quote Originally Posted by LaVolpe View Post
    To return multiple recordsets, believe you want to use a UNION clause?
    The fields returned must be the same count and field types
    Code:
    SELECT * FROM TABLE1
    UNION SELECT * FROM TABLE2
    Thanks for the suggestion, I tried it, but it doesn't do what I would like. At least, when I use the following query: "SELECT English FROM [Sheet1$] UNION SELECT Dutch FROM [Sheet1$];" I get the result from both SELECT statements together in one recordset. I would like separate recordsets even if I have to use another format to store the data and use a different database driver to accomplish this. I would like to experiment with the NextRecordSet function.

    EDIT:
    Separate recordsets in one execution.
    Last edited by Peter Swinkels; Sep 15th, 2014 at 06:29 AM.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    I would like separate recordsets
    then you would need separate queries

    you can avoid using the commandtext like
    Code:
    Sql = "select max(f1) from [sheet1$]"
    rs.Open Sql, cn, adOpenStatic, adLockReadOnly
    Sql = "select * from[sheet5$] as s5"
    rs2.Open Sql, cn, adOpenStatic, adLockReadOnly
    where cn is a connection object, change the parameters as required
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    I've got some examples at work I'll post tomorrow but it is kind of like:

    "Select top 10 * from table1;select top 10 * from table2, select top 10 * from table3" and then execute it. You now have multiple recordsets returned. You access each one individually like normal and to move to the next one it is Movenext recordset (obviously that is not syntaxtically correct but that is the concept".

    Basically each separate select creates a recordset in a "parent recordset?". I never did it ith command text but the idea is the same.

    I've done it a number of times and it isn't hard. Check this out:

    http://support.microsoft.com/kb/182290

    As I mentioned when I get into work tomorrow I'll post an example I have if someone else hasn't by then.
    Last edited by TysonLPrice; Sep 14th, 2014 at 04:39 PM.
    Please remember next time...elections matter!

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Quote Originally Posted by TysonLPrice View Post
    I've got some examples at work I'll post tomorrow but it is kind of like:

    "Select top 10 * from table1;select top 10 * from table2, select top 10 * from table3" and then execute it. You now have multiple recordsets returned. You access each one individually like normal and to move to the next one it is Movenext recordset (obviously that is not syntaxtically correct but that is the concept".

    Basically each separate select creates a recordset in a "parent recordset?". I never did it ith command text but the idea is the same.

    I've done it a number of times and it isn't hard. Check this out:

    http://support.microsoft.com/kb/182290

    As I mentioned when I get into work tomorrow I'll post an example I have if someone else hasn't by then.
    I tried that, putting multiple "SELECT" statements in one query. It didn't work.

    I've looked at the sample provided on the Microsoft page and I noticed that:
    1. I don't know how I am supposed to use this example without the data that is retrieved by its query.
    2. I have no idea what the connection string should be.
    3. I need "Microsoft Data Access Components (MDAC) 2.x" which I can apparently download from:
    http://www.microsoft.com/en-US/downl...s.aspx?id=5793
    When I try to run the executable I get, some window appears and disappears so quickly I don't know what happened. The system requirements mention Windows 98 and 2000. That stuff must be really old. (Yes I know, Vb6 is just as outdated.)

    Any way thanks for replying. I'm looking forward to seeing your examples.
    Last edited by Peter Swinkels; Sep 15th, 2014 at 06:51 AM.

  8. #8
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    "I tried that, putting multiple "SELECT" statements in one query. It didn't work"

    I don't know what you mean by "didn't work". In that link is an example of doing that and I've have done it myself in the past. I never tried it against Excel though and maybe that is different. Give me some time to see what I can mock up in the next few minutes. To be honest I forgot I responded to this.
    Please remember next time...elections matter!

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Does EXCEL support MARS? Something tells me it doesn't. Nor would it support Stored Procedures.

    ADO will only support it as long as the underlying data store also supports it. Access supports it as does SQL Server, and in fact I think in SQL Server it can be turned on/off at the server level (I think, it might be Access that turns it on/off... I forget which one of the two it is; I've always worked where it was on, so I don't rightly know other than it's possible.)


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    I keep getting a run-time error 3251 - Provider does nor support returning multiple recordsets from a single execution. I just assumed since it was so easy from SQL this provider would be too. Sorry.
    Please remember next time...elections matter!

  11. #11
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    *Gack*

    SQL ≠ SQL Server. Talking incorrectly is thinking incorrectly.

  12. #12

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    @TysonLPrince: I should have been clearer: by "didn't work" I meant that I had already tried it and got a ""Characters found after end of SQL statement." error. I did mention this in my first post. I think that techgnome is right and that I need to try another database format. (I did ask whether this could be necessary in my first post as well.)

    @techgnome: I will try using Microsoft Access and see what happens.

    Any way, thanks for replying every one.
    Last edited by Peter Swinkels; Sep 15th, 2014 at 10:32 AM.

  13. #13
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Quote Originally Posted by Peter Swinkels View Post
    @TysonLPrince: I should have been clearer: "by didn't work" I meant that I had already tried it and got a ""Characters found after end of SQL statement." error. I did mention this in my first post. I think that techgnome is right and that I need to try another database format. (I did ask whether this could be necessary in my first post.)

    @techgnome: I will try using Microsoft Access and see what happens.

    Any way, thanks for replying every one.
    I was was getting that also. Then when I tried multiple execute statements I got the error where multiple recordsets were not supported by that provider. Sorry I couldn't help. If you are dead set on doing that and you have MS SQL you could probably set up a stored procedure to read the Excel sheets with OPENROWSET (or is it OPENDATASOURCE?) and pass mulitple recordsets back that way. I don't see where it would be worth the effort but maybe it is to you.
    Please remember next time...elections matter!

  14. #14

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Quote Originally Posted by TysonLPrice View Post
    I was was getting that also. Then when I tried multiple execute statements I got the error where multiple recordsets were not supported by that provider. Sorry I couldn't help. If you are dead set on doing that and you have MS SQL you could probably set up a stored procedure to read the Excel sheets with OPENROWSET (or is it OPENDATASOURCE?) and pass mulitple recordsets back that way. I don't see where it would be worth the effort but maybe it is to you.
    I switched to Microsoft Access from Excel now. The only thing I'm dead set on is to get those multiple recordsets in one execution. It must be possible otherwise there would be no "NextRecordset" function for the "RecordSet" interface in ADO, right? I don't really care what database format or whatever I need as long I can get it done. Why? I enjoy computer programming and to learn new things. Perhaps I can use what I learn to improve a program I wrote for accessing databases.

  15. #15

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Quote Originally Posted by techgnome View Post
    Does EXCEL support MARS? Something tells me it doesn't. Nor would it support Stored Procedures.

    ADO will only support it as long as the underlying data store also supports it. Access supports it as does SQL Server, and in fact I think in SQL Server it can be turned on/off at the server level (I think, it might be Access that turns it on/off... I forget which one of the two it is; I've always worked where it was on, so I don't rightly know other than it's possible.)


    -tg
    Alright, I am now using Microsoft Access 2007 and I've tried:
    1. Multiple SQL statements in a single query. Result: "Characters found after end of SQL statement" error.
    2. Connecting two SQL statements using the UNION keyword. Result: I get the results from both statements in a single recordset.
    3. Creating a SQL procedure using "CREATE PROCURE name AS statement". Result: Microsoft Access doesn't appear to support multiple statements in a procedure. Grrr.

    The connection string I'm using:
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\Numbers.accdb;Persist Security Info=False;"

    Attachment:
    The Microsoft Access database I'm using.

    My question:
    Which database does support the NextRecordSet function??

  16. #16
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Quote Originally Posted by Peter Swinkels View Post
    I switched to Microsoft Access from Excel now. The only thing I'm dead set on is to get those multiple recordsets in one execution. It must be possible otherwise there would be no "NextRecordset" function for the "RecordSet" interface in ADO, right? I don't really care what database format or whatever I need as long I can get it done. Why? I enjoy computer programming and to learn new things. Perhaps I can use what I learn to improve a program I wrote for accessing databases.
    Make a reference to Microsoft ActiveX Data Objects n.n

    Connect to some database

    Code:
    Private Sub Command1_Click()
        Dim cn As New ADODB.Connection
        Dim rsTempRecordset As New ADODB.Recordset
        
         With cn
            .ConnectionString = "Server=devsql\tpa1;Database=manhattan;Driver=SQL Server;Trusted_Connection=Yes"
            .ConnectionTimeout = 0
            .CursorLocation = adUseClient
            .Open
        End With
        
        strSQL = "select 'AAAAA';select 'BBBBB' ;select 'CCCCC' "
        
        rsTempRecordset.Open strSQL, cn, adOpenStatic, adLockOptimistic
        
        'Show the first recordset
        MsgBox rsTempRecordset(0)
        
        Set rsTempRecordset = rsTempRecordset.NextRecordset
        
        'Show the second recordset
        MsgBox rsTempRecordset(0)
        
        Set rsTempRecordset = rsTempRecordset.NextRecordset
        
        'Show the third recordset
        MsgBox rsTempRecordset(0)
        
        rsTempRecordset.Close
        Set rsTempRecordset = Nothing
        cn.Close
        Set cn = Nothing
        
    End Sub
    Please remember next time...elections matter!

  17. #17
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Again, still there's the question of whether Access supports it. I thought it did. But when I google multiple recordsets, the only examples I get back are SQL Server or ADO.NET. So now I'm not so sure.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  18. #18
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Quote Originally Posted by techgnome View Post
    Again, still there's the question of whether Access supports it. I thought it did. But when I google multiple recordsets, the only examples I get back are SQL Server or ADO.NET. So now I'm not so sure.

    -tg
    Agreed...at the time I posted that I took this post from the OP, "I don't really care what database format or whatever I need as long I can get it done. Why? I enjoy computer programming and to learn new things. Perhaps I can use what I learn to improve a program I wrote for accessing databases.", literally.
    Please remember next time...elections matter!

  19. #19

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    @TysonLPrince: Thanks for the code. I tried it, and it just freezes when I run it. Microsoft Visual doesn't respond to anything any more. Nothing to add, it just freezes... I suppose I should change the connection string? I have Microsoft SQL Server installed as it came with Vb.net. What should I specify for a database file on the local computer?

  20. #20
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Well, if you are trying to connect to mine then you will have issues

    Yes, connect to something you have access to.
    Please remember next time...elections matter!

  21. #21
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Quote Originally Posted by Peter Swinkels View Post
    The only thing I'm dead set on is to get those multiple recordsets in one execution. It must be possible otherwise there would be no "NextRecordset" function for the "RecordSet" interface in ADO, right?
    ADO is a wrapper of sorts for various DB providers. It is the provider, you used in the connection string, that determines if & how multiple recordsets are implemented. For an example of differences, here is a quote from this msdn page (which does have a VB example linked at bottom of that page)
    Quote Originally Posted by msdn
    Your OLE DB provider determines when each command command in a compound statement is executed. The Microsoft OLE DB Provider for SQL Server, for example, executes all commands in a batch upon receiving the compound statement. The resulting Recordsets are simply returned when you call NextRecordset.

    However, other providers may execute the next command in a statement only after NextRecordset is called. For these providers, if you explicitly close the Recordset object before stepping through the entire command statement, ADO never executes the remaining commands.
    For providers that don't support multiple recordsets, this may still be accomplished with a UNION statement + filters, but with far more restrictions. The recordsets must contain same number of fields and the field, order/values must be compatible with each other
    Code:
    rs.Open "Select *, 1 As RsID From TABLE1 UNION Select *, 2 As RsID From Table2", ...
    to view recordset #1:  rs.Filter = "[RsID] = 1"
    to view recordset #2:  rs.Filter = "[RsID] = 2"
    Realize this isn't what your after, but it can be a workaround if the conditions are right
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  22. #22

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    @TysonLPrince: Yes, I did realize that I had to change the server in the connection string in the example you gave me. Like I asked in my previous post: "What should I specify for a database on the local computer?"

    @LaVolpe: Yes, I know that whether multiple recordsets in one execution are supported or not depends on the database provider. The problem is that I don't know which provider to use for what I want. So far I have tried data sources such as text files, Excel sheets, and Access databases. If I understand TysonLPrince right I need to set up a Microsoft SQL Server, but I don't have the slightest idea how.

    So how do I set up such a server?

  23. #23
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    "What should I specify for a database on the local computer?"
    check out www.connectionstrings.com
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  24. #24
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Quote Originally Posted by Peter Swinkels View Post
    The only thing I'm dead set on is to get those multiple recordsets in one execution. It must be possible otherwise there would be no "NextRecordset" function for the "RecordSet" interface in ADO, right? I don't really care what database format or whatever I need as long I can get it done.
    Since it already came out quite clearly, that the feature is not available "across the board"
    of ADO-OleDB-Drivers, why not implement it yourself in a small Class - and with broader
    support for all (or most) DB-Engines/drivers by sticking with the Standard-ADO-methods...

    I don't see the NextRecordset-Method as a "Killer-Feature" - it is rarely needed and rarely used -
    and an alternative can be implemented in a few lines of code anytime you want.

    e.g. in a Class cMultiRs
    Code:
    Private mSQLArr() As String, mCurIdx As Long
    
    Public Function AddSelectStatementsAndGetFirst(SQLArr() As String) As Recordset
      mSQLArr = SQLArr
      mCurIdx = LBound(mSQLArr)
      Set AddSelectStatementsAndGetFirst = GetRs(mSQLArr(mCurIdx))
    End Function 
    
    Public Function GetNextRecordset() As Recordset
      If mCurIdx < UBound(mSQLArr) Then 
        mCurIdx = mCurIdx + 1  
        Set GetNextRecordset = GetRs(mSQLArr(mCurIdx))
      End If
    End Function
    In above code GetRs being a Helper-Function in a *.bas which "knows" your current ADO-Cnn.
    But the principle to implement something like that is really quite simple.

    But as said, I still have no clue in what concrete scenarios such a "NextRecordset"-Functionality
    is able to act as a huge time-saver (whereas storing multiple Resultsets which belong together
    in a simple Array is far more flexible, since you could navigate back or directly pick a Set per Index).

    Assuming you're able to set-up an MS-SQL-Server-Instance - what will you use it for finally?

    I mean, just to confirm that this minor and rarely used ADORs-method "indeed works with MS-SQLServer" -
    doesn't really worth the efforts IMO...

    Olaf

  25. #25

    Thread Starter
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    Re: Using ADO to retrieve multiple recordsets while executing a query only once

    Okay, I'm going to forget about NextRecordset, for now at least. I did learn a few interesting things from this though. Which was my main motivation any way. Thanks for all the help.

    EDIT:

    I finally figured it out, for any one else who wants to retrieve multiple recordsets in one execution:
    Code:
    Public Sub Main()
    Dim ConnectionO As New ADODB.Connection
    Dim Column As Long
    Dim RecordsetO As New ADODB.Recordset
    Dim Row As Long
    
    ChDrive Left$(App.Path, InStr(App.Path, ":"))
    ChDir App.Path
    
    ConnectionO.Open "Provider=SQLNCLI11;Server=***\SQLEXPRESS;Database=NumbersDatabase;Trusted_Connection=yes;"
    RecordsetO.Open "SELECT English FROM Numbers;SELECT Dutch,German FROM Numbers;", ConnectionO
    
    Debug.Print "--->>> Database test at: " & Time$() & "<<<---"
    
    Do While RecordsetO.State = adStateOpen
       With RecordsetO
          Do Until .BOF Or .EOF
             Row = 0
             For Column = 0 To .Fields.Count - 1
                Debug.Print .Fields.Item(Column),
             Next Column
             Debug.Print
             .MoveNext
             Row = Row + 1
          Loop
       End With
       
       Set RecordsetO = RecordsetO.NextRecordset   
    Loop
    
    ConnectionO.Close
    End Sub
    Last edited by Peter Swinkels; Sep 17th, 2014 at 02:12 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width