How to access a query inside MS Access program | Newbie | | Join Date: Aug 2008
Posts: 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 - ...
-
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
| | Expert | | Join Date: Mar 2008 Location: Firestone, CO
Posts: 112
| | | re: How to access a query inside MS Access program
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
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?).
| | Newbie | | Join Date: Aug 2008
Posts: 26
| | | re: How to access a query inside MS Access program
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 Quote:
Originally Posted by 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...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
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?). | | Expert | | Join Date: Mar 2008 Location: Firestone, CO
Posts: 112
| | | re: How to access a query inside MS Access program
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.
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: How to access a query inside MS Access program
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
| | Expert | | Join Date: Mar 2008 Location: Firestone, CO
Posts: 112
| | | re: How to access a query inside MS Access program
Thanks ADezii,
I got so hung up on the parameter/recordset discussion totally forgot about the other part!
| | Newbie | | Join Date: Aug 2008
Posts: 26
| | | re: How to access a query inside MS Access program
thank you very much, everybody here is so helpful. I'll tried this example later becuase I got urgent work to test....thanks again
| | Newbie | | Join Date: Aug 2008
Posts: 26
| | | re: How to access a query inside MS Access program
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
| | Newbie | | Join Date: Aug 2008
Posts: 26
| | | re: How to access a query inside MS Access program
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 - 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
| | Newbie | | Join Date: Aug 2008
Posts: 26
| | | re: How to access a query inside MS Access program
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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: How to access a query inside MS Access program
Quote:
Originally Posted by janders468 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).
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: How to access a query inside MS Access program
Quote:
Originally Posted by 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 strQry is not an Object Variable, so you do not need the Set Statement: | | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: How to access a query inside MS Access program
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
| | Newbie | | Join Date: Aug 2008
Posts: 26
| | | re: How to access a query inside MS Access program
Thanks Adezzi....
Sorry Stewart
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,327 network members.
|
