Hello
What I would like to do is Loop through a record set using VBA code. I have a query I created which is really large and I am having problems getting the SQL View of my query copied correctly into the VBA code which I would use to set my record set. Is there a way to set my record set equal to a query?
Any help is apprecited.
Attached is the query in both Views.
Thanks
Darren
Full Version: Set RS equal to Query
Yeah.
This should work:
hope this helps
-AJ
This should work:
CODE
dim rst as ADODB.Recordset
set rst = New ADODB.Recordset
with rst
.ActiveConnection = CodeProject.Connection
.Source = "YourQueryName"
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.open ,,,,adCmdStoredProc
end with
set rst = New ADODB.Recordset
with rst
.ActiveConnection = CodeProject.Connection
.Source = "YourQueryName"
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.open ,,,,adCmdStoredProc
end with
hope this helps
-AJ
rs.Open "SELECT * FROM YourQueryName"
Of Course, you didn't say what you were using, ADO or DAO, so I can't go any further, just but using SELECT * FROM YourQueryName and that'll just reference the existing query.
Is that what you needed?
HTH
-Anthony
Of Course, you didn't say what you were using, ADO or DAO, so I can't go any further, just but using SELECT * FROM YourQueryName and that'll just reference the existing query.
Is that what you needed?
HTH
-Anthony
Thanks for the help.
I used the "SELECT * FROM qryWrsDailySupportExcel" but I think I have a problem because I am still getting the same error message, "3061- Too few parameters. Expected 2."
Would you happen to know why I am getting this error message?
I am using DAO database but I will be honest, I do not know what is differert from ADO and DAO. I have just always used DAO and it has worked. If you have a second and know the differece, the information is appreciated.
Thanks
Darren
I used the "SELECT * FROM qryWrsDailySupportExcel" but I think I have a problem because I am still getting the same error message, "3061- Too few parameters. Expected 2."
Would you happen to know why I am getting this error message?
I am using DAO database but I will be honest, I do not know what is differert from ADO and DAO. I have just always used DAO and it has worked. If you have a second and know the differece, the information is appreciated.
Thanks
Darren
OK, post the DAO Code that you are using so that we can see what you are missing.
As for the difference between ADO and DAO, let me see if I can find something that explains the differences clearly. I wrote something on it, but don't have it with me here at my client's location.
-Anthony
As for the difference between ADO and DAO, let me see if I can find something that explains the differences clearly. I wrote something on it, but don't have it with me here at my client's location.
-Anthony
Check for typos. I didn't see any obvious parameters when I glanced through it the first time, but I found at least one spot where you typed "qryWrsDailySupoortExcelDateRange"
What's happening is that the typo causes Access to think that there is some sort of variable in the query, since it doesn't recognize the name, and it doesn't know what that variable might be. So it errors, giving you the message.
-AJ
What's happening is that the typo causes Access to think that there is some sort of variable in the query, since it doesn't recognize the name, and it doesn't know what that variable might be. So it errors, giving you the message.
-AJ
To explain a little more, I am taking a beginning and ending date range from 2 text boxes on a form. Then, when I hit the command button, it runs the query. When this occurs, I get the error I described above.
Now, if I populate the form and then go run the query manually by opening the query and then running it, the query returns the records that I would expect it to run.
Any thoughts would be appreciated.
Thanks
Darren
Now, if I populate the form and then go run the query manually by opening the query and then running it, the query returns the records that I would expect it to run.
Any thoughts would be appreciated.
Thanks
Darren
two few parameters usually means that you didn't open the recordset with all of the required parameters. Can you please post what code you already have.
-Anthony
-Anthony
Here is my code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM qryWrsDailySupportExcelDeficiencies"
Set rs = db.OpenRecordset(strSQL)
With rst
Do While Not .EOF
response = MsgBox(rs![IssueID], vbOKOnly)
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM qryWrsDailySupportExcelDeficiencies"
Set rs = db.OpenRecordset(strSQL)
With rst
Do While Not .EOF
response = MsgBox(rs![IssueID], vbOKOnly)
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
Set db = Nothing
So it actually is parameterized -- you're getting parameters off of a form. Neither DAO or ADO know what the values on your form are.
The following method works for opening a parameterized query when the parameters are read off of a form.
hope this helps
-AJ
The following method works for opening a parameterized query when the parameters are read off of a form.
CODE
dim cmd as ADODB.Command
dim rst as ADODB.Recordset
dim prm as ADODB.Parameter
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CodeProject.Connection
.Properties("Jet OLEDB:Stored Query") = True
.CommandText = "YourQueryName"
For each prm in .Parameters
prm = eval(prm.Name)
next prm
set rst = .Execute
End With
dim rst as ADODB.Recordset
dim prm as ADODB.Parameter
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CodeProject.Connection
.Properties("Jet OLEDB:Stored Query") = True
.CommandText = "YourQueryName"
For each prm in .Parameters
prm = eval(prm.Name)
next prm
set rst = .Execute
End With
hope this helps
-AJ
To continue this in DAO all you need to do is change this line:
to this
However, if you are referencing a form as part of the criteria, then that form needs to be open when the query is executed.
HTH
-Anthony
CODE
Set rs = db.OpenRecordset(strSQL)
to this
CODE
Set rs = db.OpenRecordset(strSQL,dbOpenDynaset)
However, if you are referencing a form as part of the criteria, then that form needs to be open when the query is executed.
HTH
-Anthony
So do I do the above code instead of
strSQL = "SELECT * FROM qryWrsDailySupportExcelDeficiencies"
Set rs = db.OpenRecordset(strSQL)
Then how do I do my With Loop through the record set?
With rs
Do While Not .EOF
response = MsgBox(rs![IssueID], vbOKOnly)
.MoveNext
Loop
End With
rs.Close
strSQL = "SELECT * FROM qryWrsDailySupportExcelDeficiencies"
Set rs = db.OpenRecordset(strSQL)
Then how do I do my With Loop through the record set?
With rs
Do While Not .EOF
response = MsgBox(rs![IssueID], vbOKOnly)
.MoveNext
Loop
End With
rs.Close
Actually, I was thinking of this totally wrong, I see where AJS was going, I totally missed this one. Sorry about that.
Give AJS' example a run and it should work for ya.
Thanks, and sorry for missing that.
-Anthony
Give AJS' example a run and it should work for ya.
Thanks, and sorry for missing that.
-Anthony
I used your code and got the same error.
Any thoughts?
Any thoughts?
CODE
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CodeProject.Connection
.Properties("Jet OLEDB:Stored Query") = True
.CommandText = "qryWrsDailySupportExcelDeficiencies"
For Each prm In .Parameters
prm = Eval(prm.Name)
Next prm
Set rst = .Execute
End With
With cmd
.ActiveConnection = CodeProject.Connection
.Properties("Jet OLEDB:Stored Query") = True
.CommandText = "qryWrsDailySupportExcelDeficiencies"
For Each prm In .Parameters
prm = Eval(prm.Name)
Next prm
Set rst = .Execute
End With
Any way you can zip and attach the database? Just some fake data will do.
-Anthony
-Anthony
I think in Access 2000, the command objects parameters collection isn't properly populated, unless it's retrieved thru ADOX, set a reference to 'Microsoft ADO Ext. 2.# for DDL and Security' and try this
CODE
dim cmd as ADODB.Command
dim rs as ADODB.Recordset
dim prm as ADODB.Parameter
dim cat as ADOX.Catalog
dim prc as ADOX.Procedure
set cat = new ADOX.Catalog
cat.ActiveConnection=CurrentProject.Connection
set prc = cat.Procedures("qrySet")
set cmd = prc.Command
for each prm in cmd.Parameters
prm.Value = Eval(prm.Name)
next prm
set rs = cmd.Execute
dim rs as ADODB.Recordset
dim prm as ADODB.Parameter
dim cat as ADOX.Catalog
dim prc as ADOX.Procedure
set cat = new ADOX.Catalog
cat.ActiveConnection=CurrentProject.Connection
set prc = cat.Procedures("qrySet")
set cmd = prc.Command
for each prm in cmd.Parameters
prm.Value = Eval(prm.Name)
next prm
set rs = cmd.Execute
he he copy paste without changing the name of the stored query 
replace my testname with your query name "qryWrsDailySupportExcelDeficiencies".
replace my testname with your query name "qryWrsDailySupportExcelDeficiencies".
Same thing should be doable thru dao too (though I don't use it), probably something like this
CODE
dim qd as dao.querydef
dim prm as dao.parameter
dim rs as dao.recordset
set qd=currentdb.querydefs("qryWrsDailySupportExcelDeficiencies")
for each prm in qd.parameters
prm.value=eval(prm.name)
next prm
set rs=qd.openrecordset()
dim prm as dao.parameter
dim rs as dao.recordset
set qd=currentdb.querydefs("qryWrsDailySupportExcelDeficiencies")
for each prm in qd.parameters
prm.value=eval(prm.name)
next prm
set rs=qd.openrecordset()
OK.. it worked! I really appreciate the help but if it is not to much to ask, can you explain why it worked?
I have many of times referenced a field on a form in my query and had it run successfully. I do have to admit this query is much more complex but it still should work. So what did I do by adding the below code to make it work?
The other thing I noticed is the query takes about 10 seconds to run which is ok since it works but it is a lot longer than any other query I have had run. Why is it taking so long? Basically, I really don't know what this code does and if you have the time, an explanation would be much appreciated.
Thanks
Darren
I have many of times referenced a field on a form in my query and had it run successfully. I do have to admit this query is much more complex but it still should work. So what did I do by adding the below code to make it work?
The other thing I noticed is the query takes about 10 seconds to run which is ok since it works but it is a lot longer than any other query I have had run. Why is it taking so long? Basically, I really don't know what this code does and if you have the time, an explanation would be much appreciated.
Thanks
Darren
CODE
Set cmd = New ADODB.Command
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set prc = cat.Procedures("qryWrsDailySupportExcelDeficiencies")
Set cmd = prc.Command
For Each prm In cmd.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = cmd.Execute
With rs
Do While Not .EOF
response = MsgBox(rs![IssueID], vbOKOnly)
.MoveNext
Loop
End With
rs.Close
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set prc = cat.Procedures("qryWrsDailySupportExcelDeficiencies")
Set cmd = prc.Command
For Each prm In cmd.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = cmd.Execute
With rs
Do While Not .EOF
response = MsgBox(rs![IssueID], vbOKOnly)
.MoveNext
Loop
End With
rs.Close
RoyVidar
You are right, your code also worked but it is a lot faster. So what does the code you gave me do to make it work?
Set qd = CurrentDb.QueryDefs("qryWrsDailySupportExcelDeficiencies")
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qd.OpenRecordset()
With rs
Do While Not .EOF
response = MsgBox(rs![IssueID], vbOKOnly)
.MoveNext
Loop
End With
rs.Close
Thanks
Darren
You are right, your code also worked but it is a lot faster. So what does the code you gave me do to make it work?
CODE
Set qd = CurrentDb.QueryDefs("qryWrsDailySupportExcelDeficiencies")
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qd.OpenRecordset()
With rs
Do While Not .EOF
response = MsgBox(rs![IssueID], vbOKOnly)
.MoveNext
Loop
End With
rs.Close
Thanks
Darren
Ouch - I'm not a great fan of finding out why things work or not 
Can't say much about DAO, cause I only use it for form recordset manipulation, but what's happening is that when a stored query contains references to forms (parameters), VBA knows nothing about them, and we need to supply information about them, prior to opening a recordset based on it.
So in the first sample we're getting this info from the catalog objects procedures collection, getting/populating the command objects parameters collection, then step thru each parameter and resolve/evaluate them.
Using the ADOX Catalog object, I believe is only necessary in Access 2000, in later versions, the more elegant way presented by AJS works, probably faster, because ADOX isn't needed.
In DAO, it's probably the same, just using querydef's parameters collection doing the same.
Edited by: RoyVidar on Thu Sep 30 18:44:27 EDT 2004.
Can't say much about DAO, cause I only use it for form recordset manipulation, but what's happening is that when a stored query contains references to forms (parameters), VBA knows nothing about them, and we need to supply information about them, prior to opening a recordset based on it.
So in the first sample we're getting this info from the catalog objects procedures collection, getting/populating the command objects parameters collection, then step thru each parameter and resolve/evaluate them.
Using the ADOX Catalog object, I believe is only necessary in Access 2000, in later versions, the more elegant way presented by AJS works, probably faster, because ADOX isn't needed.
In DAO, it's probably the same, just using querydef's parameters collection doing the same.
Edited by: RoyVidar on Thu Sep 30 18:44:27 EDT 2004.
Thanks to everyone for the help! I am still unclear on the difference between DAO and ADO but besides that your explanation did help. Again... thanks for all of the help. I would not have nearly as much success with the database if it was not fore people like every one here.
You're welcome!
Differences between ADO and DAO? Much has been written, both here at UA and other places, try an advanced searh, here and with your favourite web search engine. Basically, they are two different object libraries giving us the possiblity of manipulating both data/information, and definition (table structure, relationship, field definitions...). They share a lot of objects and methods, which makes it necessary to disambiguate any declarations, and often also the instantiations (dim rs=dao.recordset, dim rs2 as adodb.recordset, set rs2=new adodb.recordset...).
DAO is "old", and as I've understood, specially designed for Access/Jet, therefore it will often perform faster than ADO on native Access tables. ADO is newer, and is designed to give access to "all" providers. Here's one thread with some info and links ADO vs DAO
Differences between ADO and DAO? Much has been written, both here at UA and other places, try an advanced searh, here and with your favourite web search engine. Basically, they are two different object libraries giving us the possiblity of manipulating both data/information, and definition (table structure, relationship, field definitions...). They share a lot of objects and methods, which makes it necessary to disambiguate any declarations, and often also the instantiations (dim rs=dao.recordset, dim rs2 as adodb.recordset, set rs2=new adodb.recordset...).
DAO is "old", and as I've understood, specially designed for Access/Jet, therefore it will often perform faster than ADO on native Access tables. ADO is newer, and is designed to give access to "all" providers. Here's one thread with some info and links ADO vs DAO
Thanks for the information and tips. I will definitely need to do more research on this.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.