473,715 Members | 2,220 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

ADP: QueryDef equivalent?

Seems like ADP is predicated on the notion that you'd only want to use
stored procedures in the back end or in-line SQL in the front end.

Makes it really messy to develop something like
-------------------------------------
SELECT
v.SECURITY_ALIA S,
v.NEXT_REFIX_DA TE AS ConversionDate,
v.RESET_DAY AS VariableRateMod e,
CASE
WHEN v.RESET_DAY='MO ' THEN 'Monday'
WHEN v.RESET_DAY='TU ' THEN 'Tuesday'
WHEN v.RESET_DAY='WE ' THEN 'Wednesday'
WHEN v.RESET_DAY='W' THEN 'Wednesday'
WHEN v.RESET_DAY='TH ' THEN 'Thursday'
WHEN v.RESET_DAY='FR ' THEN 'Friday'
WHEN v.RESET_DAY='F' THEN 'Friday'
WHEN v.RESET_DAY='1' THEN '1-Day'
WHEN v.RESET_DAY='7' THEN '7-Day'
WHEN v.RESET_DAY='28 ' THEN '28-Day'
WHEN v.RESET_DAY='35 ' THEN '35-Day'
WHEN v.RESET_DAY='90 ' THEN '90-Day'
WHEN v.RESET_DAY='D' THEN 'Daily'
WHEN v.RESET_DAY='FM O' THEN 'First of Month'
WHEN v.RESET_DAY='H' THEN 'Holiday Rules Appl'
WHEN v.RESET_DAY='MM ' THEN 'Mid-Month'
WHEN v.RESET_DAY='NA ' THEN Null
WHEN v.RESET_DAY='NU L' THEN Null
ELSE Null
END AS VariableRateMod e_Mapped

FROM
SECURITYDBO.VAR IABLE_RATE v,
(
SELECT
v0.SECURITY_ALI AS AS Security_Alias,
Max(v0.EFFECTIV E_DATE) AS Effective_Date

FROM
SECURITYDBO.VAR IABLE_RATE v0

WHERE
v0.SRC_INTFC_IN ST = 83

GROUP BY
v0.SECURITY_ALI AS
) mx

WHERE
v.SECURITY_ALIA S = mx.Security_Ali as AND
v.EFFECTIVE_DAT E = mx.Effective_Da te AND
v.SRC_INTFC_INS T = 83
-------------------------------------

in, say, SQL Programmer and then adapt it to the ADP.

In regluar MS Access, you'd just create a passthrough query and paste
the SQL directly into it, and go.

With an ADP it seems like one has to choose between a humongous
in-line string and laborously putting in "_ " line continuation
strings - which makes it really time-consuming to flip-flop
back-and-forth between the SQL development tool and ADP...

Is there a better way for somebody who does not have CREATE authority
for SPs in the back end?
Nov 12 '05 #1
1 2417
PeteCresswell wrote:
Seems like ADP is predicated on the notion that you'd only want to use
stored procedures in the back end or in-line SQL in the front end.

Makes it really messy to develop something like
-------------------------------------
SELECT
v.SECURITY_ALIA S,
v.NEXT_REFIX_DA TE AS ConversionDate,
v.RESET_DAY AS VariableRateMod e,
CASE
WHEN v.RESET_DAY='MO ' THEN 'Monday'
WHEN v.RESET_DAY='TU ' THEN 'Tuesday'
WHEN v.RESET_DAY='WE ' THEN 'Wednesday'
WHEN v.RESET_DAY='W' THEN 'Wednesday'
WHEN v.RESET_DAY='TH ' THEN 'Thursday'
WHEN v.RESET_DAY='FR ' THEN 'Friday'
WHEN v.RESET_DAY='F' THEN 'Friday'
WHEN v.RESET_DAY='1' THEN '1-Day'
WHEN v.RESET_DAY='7' THEN '7-Day'
WHEN v.RESET_DAY='28 ' THEN '28-Day'
WHEN v.RESET_DAY='35 ' THEN '35-Day'
WHEN v.RESET_DAY='90 ' THEN '90-Day'
WHEN v.RESET_DAY='D' THEN 'Daily'
WHEN v.RESET_DAY='FM O' THEN 'First of Month'
WHEN v.RESET_DAY='H' THEN 'Holiday Rules Appl'
WHEN v.RESET_DAY='MM ' THEN 'Mid-Month'
WHEN v.RESET_DAY='NA ' THEN Null
WHEN v.RESET_DAY='NU L' THEN Null
ELSE Null
END AS VariableRateMod e_Mapped

FROM
SECURITYDBO.VAR IABLE_RATE v,
(
SELECT
v0.SECURITY_ALI AS AS Security_Alias,
Max(v0.EFFECTIV E_DATE) AS Effective_Date

FROM
SECURITYDBO.VAR IABLE_RATE v0

WHERE
v0.SRC_INTFC_IN ST = 83

GROUP BY
v0.SECURITY_ALI AS
) mx

WHERE
v.SECURITY_ALIA S = mx.Security_Ali as AND
v.EFFECTIVE_DAT E = mx.Effective_Da te AND
v.SRC_INTFC_INS T = 83
-------------------------------------

in, say, SQL Programmer and then adapt it to the ADP.

In regluar MS Access, you'd just create a passthrough query and paste
the SQL directly into it, and go.

With an ADP it seems like one has to choose between a humongous
in-line string and laborously putting in "_ " line continuation
strings - which makes it really time-consuming to flip-flop
back-and-forth between the SQL development tool and ADP...

Is there a better way for somebody who does not have CREATE authority
for SPs in the back end?


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

Arvin Meyers (I believe) used to store his queries in a table and
retrieve them into a string before running. Do you have table creation
permission on the db you are working with?

Perhaps its time to talk to the DBA and get permission to do your work!
Yeah! You tell 'em!! :-)

If that fails, maybe you can create a local .mdb file that has a table
of queries, or the queries themselves if the .mdb file can be linked to
the SQL back-end. Some OpenDatabase() or Automation work to get at the
local tables/queries. Just some ideas . . . .

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQIQ9n4echKq OuFEgEQLfOwCgvT 8JACkpKmJ5zfoMA Tm6bAGk6bgAoOYt
JkA1hwzmFvsiwm2 nV7POefKl
=jnHD
-----END PGP SIGNATURE-----

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

33
2887
by: Jerry Boone | last post by:
A long time ago Larry Linson (MVP contributor to this group) made a point to me that mdb's could do anything ADP's (Access Projects) could by using linked tables to leverage Sql server databases. It's been years since then, and I still sometimes think about that statement when evaluating the usefulness of the ADP's I build. Larry and I discussed this technique and he found it "Credible" (hope you don't mind me quoting you sir) so what the...
3
2091
by: CFW | last post by:
I have a view (Access .adp fronte end and SQL Server back) that retrieves a currency field from a table that is sometimes a NULL value. That view is the RowSource for a List box that displays the results. I want the List Box to show that field as 0 instead of leaving it blank if the underlying table field is NULL. I can't use the IIf or NZ function in a view. How can I do this?! TIA! BTW - I MOSTLY need it because I SUM that List Box...
2
2562
by: Lauren Quantrell | last post by:
I used to use this code to create shortcuts in my Access Jet MBD programs but it won't work in Access ADP projects. I know it's related to reference to CurrentDB.Properties (where it errors). IS there an equivalent to do this in an ADP??? Any help is appreciated. lq Private Function CreateShortcut(myPath As String, myDest As Long, dDrive As String)
7
497
by: brett valjalo | last post by:
Hey Folks: Long time no see! Hope everyone is well. I have an old mdb I'm upsizing to an adp. There is a button on a form which executes code similar to the following (this is a search form where sql is dynamically generated based on 1-7 criteria fields chosen by the user): strSQL = strSQLBase & strWhere & strOrder
0
293
by: PeteCresswell | last post by:
Seems like ADP is predicated on the notion that you'd only want to use stored procedures in the back end or in-line SQL in the front end. Makes it really messy to develop something like ------------------------------------- SELECT v.SECURITY_ALIAS, v.NEXT_REFIX_DATE AS ConversionDate, v.RESET_DAY AS VariableRateMode, CASE
60
10140
by: Neil | last post by:
I have a situation with an ODBC linked view in an Access 2000 MDB with a SQL 7 back end. The view is scrolling very slowly. However, if I open the view in an ADP file, it scrolls quickly. I needed to use an ODBC link for the view because it needs to be editable. Otherwise, I would have used a pass-through query. In previous discussions about using an MDB file vs. an ADP file as a front end for SQL Server, the impression I got was that...
6
4185
by: Tim Marshall | last post by:
A2003, but this behaviour also occurred during the same DAO process I'm about to describe in A97. I have a sub procedure which takes two arguments: a querydef name; and an SQL statement constructed from the calling procedure and constructs or modifies a querydef. The sub runs through the querydefs collection - if it finds the querydef name argument, it takes the existing query and modifies the SQL. If not found, a new querydef is...
3
5725
by: Zahed | last post by:
Using Microsoft 2003 (ADP)connecting to SQL 2000. I am trying to create a simple form in Access and pass parameters to a SQL query. tried but dosen't work : SELECT Port_Code, Port_Name FROM dbo.Port_Code WHERE ]] pls help
6
2490
by: rahuldev999 | last post by:
Hi I am beginner in the access project. can anyone suggest how to deal with this " Set rq = Db.CreateQueryDef("r_temp_rslt_research", strSql)" in the below code.As its a MDB application which needs to be converted into ADP. Any kind of help would be highly appreciated. Below is the snippet of code: Dim Db As DAO.Database
0
8718
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9332
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9195
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9100
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9044
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4738
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3174
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2537
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2118
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.