-
Sep 14th, 2014, 11:36 AM
#1
Thread Starter
Frenzied Member
[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.
-
Sep 14th, 2014, 11:52 AM
#2
Hyperactive Member
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..
-
Sep 14th, 2014, 12:31 PM
#3
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
-
Sep 14th, 2014, 02:52 PM
#4
Thread Starter
Frenzied Member
Re: Using ADO to retrieve multiple recordsets while executing a query only once
Originally Posted by LaVolpe
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.
-
Sep 14th, 2014, 04:01 PM
#5
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
-
Sep 14th, 2014, 04:30 PM
#6
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!
-
Sep 15th, 2014, 06:48 AM
#7
Thread Starter
Frenzied Member
Re: Using ADO to retrieve multiple recordsets while executing a query only once
Originally Posted by TysonLPrice
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.
-
Sep 15th, 2014, 07:18 AM
#8
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!
-
Sep 15th, 2014, 07:22 AM
#9
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
-
Sep 15th, 2014, 08:21 AM
#10
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!
-
Sep 15th, 2014, 08:53 AM
#11
Re: Using ADO to retrieve multiple recordsets while executing a query only once
*Gack*
SQL ≠ SQL Server. Talking incorrectly is thinking incorrectly.
-
Sep 15th, 2014, 09:40 AM
#12
Thread Starter
Frenzied Member
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.
-
Sep 15th, 2014, 09:48 AM
#13
Re: Using ADO to retrieve multiple recordsets while executing a query only once
Originally Posted by Peter Swinkels
@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!
-
Sep 15th, 2014, 10:10 AM
#14
Thread Starter
Frenzied Member
Re: Using ADO to retrieve multiple recordsets while executing a query only once
Originally Posted by TysonLPrice
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.
-
Sep 15th, 2014, 12:30 PM
#15
Thread Starter
Frenzied Member
Re: Using ADO to retrieve multiple recordsets while executing a query only once
Originally Posted by techgnome
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??
-
Sep 15th, 2014, 12:32 PM
#16
Re: Using ADO to retrieve multiple recordsets while executing a query only once
Originally Posted by Peter Swinkels
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!
-
Sep 15th, 2014, 12:53 PM
#17
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
-
Sep 15th, 2014, 12:56 PM
#18
Re: Using ADO to retrieve multiple recordsets while executing a query only once
Originally Posted by techgnome
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!
-
Sep 15th, 2014, 01:07 PM
#19
Thread Starter
Frenzied Member
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?
-
Sep 15th, 2014, 01:22 PM
#20
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!
-
Sep 15th, 2014, 10:00 PM
#21
Re: Using ADO to retrieve multiple recordsets while executing a query only once
Originally Posted by Peter Swinkels
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)
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
-
Sep 16th, 2014, 02:30 AM
#22
Thread Starter
Frenzied Member
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?
-
Sep 16th, 2014, 07:27 AM
#23
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
-
Sep 16th, 2014, 09:57 AM
#24
Re: Using ADO to retrieve multiple recordsets while executing a query only once
Originally Posted by Peter Swinkels
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
-
Sep 16th, 2014, 12:29 PM
#25
Thread Starter
Frenzied Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|