UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

2 Pages V   1 2 >  
 Reply to this topicStart new topic
> Set RS equal to Query    
 
   
DarrenAbels
post Sep 30 2004, 02:44 PM
Post #1

UtterAccess Guru
Posts: 637
From: Kansas City, MO
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
Go to the top of the page
 
+
AJS
post Sep 30 2004, 02:55 PM
Post #2

UtterAccess VIP
Posts: 3,893
From: Lansing, Michigan, USA
Yeah.

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


hope this helps
-AJ
Go to the top of the page
 
+
ALaRiva
post Sep 30 2004, 02:55 PM
Post #3

UtterAccess VIP
Posts: 7,132
From: Perris, California
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
Go to the top of the page
 
+
DarrenAbels
post Sep 30 2004, 03:04 PM
Post #4

UtterAccess Guru
Posts: 637
From: Kansas City, MO
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
Go to the top of the page
 
+
ALaRiva
post Sep 30 2004, 03:09 PM
Post #5

UtterAccess VIP
Posts: 7,132
From: Perris, California
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
Go to the top of the page
 
+
AJS
post Sep 30 2004, 03:10 PM
Post #6

UtterAccess VIP
Posts: 3,893
From: Lansing, Michigan, USA
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
Go to the top of the page
 
+
DarrenAbels
post Sep 30 2004, 03:11 PM
Post #7

UtterAccess Guru
Posts: 637
From: Kansas City, MO
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
Go to the top of the page
 
+
ALaRiva
post Sep 30 2004, 03:14 PM
Post #8

UtterAccess VIP
Posts: 7,132
From: Perris, California
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
Go to the top of the page
 
+
DarrenAbels
post Sep 30 2004, 03:15 PM
Post #9

UtterAccess Guru
Posts: 637
From: Kansas City, MO
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
Go to the top of the page
 
+
AJS
post Sep 30 2004, 03:15 PM
Post #10

UtterAccess VIP
Posts: 3,893
From: Lansing, Michigan, USA
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.
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


hope this helps
-AJ
Go to the top of the page
 
+
ALaRiva
post Sep 30 2004, 03:23 PM
Post #11

UtterAccess VIP
Posts: 7,132
From: Perris, California
To continue this in DAO all you need to do is change this line:
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
Go to the top of the page
 
+
DarrenAbels
post Sep 30 2004, 03:26 PM
Post #12

UtterAccess Guru
Posts: 637
From: Kansas City, MO
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
Go to the top of the page
 
+
ALaRiva
post Sep 30 2004, 03:27 PM
Post #13

UtterAccess VIP
Posts: 7,132
From: Perris, California
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
Go to the top of the page
 
+
DarrenAbels
post Sep 30 2004, 03:32 PM
Post #14

UtterAccess Guru
Posts: 637
From: Kansas City, MO
I used your code and got the same error.

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
Go to the top of the page
 
+
ALaRiva
post Sep 30 2004, 03:44 PM
Post #15

UtterAccess VIP
Posts: 7,132
From: Perris, California
Any way you can zip and attach the database? Just some fake data will do.

-Anthony
Go to the top of the page
 
+
RoyVidar
post Sep 30 2004, 03:46 PM
Post #16

UtterAccess Addict
Posts: 290
From: Norway
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
Go to the top of the page
 
+
RoyVidar
post Sep 30 2004, 03:48 PM
Post #17

UtterAccess Addict
Posts: 290
From: Norway
he he copy paste without changing the name of the stored query (IMG:http://www.utteraccess.com/forum/style_emoticons/default/blush.gif) replace my testname with your query name "qryWrsDailySupportExcelDeficiencies".
Go to the top of the page
 
+
RoyVidar
post Sep 30 2004, 04:10 PM
Post #18

UtterAccess Addict
Posts: 290
From: Norway
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()
Go to the top of the page
 
+
DarrenAbels
post Sep 30 2004, 04:14 PM
Post #19

UtterAccess Guru
Posts: 637
From: Kansas City, MO
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

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
Go to the top of the page
 
+
DarrenAbels
post Sep 30 2004, 04:18 PM
Post #20

UtterAccess Guru
Posts: 637
From: Kansas City, MO
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?

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
Go to the top of the page
 
+
« Next Oldest · Access Q and A · Next Newest »
 

2 Pages V   1 2 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 22nd May 2013 - 06:48 AM