By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,574 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

How to access a query inside MS Access program

P: 26
Hi eveyone,

Its my first time to use MS Access 2003 and I need to modify the existing program

I have a MS Access query and I want to fetch the record 1 by 1 and write the result in a text file. Can I use MS Access OpenRecordSet() for this? If yes,
can anybody help me with the syntax? I tried the following code but it didn't work
Expand|Select|Wrap|Line Numbers
  1. ...
  2. Dim MyDb As Database
  3. Dim rstQry As Recordset
  4.  
  5. strQry = "CashierOrder"    ' my MS Access Query
  6.  
  7. Set rstQry = MyDb.OpenRecordset(strQry)   ' fails here saying Too few parameters. Expected 2.[3061]
  8. ...

Another question, how can I pass parameter to my existing query?

this is the select statement of my query

Expand|Select|Wrap|Line Numbers
  1. SELECT Format$(DSum("sumofamount","subqry_CashierOrder"),"#.00") AS Expr3
  2. FROM dbo_SALFLDGDSN INNER JOIN dbo_qrySSRFADDDSN ON dbo_SALFLDGDSN.ACCNT_CODE = dbo_qrySSRFADDDSN.ADD_CODE
  3. WHERE (((dbo_SALFLDGDSN.D_C)="C"))
  4. GROUP BY dbo_SALFLDGDSN.PERIOD, Trim([ACCNT_CODE]), dbo_SALFLDGDSN.JRNAL_NO
  5. HAVING (((dbo_SALFLDGDSN.PERIOD)=SunToSqlServerPeriod([Forms]![frmPayInvFileGen]![txtRepPeriod])) AND ((Trim([ACCNT_CODE])) Like "9*") AND ((dbo_SALFLDGDSN.JRNAL_NO)=[Forms]![frmPayInvFileGen]![txtJrnlNo]))
  6. ORDER BY Trim([ACCNT_CODE])
Thanks in advance
Aug 12 '08 #1
Share this Question
Share on Google+
13 Replies


Expert 100+
P: 112
Regarding issue #1:
It appears that the reason this is failing is that the query you are referencing expects parameters and you aren't giving it any so it fails. It is not straightforward to use parameterized queries for your recordset datasource so I will reference you here:
http://support.microsoft.com/default...b;en-us;209203
Other than that it should open your recordset. One other note is in your variable declaration of the recordset it is always a good idea to fully qualify it as
Expand|Select|Wrap|Line Numbers
  1. Dim rst as DAO.RecordSet
  2.  
or
Expand|Select|Wrap|Line Numbers
  1. Dim rst as ADODB.RecordSet
  2.  
as the lack of doing so can cause your code to break when used in another database based on the search priority for rectifying name clashes. Especially since MS has gone back and forth on the preferred method (DAO versus ADO) in your case you are wanting to use DAO. Let me know if you have any further questions.

Not sure if this answers issue #2 (are you asking how to pass parameters to queries or how to pass it to the query so it loads in the recordset?).
Aug 12 '08 #2

P: 26
Hi janders468,

Thanks for the reply, I did try the declaration with DAO but still I end up with the same error message.

For issue #1, sorry I didn't mentioned that the calling of my query is inside one of the Sub (procedure) from a form where these parameters are supplied successfully.

Actually I tried using DoCmd.OpenQuery just to check if my query is valid & error free, then it is. I got the query executed with the result set, but I want to assign each of the columns of these query results to a variable. I guess I need to use OpenRecordSet() method for this, but the thing is I don't know the syntax.

Is it possible to use a query inside OpenRecordset() method to extract the result into a variable? Can you give me the syntax or maybe example.


Thanks Again....
EVH316



Regarding issue #1:
It appears that the reason this is failing is that the query you are referencing expects parameters and you aren't giving it any so it fails. It is not straightforward to use parameterized queries for your recordset datasource so I will reference you here:
http://support.microsoft.com/default...b;en-us;209203
Other than that it should open your recordset. One other note is in your variable declaration of the recordset it is always a good idea to fully qualify it as
Expand|Select|Wrap|Line Numbers
  1. Dim rst as DAO.RecordSet
  2.  
or
Expand|Select|Wrap|Line Numbers
  1. Dim rst as ADODB.RecordSet
  2.  
as the lack of doing so can cause your code to break when used in another database based on the search priority for rectifying name clashes. Especially since MS has gone back and forth on the preferred method (DAO versus ADO) in your case you are wanting to use DAO. Let me know if you have any further questions.

Not sure if this answers issue #2 (are you asking how to pass parameters to queries or how to pass it to the query so it loads in the recordset?).
Aug 13 '08 #3

Expert 100+
P: 112
Hi EVH316,

I am assuming that you have a control in a form that contains the value and that is being fed to the query. Even though it seems like this should qualify as filling the parameter it does not, and you will get this same error. Here is a quick and dirty example of how to supply the parameter within code. I don't really ever do it this way so if there is a better way anyone can feel free to correct me, but this was essentially how Microsoft said to do it. This assumes you have a parameter called [Test]. The key point to realize is that you set this parameter and then use the QueryDef to open the recordset.
Expand|Select|Wrap|Line Numbers
  1.     Dim strQuery As String
  2.     Dim qd As QueryDef
  3.     Dim daoRst As DAO.Recordset
  4.     strQuery = YourQuery
  5.     Set qd = CurrentDb.QueryDefs(strQuery)
  6.     qd![Test] = PutTheValueofTheControlHere
  7.     Set daoRst = qd.OpenRecordset()
  8.  
When I am faced with a situation where I want to filter a query based on the values in a form I typically build up the where clause of the SQL at runtime. You could do that and then use the SQL string to open a recordset. The reason I feel this approach is better is that you don't hardcode your parameter names (especially if you are using controls from a form as parameters, if you change the name of the controls or the name of the form it can be a maintenance issue) it just gets generated based on the values you are interested in.

In answer to your second question about placing the results in a variable, the answer is, the results are actually already in a variable (the RecordSet) which you can manipulate. The values are accessed based on an index number or string. There is a cursor in the recordset that keeps track of what row you are on. If you have a recordset, rs, then
Expand|Select|Wrap|Line Numbers
  1.      Dim varItem as Variant
  2.      varItem =  rs(0) 
  3.  
will place the value of the first column of the current row into varItem. You can reference the columns positionally or by name. If the first column is titled "Column1" then rs("Column1") will reference the same value as rs(0). You can move through the recordset using the methods MoveNext, MovePrevious, MoveFirst, MoveLast
For example this will loop through every record (in the column selected) in the recordset and is a very common looping structure for recordsets
Expand|Select|Wrap|Line Numbers
  1. Do
  2.    'Do something with the value here, such as write to a text file
  3.    rs(0)
  4.    rs.MoveNext
  5. Loop until rs.Eof
  6.  
Recordsets are complicated and I can't really go into all the details but thought this might orient your thinking about the object you are dealing with.
Aug 13 '08 #4

ADezii
Expert 5K+
P: 8,597
Janders has got you covered on the other points, so I'll take the easy one, how to Print the results of a Query to a Text File. The following code will print the results of a Query (SQL Statement) listing the Employee Last Names (alpha), First Names, and City from the Northwind Database to a Text File named C:\QueryResults.txt:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim MyRS As DAO.Recordset
  3. Dim strSQL As String
  4. Dim intFileNum As Integer
  5.  
  6. strSQL = "SELECT Employees.LastName, Employees.FirstName, Employees.City FROM " & _
  7.          "Employees ORDER BY Employees.LastName;"
  8.  
  9. intFileNum = FreeFile
  10.  
  11. Set MyDB = CurrentDb()
  12.  
  13. Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
  14.  
  15. Open "C:\QueryResults.txt" For Output As #intFileNum
  16.  
  17. Do While Not MyRS.EOF
  18.   Print #intFileNum, MyRS![LastName]; Tab(20); MyRS![FirstName]; Tab(35); MyRS![City]
  19.   MyRS.MoveNext
  20. Loop
  21.  
  22. MyRS.Close
  23. Set MyRS = Nothing
  24.  
  25. Close #intFileNum
OUTPUT ==> C:\QueryResults.txt
Expand|Select|Wrap|Line Numbers
  1. Buchanan           Steven         London
  2. Callahan           Laura          Seattle
  3. Davolio            Nancy          Seattle
  4. Dodsworth          Anne           London
  5. Fuller             Andrew         Tacoma
  6. King               Robert         London
  7. Leverling          Janet          Kirkland
  8. Peacock            Margaret       Redmond
  9. Suyama             Michael        London
Aug 13 '08 #5

Expert 100+
P: 112
Thanks ADezii,

I got so hung up on the parameter/recordset discussion totally forgot about the other part!
Aug 13 '08 #6

P: 26
thank you very much, everybody here is so helpful. I'll tried this example later becuase I got urgent work to test....thanks again
Aug 13 '08 #7

P: 26
I combined Jarden & ADezil answers and I end up with this function
Expand|Select|Wrap|Line Numbers
  1. Public Function GetTotalAmt() As Long
  2.     Dim currDB As Database
  3.     Dim rstDAO As DAO.Recordset
  4.     Dim qryDef As QueryDef
  5.    Dim strQry As String
  6.  
  7.    Set currDB = CurrentDb()
  8.    Set strQry = "CashierOrder"      '<---- got error here saying "Compile error:   Object required"
  9.    Set qryDef = currDB.QueryDef(strQry)
  10.    Set rstDAO = qryDef.OpenRecordset()
  11.  
  12.    Do While Not rstDAO.EOF
  13.       MsgBox rstDAO![sum_amt]
  14.       rstDAO.MoveNext
  15.  
  16.    Loop
  17. End Function
I missed something but I don't know what it is. Can you guys tell me what is wrong.

Thanks in Advance
Aug 13 '08 #8

P: 26
i'm still trying my luck and amend my previous code to solved the error and I end up into this code. "CashierOrder" is a MS Access query
Expand|Select|Wrap|Line Numbers
  1. Public Sub GetTotalAmt()
  2.    Dim currDB As Database
  3.    Dim rstDAO As DAO.Recordset
  4.  
  5.    Set currDB = CurrentDb()
  6.    Set rstDAO = currDB.OpenRecordset("CashierOrder", dbOpenSnapshot, dbForwardOnly) '<- this line cause error 
  7.  
  8.    Do While Not rstDAO.EOF
  9.       MsgBox rstDAO![sum_amt]
  10.       rstDAO.MoveNext
  11.  
  12.    Loop
  13. End Sub
but still i got error message "Too few parameters. Expected 3. [3061]" but i already have 3 parameters when using OpenRecordSer()

pls help....

thanks in advance
Aug 13 '08 #9

P: 26
Hi Adezii,

Let's say your query was save in MS Access, then you use this query for your OpenRecordSet(). Question, how can I pass the parameter in this saved query? Can you show the syntax.

Thanks in Advance.
Aug 13 '08 #10

ADezii
Expert 5K+
P: 8,597
Thanks ADezii,

I got so hung up on the parameter/recordset discussion totally forgot about the other part!
Not a problem, you had the tough part! (LOL).
Aug 13 '08 #11

ADezii
Expert 5K+
P: 8,597
I combined Jarden & ADezil answers and I end up with this function

Public Function GetTotalAmt() As Long
Dim currDB As Database
Dim rstDAO As DAO.Recordset
Dim qryDef As QueryDef
Dim strQry As String

Set currDB = CurrentDb()
Set strQry = "CashierOrder" '<---- got error here saying "Compile error: Object required"
Set qryDef = currDB.QueryDef(strQry)
Set rstDAO = qryDef.OpenRecordset()

Do While Not rstDAO.EOF
MsgBox rstDAO![sum_amt]
rstDAO.MoveNext

Loop
End Function


I missed something but I don't know what it is. Can you guys tell me what is wrong.

Thanks in Advance
strQry is not an Object Variable, so you do not need the Set Statement:
Expand|Select|Wrap|Line Numbers
  1. strQry = "CashierOrder"
Aug 13 '08 #12

Expert Mod 2.5K+
P: 2,545
Hi. Please do not post duplicate or near-duplicate threads (see thread linked here)- it wastes the time of the expert volunteers who staff this site.

I have posted a response in the other thread which may shed some light on apparent confusion in your most recent code above between querydef and recordset objects. If you want to open a recordset you don't need the querydef object to do so. Ahh, as per Adezii's post you are using the querydef to pass a parameter -but you seem to have supplied the wrong arguments to OpenRecordset in the process.

It is clear from your posts that you are confusing the arguments supplied to the OpenRecordset method with the error message 'too few parameters - expected 3'. The error message refers to the recordset itself - the query you are trying to open, not the OpenRecordset call. You do not need to provide the optional recordset types as arguments to the OpenRecordset method.

The 'expected X' failure can arise when there is confusion between ADO and DAO recordsets, but as you have dealt with this as per Janders468's recommendation, another potential reason for such a failure relates to the use of form control references in the WHERE clause of the query (used to filter the results of the query by a value on a form, say). Adezii has metioned these.

Such WHERE clause references to form fields can work fine when the query is opened in the Access query editor but not be recognised as valid by the database engine when passed to the OpenRecordset method. Check to see if this is potentially the source of your problem. If this is the case, Adezii solution can be tried (properly!) or you can substitute the use of a custom VBA function to supply the value of the form field reference in place of the direct reference in the SQL. I can advise further on this if need be.

-Stewart
Aug 13 '08 #13

P: 26
Thanks Adezzi....

Sorry Stewart
Aug 13 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.