|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
June 16th, 2003, 01:56 AM
|
|||
|
|||
|
Calling Access query with parameters from another Query
It may sound like a stupid thing to want to do but I'm trying to call a Access Database query from another query and also pass the second query a paramter, which is possibly a parameter to the first.
I don't want a solution for any other type of database. I'm interested in the limitations of Access only. If i have a query Code:
select name from tblperson where id = [x] I can type in x when propted by access when run through Access gui. I can also use VB or ASP and ADO to run a command which calls using "exec query" and setting a parameter for "x" But in a query you are not able to call "exec" Does anybody know a pure Access sql solution to this problem, I don't even want to use a access/VBA module.
__________________
-- ngibsonau
|
|
#2
June 16th, 2003, 09:44 AM
|
||||
|
||||
|
if the first query is in the same DB as the second DB, it should be simple.
Just type Select Query1.fieldName from Query1; forgot how to make it ask u for a parameter..
|
|
#3
June 16th, 2003, 10:28 AM
|
|||
|
|||
|
I'm not 100% clear on what you want to do, but it seems to me you are trying to use a parameter (possibly several times) without being prompted for the parameter or writing code.
One work-around I have used is to create a table or view which returns one record to store the parameter. An update statement can then set the parameter, and then by joining that table or view into your query you can easily apply that parameter to any queries you need to run.
|
|
#4
June 16th, 2003, 08:23 PM
|
|||
|
|||
|
That sounds like the best work around I have heard so far.
I'll give it a try. It's a shame Access Queries seem to be crippled in that you cant pass them parameters no matter where you call them from. I'm still open to any other suggestions. Here is a little more detail so everbody is on the same wavelength If you call the query I gave before the name qn (here again for convenience) Query: qn Code:
select name from tblperson where id = [x] and try to call it from a query named q1, how do I pass a value for [x] in qn? Query:q1 Code:
SELECT [name] FROM qn; With the above query the user interface asks for x to be input the following don't work, where I'm trying to give x the value of 4 Code:
SELECT [name] FROM qn(4); The above fails i nthe where clause Code:
SELECT [name] FROM qn[4]; The above fails i nthe where clause also Code:
SELECT [name] FROM qn where qn.x =4; The above ask for input for x and qn.x via the user interface. Code:
exec qn 4 and Code:
exec qn(4) both fail because exec is not allowed. Finally what I really want to be able to do is pass the value of the parameter [y] from the first query to qn as [x] Code:
SELECT [name] FROM qn([y]); I will post code code that emulates the process as suggested by dschroth using tempory tables etc. when I get a chance. But again don't let that stop anybody from doing the same.
|
|
#5
June 16th, 2003, 09:33 PM
|
|||
|
|||
|
I guess you also know about MSDE, which is basically a free version of MS SQL that Access can use as a backend database. If you use an MSDE database you can do what you want with a stored procedure and EXEC.
|
|
#6
June 16th, 2003, 10:19 PM
|
|||
|
|||
|
Take a look at my first post. I know about other databases, I'm only interested in the cababilities or lack thereof Access and the jet engine, in so much as what I can do in SQL in an access MDB without any non SQL programming, no VBA, no MySql, no Oracle, no stored procedures (unless you can implement a stored procedure in ACCESS). I can do what I want in Access with about ten lines of VB code, but thats not the point.
Which brings up an interesting point.( To give this post a purpose, more than a plain flame) In access you can go Code:
PARAMETERS x IEEEDouble; SELECT [tblname].[name] FROM tblname WHERE id=x; which seems equivalent to Code:
select name from tblname where id = [x]; as far as I can tell except what looks like a slightly more type safe input??? Can anybody give me more clues about this syntax? is it old and outdate/undocumented? To complement this there is also Code:
PROCEDURE qaa; select name from tblname where id = [x]; Does the procedure statement and its parameter qaa actually do anything? For me the query name is given when saving the query in the access gui which doesn't have to be the same as that given in the query body? Does anybody know how to use this properly. I can't seem to mix the PARAMETERS and PROCEDURE statements and get a query that works!
|
|
#7
September 30th, 2003, 10:30 AM
|
|||
|
|||
|
ok this is way too simple but here goes..
Look at this sample function from one of my vbscripts. function SelectJob() Dim MyConn, SQL, RS Set MyConn=CreateObject("ADODB.Connection") MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=d:\sample.mdb" Rjobno = InputBox("Job Number To Display?",JobNo) SQL = "Delete * From Requests Where Jobno='"&Rjobno&"'" Set RS=MyConn.Execute(SQL) Alert("Record Deleted") end function Rjobno = InputBox("Job Number To Display?",JobNo) this prompts the user for a jobnumber SQL = "Delete * From Requests Where Jobno='"&Rjobno&"'" this applies the variable Rjobno to the sql query so this function prompts the user for a jobnumber then builds and executes the sql to delete the corresponding row from the Requests table in the database d:\sample.mdb. Jim Wildman
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Calling Access query with parameters from another Query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
