Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 11th, 2003, 12:03 AM
Authorized User
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default passing parameter to query

Hi everyone,

I want to pass a combo box parameter to a query inside a for...next loop.

My code is:

 For i = 1 To cboDept.ListCount
        With DoCmd
            .OpenQuery "qryObsvnClassOcurrences"
        End With
    Next i

The combo box is located on a form and the code is behind one of the buttons' OnClick event. There are 11 parameters in the combo box. My question is where can i insert the cboDept.itemdata value in this loop so that the query executes with different parameter each time ?


Sydney, Australia
Old December 11th, 2003, 09:33 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

Actually, you don't... not with the OpenQuery method. What you do is use the DoCmd.RunSQL method. Open "qryObsvnClassOcurrences" with no criteria and look at it in SQL view. Copy and paste the SQL string you see and place it after the DoCmd.RunSQL statement in your code. Enter your criteria in the string using proper syntax.

For example, if your query lists everything in last name order, your SQL view shows merely "SELECT * FROM qryObsvnClassOcurrences ORDER BY strLastName;" Then your code can look like this:
   With DoCmd
      .RunSQL "SELECT * FROM qryObsvnClassOcurrences" & _ 
              " WHERE strDept = '" & Me.cboDept.itemdata & "'" & _
              " ORDER BY strLastName;"
   End With
   Note the spaces before the WHERE and the ORDER BY.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Old December 11th, 2003, 07:12 PM
Friend of Wrox
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts

Doesn't DoCmd.RunSQL only work with Action queries (insert, delete, update)?
Because you're actually running SQL, not viewing something.


I am a loud man with a very large hat. This means I am in charge
Old December 11th, 2003, 07:15 PM
Authorized User
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts

My query is actually Crosstab. I am about to try the method Serrano sent me earlier and see if it works.

Old December 11th, 2003, 09:54 PM
Authorized User
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts

OK, i made some modifications. I can't use the combo box to pass parameters, because it only sends 1 to the query. Instead, i run a loop through a table to run for each value (11 in total). I want to create a temp querydef to filter data on 1 of the values, then export the results to Excel s/sheet. Each time it exports, the query should have a different name, to create a different worksheet in the file. In order to use TrasferSSheet function, as i understand, i have to physically create a query before i export it. My code is below. It's running, but nothing gets transferred to Excel. I'm not sure what is wrong. Any suggestions welcome.


Set mydb = CurrentDb()
Set mytbl = mydb.OpenRecordset("tblDepartment")

On Error Resume Next ' Defer error trapping.

Do Until mytbl.EOF = True
    strDept = mytbl![Dept]
    Set qdfTemp = mydb.CreateQueryDef(strDept, "TRANSFORM Count(tblObsSafety.ObsvnTypeLUp) AS Occurrences" & _
                "SELECT tblObsSafety.ObsvnTypeLUp, tblObsSafety.SafetyType" & _
                "FROM tblObservation INNER JOIN tblObsSafety ON tblObservation.Obsvn = tblObsSafety.Obsvn" & _
                " WHERE (((tblObservation.Department) = strDept " & _
                "GROUP BY tblObsSafety.ObsvnTypeLUp, tblObsSafety.SafetyType" & _
                "ORDER BY Format([DatComp],""mm/yyyy"")" & _
                " PIVOT Format([DatComp],""mm/yyyy"");")

    With DoCmd
        .TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strDept, "C:\HMSafeDb\HMSafeGraphs.xls"
        .close acQuery, strDept
    End With

Old August 30th, 2006, 05:22 AM
Registered User
Join Date: Aug 2006
Location: Delhi, Delhi, India.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

Total 3 way to interact with linked server through OpenQuery

1. Directly (Not good from performance point)
   Description: Queries run locally and not on the remote servers, which means that the OLE layer may draw the needed row sets across the link to be processed. This can be a performance nightmare with large remote objects.

2. Thru OPenQuery ( good but syntax is difficult..)
   Description : This function allows queries to pass through and execute on the linked server.
    Syntax : OPENQUERY(linkname, 'SQL statement')
    Variables cannot be passed into the OPENQUERY function,so the SQL
 statement needs to be constructed as a string and then passed into
 the stored procedure sp_executesql as the SQL statement parameter,thats the 3rd type.

3. thru Stored procedure(Good from performace point & also from other aspect)

/* 1st way : Directly*/

/* Select Cmd */
SELECT Uid, Pwd FROM DD.Test.dbo.Login

/* Select Cmd with where clause */
SELECT Uid, Pwd FROM DD.Test.dbo.Login where Uid='Admin'

/* Insert Cmd */
Insert Into DD.Test.dbo.Login Values('AAA','123')

/* Update Query */
update DD.Test.dbo.Login set Pwd ='111' where Pwd='abc'

/* Update query thtu 1nd way (update data come from the local datatable )*/

UPDATE DD.Test.dbo.Login SET Pwd = a.Pwd
   FROM Login1 a ,DD.Test.dbo.Login b
   WHERE a.uid = b.uid

/* delete also work on the same way as update in the above example*/

************************************************** ***************
/* 2nd way(OpenQuery) */

/* Select Query without where clase */

/* Select Query with where clase */
SELECT * FROM OPENQUERY(DD,'SELECT * FROM Login') where Uid = 'Admin'
SELECT * FROM OPENQUERY(DD,'SELECT * FROM Login where Uid = ''Admin''')

/* Insert Query */

/* Update Query */
i tried but Update & Delete not work with openQuery
Here is the link for ur help that may help you to delete or update thru OPenQuery.

UPDATE Openquery(DD1,'SELECT * FROM Emp WHERE EmpID = 1') set EmpID = EmpID + 1


/* Delete Query */

Delete OPENQUERY (DD1, 'SELECT * FROM Emp where EmpID=2')

The SQL Server OLE DB provider requires that a unique index exist on the underlying table
for UPDATE or DELETE operations. If no unique index exists on a remote table, the following
error occurs when an UPDATE or DELETE is attempted: Server: Msg 7320, Level 16, State 2, Line
1 Could not execute query against OLE DB provider 'SQLOLEDB'. The provider could not support
a required row lookup interface. The provider indicates that conflicts occurred with other
properties or requirements. [OLE/DB provider returned message: Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work was done.
This applies both to OpenQuery and four-part named UPDATE and DELETE operations. The problem
is resolved by adding a unique index on the remote table.

   ************************************************** ******************

/* 3rd Type Stored procedure */

I tried lot to pass parameter in the OpenQuery but m not able to pass the parameter.
This is the only way(i think) to pass parameter

DECLARE @TSQL varchar(8000), @VAR char(5),@VAR1 char(5)
SELECT @VAR1 = 'vv34'
SELECT @VAR = 'Admin'
SELECT @TSQL = 'SELECT * FROM OPENQUERY(DD,''SELECT * FROM Login WHERE pwd = ''''' + @VAR1 + ''''' AND Uid = ''''' + @VAR + ''''''')'

Posted By....
Dinesh Kumar...Zed-axis Technologies
Contact @: 9350534252 for future query

Dinesh Kumar
( 9350534252 )
Old August 30th, 2006, 05:39 AM
Registered User
Join Date: Aug 2006
Location: Delhi, Delhi, India.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

hi All

in continuation .....

Sorry ..m forgot to mention abt something in my last post..so here m mentioning all those things..

DD is my linked server
DD1 is any another linked server ...refer to MSDN.com to know how to create the Linked Server Instance in your Enterprise Manager.

Here i took :
   "Test" as my Database
   "Login" as my Table in "Test Database"

Good Luck !!!

Dinesh Kumar
( 9350534252 )

Similar Threads
Thread Thread Starter Forum Replies Last Post
passing a parameter to an Access query! pankaj_daga Access VBA 6 November 16th, 2007 12:26 PM
Passing function parameter to query kar22 Access 1 February 27th, 2007 08:28 AM
Passing an Input Field to a Parameter Query stcraig BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 4 November 6th, 2006 06:06 PM
Passing parameter to Access query eapsokha Classic ASP Databases 2 September 16th, 2004 02:49 AM
Passing Parameter tgopal Javascript 3 August 7th, 2003 07:54 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.