Results 1 to 2 of 2
  1. #1
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56

    Pre-Define query based on input data

    I am looking to find out if I can build a "on the fly" pass-through query based on user predefined data.



    Basically I am linking to a massive database. Access link to DB2 table. In order to make the system useable for the users they need to edit the query before it's ran. If not the query over the network takes around 5 minutes every time it's accessed and options such as filters and sorting are chosen. So if a user sorts one column and filters on another the query has now ran 3 times. Once to open, once to filter, and once to sort. That just took 15 minutes.

    So If there is a way to build macro or form that asks them for specific information first and then modify's the query so that it only gets ran once that would be fantastic.

    Is this possible?

    Example:
    Table has 7 fields/columns. Usually they will do 3 things. Pull back data from either a month or quarter. Then filter a column by it's content. Then sort by date of another date/timestamp column.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If all of your users have their own FE file, perhaps creating a temp query object using querydefs is a solution.
    http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

    Here is an example of mine that I use to export data to Excel. It uses an existing Named Saved Query Object and creates a second, temp, query object based on the first and a subform's filter property. When finished, the temp object is deleted.

    Code:
            Dim strWhere As String
            Dim strDate As String
            strDate = Format(Now(), "yyyy_mm_dd_hh_ss")
            Dim strDirectory As String
            strDirectory = "C:\Test\" & strDate & ".xls"
            If Me.sfrmContainer.Form.FilterOn = False Then
            MsgBox "No filter is applied to your form."
            Exit Sub
            Else
            strWhere = "Where " & Me.sfrmContainer.Form.Filter
            End If
            Me.SetFocus
                Dim dbTrans As DAO.Database
                Dim qdfTemp As DAO.QueryDef
                Dim strSQL As String, strQDF As String
                Set dbTrans = CurrentDb
                strSQL = "SELECT qryEquipMaint.* FROM qryEquipMaint "
                strSQL = strSQL & strWhere
                strQDF = "_TempQuery_"
                Set qdfTemp = dbTrans.CreateQueryDef(strQDF, strSQL)
                'Use the temp query def
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQDF, strDirectory, True
                Me.cmdSubThree.HyperlinkAddress = strDirectory
                'Done with the temp query def
                dbTrans.QueryDefs.Delete ("_TempQuery_")
                qdfTemp.Close
                Set qdfTemp = Nothing
                dbTrans.Close
                Set dbTrans = Nothing

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-07-2013, 03:21 PM
  2. View Data based on Form Input?
    By 0REDSOX7 in forum Forms
    Replies: 7
    Last Post: 11-29-2011, 10:25 AM
  3. Replies: 3
    Last Post: 07-13-2011, 08:01 AM
  4. Replies: 6
    Last Post: 07-22-2010, 05:53 PM
  5. Replies: 1
    Last Post: 06-14-2010, 02:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums