How to access a query inside MS Access program

[EVH316]

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

...
Dim MyDb As Database
Dim rstQry As Recordset

strQry = "CashierOrder"    ' my MS Access Query

Set rstQry = MyDb.OpenRecordset(strQry)   ' fails here saying Too few parameters. Expected 2.[3061]
...

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

this is the select statement of my query

SELECT Format$(DSum("sumofamount","subqry_CashierOrder"),"#.00") AS Expr3
FROM dbo_SALFLDGDSN INNER JOIN dbo_qrySSRFADDDSN ON dbo_SALFLDGDSN.ACCNT_CODE = dbo_qrySSRFADDDSN.ADD_CODE
WHERE (((dbo_SALFLDGDSN.D_C)="C"))
GROUP BY dbo_SALFLDGDSN.PERIOD, Trim([ACCNT_CODE]), dbo_SALFLDGDSN.JRNAL_NO
HAVING (((dbo_SALFLDGDSN.PERIOD)=SunToSqlServerPeriod([Forms]![frmPayInvFileGen]![txtRepPeriod])) AND ((Trim([ACCNT_CODE])) Like "9*") AND ((dbo_SALFLDGDSN.JRNAL_NO)=[Forms]![frmPayInvFileGen]![txtJrnlNo]))
ORDER BY Trim([ACCNT_CODE])

Thanks in advance

[janders468]

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.aspx?scid=kb;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

Dim rst as DAO.RecordSet

or

Dim rst as ADODB.RecordSet

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?).

[EVH316]

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.aspx?scid=kb;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

Dim rst as DAO.RecordSet

or

Dim rst as ADODB.RecordSet

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?).

[janders468]

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.

    Dim strQuery As String
    Dim qd As QueryDef
    Dim daoRst As DAO.Recordset
    strQuery = YourQuery
    Set qd = CurrentDb.QueryDefs(strQuery)
    qd![Test] = PutTheValueofTheControlHere
    Set daoRst = qd.OpenRecordset()

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

     Dim varItem as Variant
     varItem =  rs(0)

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

Do
   'Do something with the value here, such as write to a text file
   rs(0)
   rs.MoveNext
Loop until rs.Eof

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.

[ADezii]

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:

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
Dim intFileNum As Integer

strSQL = "SELECT Employees.LastName, Employees.FirstName, Employees.City FROM " & _
         "Employees ORDER BY Employees.LastName;"

intFileNum = FreeFile

Set MyDB = CurrentDb()

Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

Open "C:\QueryResults.txt" For Output As #intFileNum

Do While Not MyRS.EOF
  Print #intFileNum, MyRS![LastName]; Tab(20); MyRS![FirstName]; Tab(35); MyRS![City]
  MyRS.MoveNext
Loop

MyRS.Close
Set MyRS = Nothing

Close #intFileNum

OUTPUT ==> C:\QueryResults .txt

Buchanan           Steven         London
Callahan           Laura          Seattle
Davolio            Nancy          Seattle
Dodsworth          Anne           London
Fuller             Andrew         Tacoma
King               Robert         London
Leverling          Janet          Kirkland
Peacock            Margaret       Redmond
Suyama             Michael        London

[janders468]

Thanks ADezii,

I got so hung up on the parameter/recordset discussion totally forgot about the other part!

[EVH316]

thank you very much, everybody here is so helpful. I'll tried this example later becuase I got urgent work to test....thanks again

[EVH316]

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

[EVH316]

i'm still trying my luck and amend my previous code to solved the error and I end up into this code. "CashierOrd er" is a MS Access query

Public Sub GetTotalAmt()
   Dim currDB As Database
   Dim rstDAO As DAO.Recordset
   
   Set currDB = CurrentDb()
   Set rstDAO = currDB.OpenRecordset("CashierOrder", dbOpenSnapshot, dbForwardOnly) '<- this line cause error 

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

   Loop
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

[EVH316]

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.

[ADezii]

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).

[ADezii]

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 = "CashierOrd er" '<---- got error here saying "Compile error: Object required"
Set qryDef = currDB.QueryDef (strQry)
Set rstDAO = qryDef.OpenReco rdset()

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:

strQry = "CashierOrder"

[Stewart Ross]

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

[EVH316]

Thanks Adezzi....

Sorry Stewart