Is It Possible To Pass Parameters to A Pass Through Query

My assumption is that's it's not. At work I use ODBC to connect to our 
oracle database with Access 2003. There are cases where using a pass through 
query runs much much faster and I then use it in a make table query to make 
a local table. In access you can use brackets [] to have it ask for input. 
May I assume there is no way to do anything like that in a pass through 
query? 


-1
mcl
9/11/2007 10:13:41 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
4035 Views

Similar Articles

[PageSpeed] 23

Create a PassThrough query and use code to assign the SQL to this query 
filtering it with a parameter, and then run it

e.g
Dim MyVariable As Integer
MyVariable = InputBox("Please select a Number")
CurrentDb.QueryDefs("QueryName").SQL="Select * From TableName Where 
FieldName =" & MyVariable


-- 
Good Luck
BS"D


"mcl" wrote:

> My assumption is that's it's not. At work I use ODBC to connect to our 
> oracle database with Access 2003. There are cases where using a pass through 
> query runs much much faster and I then use it in a make table query to make 
> a local table. In access you can use brackets [] to have it ask for input. 
> May I assume there is no way to do anything like that in a pass through 
> query? 
> 
> 
> 
0
Utf
9/11/2007 11:12:01 PM
mcl wrote:
> My assumption is that's it's not. At work I use ODBC to connect to our
> oracle database with Access 2003. There are cases where using a pass
> through query runs much much faster and I then use it in a make table
> query to make a local table. In access you can use brackets [] to
> have it ask for input. May I assume there is no way to do anything
> like that in a pass through query?

Not in the same way no.  You would have to prompt for the parameters, store them 
in variables and then use them to re-write the SQL of the pass-through query. 
Sounds harder than it is.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


2
Rick
9/11/2007 11:15:35 PM
Ok, how do I do all that?
Here's the pass through query which I named sfc_obs_pass_through

Select BLKSTN,LATITUDE ,LONGITUDE ,CALLLETTER, OBSERVATIONTIME,WINDDIRECTION 
,WINDSPEED ,CLOUDCEILING ,CLOUDCAVOK ,VISIBILITY ,AIRTEMPERATURE 
,DEWPOINTTEMPERATURE ,SEALEVELPRESSURE ,PRECIPAMOUNT1, OBSERVATIONPERIODPP1, 
PRECIPAMOUNT2, OBSERVATIONPERIODPP2 ,PASTMANUAL1 ,PASTMANUAL2 
,WXPASTPERIOD1, PASTAUTOMATED1, PRESENTMANUAL1, PRESENTMANUAL2, CLOUDCOVER 
,ALTIMETERSETTING ,STATIONPRESSURE, WINDGUSTSPEED  from SFC_OBS where BLKSTN 
= 723150 order by OBSERVATIONTIME;

I want to prompt for "BLKSTN"  (Block Station Number) which is a six digit 
code identifying weather stations around the world. BTW, the one hard coded 
in above "723150" is for Asheville NC.


"Ofer Cohen" <OferCohen@discussions.microsoft.com> wrote in message 
news:6A65FF22-9F88-49FC-B021-B40F55D9D44B@microsoft.com...
> Create a PassThrough query and use code to assign the SQL to this query
> filtering it with a parameter, and then run it
>
> e.g
> Dim MyVariable As Integer
> MyVariable = InputBox("Please select a Number")
> CurrentDb.QueryDefs("QueryName").SQL="Select * From TableName Where
> FieldName =" & MyVariable
>
>
> -- 
> Good Luck
> BS"D
>
>
> "mcl" wrote:
>
>> My assumption is that's it's not. At work I use ODBC to connect to our
>> oracle database with Access 2003. There are cases where using a pass 
>> through
>> query runs much much faster and I then use it in a make table query to 
>> make
>> a local table. In access you can use brackets [] to have it ask for 
>> input.
>> May I assume there is no way to do anything like that in a pass through
>> query?
>>
>>
>> 


0
mcl
9/21/2007 12:15:14 AM
Reply:

Similar Artilces:

Parameter Query Help
I have a table that contains a filed called "Accnt_Code", the data in this field is from 1A to 9Z, what I need to do is create a parameter quesy, where the user may input, for example a group of Accnt_Codes, for example 1A to 1Z, and also be able to input individual Accnt_Codes, for example 2A,3B,4G - it this possible in access? You cannot do that with a parameter query. You can do it by building a Filter string in code, and applying it to a form or report. Or you can build the entire SQL statement as a string, and assign it to th SQL property of the QueryDef. -- Allen Bro...

Is it possible
I would like to know is it possible to make million dollar in the internet, if you have any suggestions, write on my forumhttp://isitpossible.truemillion.comMark...

Personal Folders in OWA
Hello, I have just enabled OWA on our Exchange 2003 SP2 through ISA Server 2004. Is there any way to access personal folders in OWA? Thanks in advance. If by personal folders you mean .pst files, the answer is NO! Sorry. C C wrote: > Hello, > > I have just enabled OWA on our Exchange 2003 SP2 > through ISA Server 2004. Is there any way to > access personal folders in OWA? > > Thanks in advance. > > "C C" <someone@atsbcglobal.net> wrote: >I have just enabled OWA on our Exchange 2003 SP2 >through ISA Server 2004. Is there any way...

Downloading Statements Directly Not Possible
I have Money 2002 installed on an Win XP Pro SP1 machine. Something happened and I probably did it, that causes the statement downloads not to go directly to the account. Rather they want to be saved in a file and then opened into Money. I then have to delete the download. I have tried several things in order to fix this: 1. I verified the file associations of OFC, OFX were correct. They were. 2. Lowered all my security settings in IE. Even put banks into Trusted site zone. No go. 3. Made new accounts. Didn't help. 4. My IE cache is large (500MB) and regularly cleaned out. 5. I made s...

A better way of constructing a LINQ query
Hi folks, I�ve got a LINQ query that filters out items in a database that begin with a number. It looks like this: char[] numbers = new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' }; var result = (from i in Items where numbers.Contains(i.Name[0]) orderby i.Name select i).ToList(); Now this works, but I�m sure there must be a more efficient way of doing this. Can anyone suggest a way that I can do it in a more efficient manner? Cheers, -- Dylan Parry What about: var r...

is this possible? reload a form that is based on a query with new query that is defined in the form.
I have a basic form. The form is based on a query, so when I open the form, a pop comes up where I enter in what I want to query. So instead of 30,0000 records, I get 1-10 records. on the form, I have a text box. I want to be able to enter in what I want to query next and afterupdate have the recordset/form reload with that query instead of my original qeury. I figure this has to be done with VB. I am just starting to learn some VB but I am lost. Any info would be great, thank you. It does. You need to apply the new query or table name, or the sql string to the forms recordsource, then...

unrelated table in a query
I am using a query with several tables, linked with one to many relationships. There is one table in there that is not related to any other tables. I contains a single record and I use a form on opening the database to set parameters on it (start date and end date). I use this table to set conditions on the query, then I use the query for about 10 reports. I haven't had any problems with the query (although the reports run slow). Is there any reason I should not set it up this way? I have to start a new project and would like to repeat the logic if it is not flawed. > I u...

Possible to detect changes to an XmlDocument object?
If I instantiate an XmlDocument object, the load a file through the Load method, is there a way, later on in execution, to easily determine if the XmlDocument object has changed from the original version loaded? I'd like to be able to check a condition to see if the document in memory has been editted (i.e., nodes added, attributes changed, etc.) so that my user can decide whether or not to commit changes to the disk file before moving to another file. Will I have to resort to re-loading the file, and comparing OuterXml properties? Gabe Gabe Covert wrote: > I'd like t...

You will pass all the exams at 1st attempt No more headaches and worry about spending the time and money to attempt another exam again. You will pass all of your exams, every time www.FreeExamKing.com
You will pass all the exams at 1st attempt No more headaches and worry about spending the time and money to attempt another exam again. You will pass all of your exams, every time www.FreeExamKing.com ...

Query ask for parameter value?
Hi, I biult a query from another one. When I run the query, Access keep ask the value of M and F. When I click OK I get the result that I want. Is there a way to tell Access not to display dialog asking for value of M and F? SELECT NumberOfCitizenSpeakingByQuarter.Quaters, Sum(NumberOfCitizenSpeakingByQuarter.[Meeting Observed]) AS [Meeting Observed], Sum(NumberOfCitizenSpeakingByQuarter.Male) AS M, Sum(NumberOfCitizenSpeakingByQuarter.Female) AS F, [M]/[Meeting Observed] AS [Avg Male], [F]/[Meeting Observed] AS [Avg Female] FROM NumberOfCitizenSpeakingByQuarter GROUP BY Nu...

Export Access Data Project Queries to old Access .mdb file
I want to copy Access Data Project queries from a project into a new .mdb file. What is the best way to do this? In the new .mdb go to the menu bar, click file, Get external data, and Import. Select the orignal .mdb file and then the items. >-----Original Message----- >I want to copy Access Data Project queries from a project into a new .mdb >file. What is the best way to do this? >. > ...

Is it possible to compare a string and a field name
I have the following tables: Membership, Subscriptions, OfficeUseOnly. The Office Table contains a Year, Year To and From [financial year] and the other fields are the 5 membership types (regular, student etc) and these 5 fields contain the different amount each type of member has to pay. I want to create a form whereby on entering the financial year and the membership type the correct amount is automatically inserted into the 'Amount' field: FinancialYear - in current form = OfficeUseOnly SubsFinYear and then if MemberType = 'Student' find Student field ...

pictures in various shapes
Is it possible to insert a picture and make it into a circle or an oval or various other shapes than just squares and rectangles? Thank you ahead of time. with a graphics editor -- Rob Giordano Microsoft MVP - FrontPage "Stephanie" <Stephanie@discussions.microsoft.com> wrote in message news:014D0D06-15F9-4D92-8D93-64FBD3A9CA32@microsoft.com... | Is it possible to insert a picture and make it into a circle or an oval or | various other shapes than just squares and rectangles? Thank you ahead of | time. | | Stephanie wrote: > Is it possible to insert a pict...

need help with msquery query
i am using access 2000 database and an excel 2000 spreadsheet. the access data base also has linked tables to a SQLAnywhere database. the data that i need to pull comes from both a native access table and a linked table to the SQLAnywhere database. when just pulling data from the access table, using parameters to limit the data, i get the data that i selected. when i create a new query using both the data from the access table and the SQLAnywhere table, i am getting way too much data. The parameters from the msquery seem to not be working. can someone help me with this? ...

Query Formula (stones and pounds to kg's)
I have a table with 2 weight fields, stones and pounds. Is there a formula that I could use to convert this into a single kg field. I would be doing this in a query There are many answers on google but they seem all to be for excel. Thank you not perfectly clear but can't you just add the two converted values? [Kg] field source =[stone]* 6.35 + [Lbs] * 2.2 "Karren Lorr" wrote: > I have a table with 2 weight fields, stones and pounds. > Is there a formula that I could use to convert this into a single kg field. > > I would be doing this in a que...

strange web query behavior
(Using Excel 2003, XP Pro, SP2) The other day I wanted to import data from a website, and accessed Data > Import External Data > New Web Query. The dialog box which opened up already had my hotmail website page entered and was already opening that page. The drop-down address bar also included a Nikon site which I recall visiting only once. I have never imported data from either site in the past, and the only website I'd ever imported data from was not listed on the drop-down address bar. I had to delete the hotmail address and enter the URL of the site I wanted to import f...

Crossing out a day once it has passed on the calendar
How can i cross out a day on the calender after that day is over with. Thanks "john" <jnutella@hotmail.com> wrote in message news:Rm4Ah.14957$4Q2.13975@read1.cgocable.net... > How can i cross out a day on the calender after that day is over with. > > Thanks > > > You could take a permanent marker. After a few days, you would have to buy a new monitor. Really, Outlook highlights the current day. Everything to the left is "over." "Chuck Davis" <newsgroup at anthemwebs dot com> wrote in message news:%23p%23qOhuTHHA.192@TK2...

Is this possible
I want to make a list of the files in several different folders. Is it possible to somehow "Copy and paste" just the filenames into a list, in either Word or Excel or Wordperfect. I am just trying to sort the files in several different folders to weed out duplicates. There has got to be an easier way then typing them all or screen printing the lists and manually weeding them out. Any suggestions. Frustrated... Hans, Here is some code that will start in a directory and list all files in that and any sub directories. It uses recursion to go through all the subs. Just ch...

Is this Possible ?
Hi Have a newsletter document with text boxes that have no outlines but internal gradient colour fills. Is it possible that a macro could remove all colour fills in one hit just leaving the text in place ? many thanks Dim oShape As Shape For Each oShape In ActiveDocument.Shapes If oShape.Type = msoTextBox Then If oShape.Line.Visible = msoFalse Then oShape.Fill.Visible = msoFalse End If End If Next oShape should clear the fill from text boxes that are filled and have no border. -- <>>< ><<> ><&l...

Query combined with formula
Hi, I have made a query, that gets the parameter from my cell A11. There is always only one result, which is written i cell B11. Now i wish to copy that formula from A11 to the area A12:A3000, so it picks up the parameter from the row, where the formula is placed. The result should be, that a query request data based on A11 and spit it out in B11; another query uses A12 and spits out in B12 (...) Now I need it for now in 3000 rows, and that might expande drasticly over time, so how do I easy copy it down? Regular copy/paste only copy the formula or value of the cells, not the queries... ...

Is Batch Marking Possible?
Hello, I am doing a mass mailing from Outlook. I will print 50 address labels per day. Is there a way for me to batch-mark the contacts for which I print a label? Thanks, Talal Itani ...

Is it possible to make cmd window to flash?
Greetings, I have an existing VC2003 windows console program. Now I want to add a alarm feature to it, the simpliest way is to make the cmd window to flash. Can this be implemented? Thanks, Evan >I have an existing VC2003 windows console program. Now I want to add a alarm >feature to it, the simpliest way is to make the cmd window to flash. Can >this be implemented? Evan, If the console is running in a window, I presume you can use the FlashWindow(Ex) API. In newer OS's you can find the console window handle using the GetConsoleWindow API. For older OS's here's...

MS Query convert text to #
I am querying a data base that is returning a number as text. Is there a way to convert the text to an actual number in the query, before it is returned to excel??? Thanks to everyone that replies. In Microsoft Query, you can create a calculated field. For example, if the field name is ItemCode: Click in a blank column heading Type an expression to convert the text to a number, e.g.: ItemCode+0 or CLng(ItemCode) Return the data to Excel Shane wrote: > I am querying a data base that is returning a number as > text. Is there a way to convert the text to an actua...

Form Variable no longer passes to new form
Button Command39 on main form, when clicked opens the SWLicensesForm. Button WinXP_Click() on the SWLicensesform, when clicked adds a new record and fills in some fields for me. My problem is in the POTEMP and NuaNumTmp variables being passed from the main form to th SwLicensesform.. These are no longer being passed from the main form to the SWLicensesform, and I can not fiure out why. I have a backup of the database that is about 8 days old that works fine. The only thing I have done between the backup and the variables not working is to compact the database. There are no error messages, ...

How is possible?
I have performed the regular procedure in order to unistall outlook express. In the windows installed programs look not present but outlook express is still there and working. I cannot understand how this is possible. Would you anybody explain me? Thanks and regards Hello Salicilico, you wrote on Thu, 25 Aug 2005 20:21:32 GMT: > I have performed the regular procedure in order to unistall outlook express. > In the windows installed programs look not present but outlook express is > still there and working. > I cannot understand how this is possible. > Would you anybody expl...