Is it possible to prompt for input and then use that input in pass through query?

M

mcl

At work I use access with ODBC to connect to our oracle database. I'm a
meteorologist. I pull down data for a particular station and can crunch all
kinds of climate data for it. Anyway, to speed things up I use a pass
through query as the front end of a make table query. Normally I like to use
brackets [] with prompt to ask for a requested station number. In the
example below the "Block Station Number" (BLKSTN) is 388950. I have to edit
the pass through query to change the number. Is there any way I can prompt
for BLKSTN number using modules?

I must say I don't know VBA. The only thing I know about modules is it's one
of the object choices. I learned a few weeks ago that there is a compiler
after seeing something about it in these newsgroups. (So I would have to
compile anything before running it?).

Anyway, what would it take under modules? How would I have to edit my pass
through query to link to it? Would I have to do anything special to run it?
Compile it?

The last "basic" programming I ever did was in the early 80's with TI
Extended Basic on my old TI 99/4a computer.

Pass through query:
Select BLKSTN,LATITUDE ,LONGITUDE ,CALLLETTER, NETWORKTYPE, PLATFORMID,
OBSERVATIONTIME,WINDDIRECTION ,WINDSPEED ,WINDSPEEDQC,CLOUDCEILING
,CLOUDCAVOK ,VISIBILITY ,AIRTEMPERATURE,AIRTEMPERATUREQC
,DEWPOINTTEMPERATURE ,DEWPOINTTEMPERATUREQC,SEALEVELPRESSURE
,SEALEVELPRESSUREQC,PRECIPAMOUNT1, OBSERVATIONPERIODPP1, PRECIPAMOUNT2,
OBSERVATIONPERIODPP2 ,PASTMANUAL1 ,PASTMANUAL1QC,PASTMANUAL2
,PASTMANUAL2QC,WXPASTPERIOD1, PASTAUTOMATED1,PASTAUTOMATED1QC,
PRESENTMANUAL1,
PRESENTMANUAL1QC,PRESENTMANUAL2,PRESENTMANUAL2QC,PRESENTAUTOMATED,
PRESENTAUTOMATEDQC,CLOUDCOVER ,ALTIMETERSETTING ,ALTIMETERSETTINGQC,
STATIONPRESSURE, STATIONPRESSUREQC, WINDGUSTSPEED, WINDGUSTSPEEDQC, from
SFC_OBS where BLKSTN in(388950) order by OBSERVATIONTIME;


Append Table Query:
INSERT INTO clst_station ( BLKSTN, LATITUDE, LONGITUDE, NETWORKTYPE,
PLATFORMID, OBSERVATIONTIME, [Year], [Month], [Day], [Hour], [Minute],
WINDDIRECTION, wspdkts, WINDSPEEDQC, cighgtft, CLOUDCAVOK, vsbysmi, TempF,
AIRTEMPERATUREQC, DewpF, DEWPOINTTEMPERATUREQC, SEALEVELPRESSURE,
SEALEVELPRESSUREQC, prcp1in, OBSERVATIONPERIODPP1, prcp2in,
OBSERVATIONPERIODPP2, PASTMANUAL1, PASTMANUAL1QC, PASTMANUAL2,
PASTMANUAL2QC, WXPASTPERIOD1, PASTAUTOMATED1, PASTAUTOMATED1QC,
PRESENTMANUAL1, PRESENTMANUAL1QC, PRESENTMANUAL2, PRESENTMANUAL2QC,
PRESENTAUTOMATED, PRESENTAUTOMATEDQC, CLOUDCOVER, ALTIMETERSETTING,
ALTIMETERSETTINGQC, STATIONPRESSURE, STATIONPRESSUREQC, gustkts,
WINDGUSTSPEEDQC, sstC, sstF, e, es, RH, TIME_CONV ) SELECT
sfc_obs_pass_through.BLKSTN, sfc_obs_pass_through.LATITUDE,
sfc_obs_pass_through.LONGITUDE, sfc_obs_pass_through.NETWORKTYPE,
sfc_obs_pass_through.PLATFORMID, sfc_obs_pass_through.OBSERVATIONTIME,
Year([OBSERVATIONTIME]) AS [Year], Month([OBSERVATIONTIME]) AS [Month],
Day([OBSERVATIONTIME]) AS [Day], Hour([OBSERVATIONTIME]) AS [Hour],
Minute([OBSERVATIONTIME]) AS [Minute],
sfc_obs_pass_through.WINDDIRECTION, CDbl([WINDSPEED]*1.943844) AS wspdkts,
sfc_obs_pass_through.WINDSPEEDQC, CDbl([CLOUDCEILING]*3.28) AS cighgtft,
sfc_obs_pass_through.CLOUDCAVOK, CDbl([VISIBILITY]/1609) AS vsbysmi,
CDbl([AIRTEMPERATURE]*1.8+32) AS TempF,
sfc_obs_pass_through.AIRTEMPERATUREQC,
CDbl([DEWPOINTTEMPERATURE]*1.8+32) AS DewpF,
sfc_obs_pass_through.DEWPOINTTEMPERATUREQC,
sfc_obs_pass_through.SEALEVELPRESSURE,
sfc_obs_pass_through.SEALEVELPRESSUREQC, CDbl([PRECIPAMOUNT1]/25.4) AS
prcp1in, sfc_obs_pass_through.OBSERVATIONPERIODPP1,
CDbl([PRECIPAMOUNT2]/2.54) AS prcp2in,
sfc_obs_pass_through.OBSERVATIONPERIODPP2,
sfc_obs_pass_through.PASTMANUAL1, sfc_obs_pass_through.PASTMANUAL1QC,
sfc_obs_pass_through.PASTMANUAL2, sfc_obs_pass_through.PASTMANUAL2QC,
sfc_obs_pass_through.WXPASTPERIOD1, sfc_obs_pass_through.PASTAUTOMATED1,
sfc_obs_pass_through.PASTAUTOMATED1QC,
sfc_obs_pass_through.PRESENTMANUAL1,
sfc_obs_pass_through.PRESENTMANUAL1QC,
sfc_obs_pass_through.PRESENTMANUAL2,
sfc_obs_pass_through.PRESENTMANUAL2QC,
sfc_obs_pass_through.PRESENTAUTOMATED,
sfc_obs_pass_through.PRESENTAUTOMATEDQC,
sfc_obs_pass_through.CLOUDCOVER, sfc_obs_pass_through.ALTIMETERSETTING,
sfc_obs_pass_through.ALTIMETERSETTINGQC,
sfc_obs_pass_through.STATIONPRESSURE,
sfc_obs_pass_through.STATIONPRESSUREQC, CDbl([WINDGUSTSPEED]*1.943844) AS
gustkts, sfc_obs_pass_through.WINDGUSTSPEEDQC,
6.11*(10^((7.567*[DEWPOINTTEMPERATURE])/(239.7+[DEWPOINTTEMPERATURE])))
AS e, 6.11*(10^((7.567*[AIRTEMPERATURE])/(239.7+[AIRTEMPERATURE]))) AS es,
Round(100*[e]/[es],1) AS RH, MSC_ADMIN_WEATHER_STATION.TIME_CONV
FROM sfc_obs_pass_through INNER JOIN MSC_ADMIN_WEATHER_STATION ON
(sfc_obs_pass_through.PLATFORMID = MSC_ADMIN_WEATHER_STATION.PLATFORMID)
AND (sfc_obs_pass_through.NETWORKTYPE =
MSC_ADMIN_WEATHER_STATION.NETWORKTYPE) AND (sfc_obs_pass_through.BLKSTN =
MSC_ADMIN_WEATHER_STATION.BLKSTN) ORDER BY sfc_obs_pass_through.BLKSTN,
sfc_obs_pass_through.OBSERVATIONTIME;
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The easiest way to run pass-thru qrys w/ vairables is to place a stored
procedure on the SQL server (yeah, I know you're running Oracle, but all
servers that run SQL are called SQL servers. Clever Microsoft using the
generic name for its product). If you can't create a stored procedure
on the server 'cuz you don't have permission, then you have to set up a
template of your query and change it for every run.

Ex: I'll use a shorter query than yours for brevity's sake:

My qry (gets today's total sales for one store):

SELECT store_id, SUM(sales) As TodaySales
FROM Sales
WHERE store_id = XXXX
AND sales_date = CURRENT_TIMESTAMP
GROUP BY store_id

Here is a VBA routine (what goes in a Form's module [there are 2 types
of modules: Form modules and general modules) that reacts to the click
of a CommandButton named "Get Store Sales Totals":

Private Sub cmdGetStoreSales_Click()

CONST SQL = "SELECT store_id, SUM(sales) As TodaySales " & _
"FROM Sales " & _
"WHERE store_id = |1 " & _
"GROUP BY store_id"

Dim db AS DAO.Database, qd As DAO.QueryDef
Dim intStoreID As Integer
Dim strSQL As String

' Get the variables from the user
intStoreID = InputBox ("Store's ID Number? ")

' Put the variable in the T-SQL query
strSQL = Replace(SQL, "|1","intStoreID")

' Set up the pass-thru query
Set db = CurrentDB
Set qd = db.QueryDefs("PassThruQuery")
qd.SQL = strSQL

' Run the Append query
Set qd = db.QueryDefs("AppendQuery")
qd.Execute dbFailOnError

' Clean up
Set qd = nothing
Set db = nothing

End Sub

This procedure needs an error handling routine.

The query "PassThruQuery" is a stored query that has the above SQL
string (Constant SQL) in it. It must be set up ahead of time as a pass
thru query. Be sure to set the Connect property to the same connection
string as you use for the Oracle db.

If you have a large query, it will be difficult to place it in one VBA
Constant definition 'cuz you can only use X number of " & _" line
continuation symbols on one statement. You can store the text of the
query in a table and retrieve it like this:

strSQL = DLookup("SQL_string","SQLStrings","ID = 2")

- From a table that looks like this:

CREATE TABLE SQLStrings (
ID Counter PRIMARY KEY,
SQL_string Memo
);

A Counter is an AutoNumber.

You'll have to remember the ID for the different SQL strings you store
in the table.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR8ZC3oechKqOuFEgEQKxNQCePqM9fpdDcTVpa4xWKridDn31bpUAoKQf
UckV7FDYgkLmwNTF/5WKjaks
=TVWL
-----END PGP SIGNATURE-----

At work I use access with ODBC to connect to our oracle database. I'm a
meteorologist. I pull down data for a particular station and can crunch
all kinds of climate data for it. Anyway, to speed things up I use a
pass through query as the front end of a make table query. Normally I
like to use brackets [] with prompt to ask for a requested station
number. In the example below the "Block Station Number" (BLKSTN) is
388950. I have to edit the pass through query to change the number. Is
there any way I can prompt for BLKSTN number using modules?

I must say I don't know VBA. The only thing I know about modules is it's
one of the object choices. I learned a few weeks ago that there is a
compiler after seeing something about it in these newsgroups. (So I
would have to compile anything before running it?).

Anyway, what would it take under modules? How would I have to edit my
pass through query to link to it? Would I have to do anything special to
run it? Compile it?

The last "basic" programming I ever did was in the early 80's with TI
Extended Basic on my old TI 99/4a computer.

Pass through query:
Select BLKSTN,LATITUDE ,LONGITUDE ,CALLLETTER, NETWORKTYPE, PLATFORMID,
OBSERVATIONTIME,WINDDIRECTION ,WINDSPEED ,WINDSPEEDQC,CLOUDCEILING
,CLOUDCAVOK ,VISIBILITY ,AIRTEMPERATURE,AIRTEMPERATUREQC
,DEWPOINTTEMPERATURE ,DEWPOINTTEMPERATUREQC,SEALEVELPRESSURE
,SEALEVELPRESSUREQC,PRECIPAMOUNT1, OBSERVATIONPERIODPP1, PRECIPAMOUNT2,
OBSERVATIONPERIODPP2 ,PASTMANUAL1 ,PASTMANUAL1QC,PASTMANUAL2
,PASTMANUAL2QC,WXPASTPERIOD1, PASTAUTOMATED1,PASTAUTOMATED1QC,
PRESENTMANUAL1,
PRESENTMANUAL1QC,PRESENTMANUAL2,PRESENTMANUAL2QC,PRESENTAUTOMATED,
PRESENTAUTOMATEDQC,CLOUDCOVER ,ALTIMETERSETTING ,ALTIMETERSETTINGQC,
STATIONPRESSURE, STATIONPRESSUREQC, WINDGUSTSPEED, WINDGUSTSPEEDQC, from
SFC_OBS where BLKSTN in(388950) order by OBSERVATIONTIME;


Append Table Query:
INSERT INTO clst_station ( BLKSTN, LATITUDE, LONGITUDE, NETWORKTYPE,
PLATFORMID, OBSERVATIONTIME, [Year], [Month], [Day], [Hour], [Minute],
WINDDIRECTION, wspdkts, WINDSPEEDQC, cighgtft, CLOUDCAVOK, vsbysmi,
TempF, AIRTEMPERATUREQC, DewpF, DEWPOINTTEMPERATUREQC, SEALEVELPRESSURE,
SEALEVELPRESSUREQC, prcp1in, OBSERVATIONPERIODPP1, prcp2in,
OBSERVATIONPERIODPP2, PASTMANUAL1, PASTMANUAL1QC, PASTMANUAL2,
PASTMANUAL2QC, WXPASTPERIOD1, PASTAUTOMATED1, PASTAUTOMATED1QC,
PRESENTMANUAL1, PRESENTMANUAL1QC, PRESENTMANUAL2, PRESENTMANUAL2QC,
PRESENTAUTOMATED, PRESENTAUTOMATEDQC, CLOUDCOVER, ALTIMETERSETTING,
ALTIMETERSETTINGQC, STATIONPRESSURE, STATIONPRESSUREQC, gustkts,
WINDGUSTSPEEDQC, sstC, sstF, e, es, RH, TIME_CONV ) SELECT
sfc_obs_pass_through.BLKSTN, sfc_obs_pass_through.LATITUDE,
sfc_obs_pass_through.LONGITUDE, sfc_obs_pass_through.NETWORKTYPE,
sfc_obs_pass_through.PLATFORMID, sfc_obs_pass_through.OBSERVATIONTIME,
Year([OBSERVATIONTIME]) AS [Year], Month([OBSERVATIONTIME]) AS [Month],
Day([OBSERVATIONTIME]) AS [Day], Hour([OBSERVATIONTIME]) AS [Hour],
Minute([OBSERVATIONTIME]) AS [Minute],
sfc_obs_pass_through.WINDDIRECTION, CDbl([WINDSPEED]*1.943844) AS
wspdkts, sfc_obs_pass_through.WINDSPEEDQC, CDbl([CLOUDCEILING]*3.28) AS
cighgtft, sfc_obs_pass_through.CLOUDCAVOK, CDbl([VISIBILITY]/1609) AS
vsbysmi, CDbl([AIRTEMPERATURE]*1.8+32) AS TempF,
sfc_obs_pass_through.AIRTEMPERATUREQC,
CDbl([DEWPOINTTEMPERATURE]*1.8+32) AS DewpF,
sfc_obs_pass_through.DEWPOINTTEMPERATUREQC,
sfc_obs_pass_through.SEALEVELPRESSURE,
sfc_obs_pass_through.SEALEVELPRESSUREQC, CDbl([PRECIPAMOUNT1]/25.4) AS
prcp1in, sfc_obs_pass_through.OBSERVATIONPERIODPP1,
CDbl([PRECIPAMOUNT2]/2.54) AS prcp2in,
sfc_obs_pass_through.OBSERVATIONPERIODPP2,
sfc_obs_pass_through.PASTMANUAL1, sfc_obs_pass_through.PASTMANUAL1QC,
sfc_obs_pass_through.PASTMANUAL2, sfc_obs_pass_through.PASTMANUAL2QC,
sfc_obs_pass_through.WXPASTPERIOD1, sfc_obs_pass_through.PASTAUTOMATED1,
sfc_obs_pass_through.PASTAUTOMATED1QC,
sfc_obs_pass_through.PRESENTMANUAL1,
sfc_obs_pass_through.PRESENTMANUAL1QC,
sfc_obs_pass_through.PRESENTMANUAL2,
sfc_obs_pass_through.PRESENTMANUAL2QC,
sfc_obs_pass_through.PRESENTAUTOMATED,
sfc_obs_pass_through.PRESENTAUTOMATEDQC,
sfc_obs_pass_through.CLOUDCOVER, sfc_obs_pass_through.ALTIMETERSETTING,
sfc_obs_pass_through.ALTIMETERSETTINGQC,
sfc_obs_pass_through.STATIONPRESSURE,
sfc_obs_pass_through.STATIONPRESSUREQC, CDbl([WINDGUSTSPEED]*1.943844)
AS gustkts, sfc_obs_pass_through.WINDGUSTSPEEDQC,
6.11*(10^((7.567*[DEWPOINTTEMPERATURE])/(239.7+[DEWPOINTTEMPERATURE])))
AS e, 6.11*(10^((7.567*[AIRTEMPERATURE])/(239.7+[AIRTEMPERATURE]))) AS
es, Round(100*[e]/[es],1) AS RH, MSC_ADMIN_WEATHER_STATION.TIME_CONV
FROM sfc_obs_pass_through INNER JOIN MSC_ADMIN_WEATHER_STATION ON
(sfc_obs_pass_through.PLATFORMID = MSC_ADMIN_WEATHER_STATION.PLATFORMID)
AND (sfc_obs_pass_through.NETWORKTYPE =
MSC_ADMIN_WEATHER_STATION.NETWORKTYPE) AND (sfc_obs_pass_through.BLKSTN
= MSC_ADMIN_WEATHER_STATION.BLKSTN) ORDER BY
sfc_obs_pass_through.BLKSTN, sfc_obs_pass_through.OBSERVATIONTIME;
Click to expand...
 
Top