Solved

ChangeSQL for 2+ qrys

Posted on 2007-04-06
3
344 Views
Last Modified: 2012-06-27
Design:
SP on SQL 2k5
PT qrys in Access executing SP
User selects report from main menu
a form appears asking for parameter
user selects a month from the drop down list
cmdButton changes sql in pt qry accordingly

This code sounded like a great design until I got to the report that has three queries as a data source. (Detail and two charts)

Anyone have an idea of how to restructure this code without junking it up?

adria

************************
Private Sub cmdOpenRpt_Click()
Dim strSQL As String
Dim strOldSQL As String
Dim strQry As String
Dim strRpt As String

    Select Case txtRptName
        Case Is = "ISP Orders"
            strQry = "qryOrders_ISP"
            strSQL = "execute upOrders_ISP"
            strRpt = "rptOrders_ISP"
        Case Is = "Orders By Month"
            strQry = "qryOrders_ByMonth"
            strSQL = "execute upOrders_ByMonth"
            strRpt = "rptOrders_Lookup"
        Case Else
            Exit Sub
    End Select
        strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
                    Forms![frmSys_RunSPs]![cmbRptMonth] & "'")
        DoCmd.OpenReport strRpt, acPreview
        DoCmd.Close acForm, "frmSys_RunSPs", acSaveNo

End Sub

Function ChangeSQL(strQry As String, strSQL As String) As String

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(strQry)
    qd.SQL = strSQL
    ChangeSQL = qd.SQL
    Set qd = Nothing
    Set db = Nothing
End Function
0
Comment
Question by:adraughn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
stevbe earned 500 total points
ID: 18865350
so you need to chnage more than 1 PT query?

1. You could call ChnageSQL in each case, this way if you need to call it three times for a particular report you can and if you only need to call it one time, that id fine too.
2. You could add a boolean variable to indicate if you call ChnageSQL inside the case (which you would do for the 3 pass Case and then check that var after the End Select

    End Select
        If blnProcessed = False Then
            strOldSQL = ChangeSQL(strQry, strSQL & "'" & 
                    Forms![frmSys_RunSPs]![cmbRptMonth] & "'")
        End If
        DoCmd.OpenReport strRpt, acPreview
        DoCmd.Close acForm, "frmSys_RunSPs", acSaveNo

3. You could chnage the ChangeSQL function to handle multiple queries by passing the query and sql paramters comma separated and then parse them out for processing inside ChnageSQL.

I would probably go with 1 or 2 as 3 would be a fair bit extra work.

Steve
0
 
LVL 13

Author Comment

by:adraughn
ID: 18865426
this particular report is based off of three access queries that all require parameters. so in changing them to SQL, they will all need to be SPs.

I'm thinking right now that option #1 might be best, but I was hoping for a better solution.

With this migration, I will eventually need to change multiple reports that have more than one query as a data source.

Thanks for the response. I'll try one and two and see how it does.

adria
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18865478
here is a chnage to ChangeSQL to handle passing multiple queries (separated by | character as it will likely never be used in a SQL statement). You don;t actually use the return value from the function so I didn't bother returning anything


Function ChangeSQL(strQry As String, strSQL As String) As String
   
    Dim astrQry As Variant
    Dim astrSQL As Variant
    Dim intCount As Integer
   
    astrQry = Split(strQry, "|")
    astrSQL = Split(strSQL, "|")
   
    For intCount = 0 To UBound(astrQry)
        CurrentDb.QueryDefs(astrQry(intCount)).SQL = astrSQL(intCount)
    Next

End Function
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Excel Pivot Table and MS Access Pivot Table 4 84
SQL Query logic question 14 71
Return Data From Website in Access 6 59
Access VBA If Statements 5 20
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question