Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running a query automatically in the background (2003)

    I have this idea which I have been playing with which maybe some of you could help me with please, either by changing my way of thinking or helping me solve my problem.

    I have got an access database with ODBC links to tables in an Informix database. I currently have code which downloads quiet a considerable amount of data from the Informix server into an access table so that I can use it for many other database that rely on this key data, at the moment this download takes about 30 mins to run.

    My idea was to have a separate computer run this query at specific times during the day, so that the data is always up to date. I could then create links to this table and ultimately save me time in the long run.

    My questions are

    1) If I were to use a timer function to trigger the running of the query, how is it done? (I think I would need to compact the data at certain times as well because of the volume of data I'm working with)
    2) Am I on the right path to solving this? ( for various reasons, budget being one of them, I need to try and get the maximum benefit out of what little I have)

    Thanks for all your help, as always
    Hayden

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running a query automatically in the background (2003)

    My first thinking is to ask why you download the data ? Can't you work with the linked tables ?

    For the Timer function use the on timer event of a form. Set the timer interval property of the form to the time you want in thousands of a second. In the On timer Event you put the code you use to download your data. The form should always stay open.
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running a query automatically in the background (2003)

    Thanks for the quick reply Francois

    The only reason for downloading the data in such a way is because there is so much of it, it takes ages to get the data I want, so I have this code which creates a table for me:

    Sub SelectNLData()
    Dim dbs As DAO.Database
    Dim rstNL As DAO.Recordset
    Dim rstNL1 As DAO.Recordset

    On Error GoTo ErrHandler
    DoCmd.SetWarnings False
    'Delete records in tables to avoid duplicates
    DoCmd.OpenQuery ("qryDeleteRecordsFromtblNLTransTemp")
    DoCmd.OpenQuery ("qryDeleteRecordsFromNlTransactions")

    'Set reference to database and open recordsets on the three tables
    Set dbs = CurrentDb

    Set rstNL = dbs.OpenRecordset("qryNLTransactions", dbOpenForwardOnly)
    Set rstNL1 = dbs.OpenRecordset("tblNLTransTemp", dbOpenDynaset)

    ' Loop through the records of the NL table
    Do While Not rstNL.EOF
    With rstNL1
    ' Add a new NL record
    .AddNew
    ' Set the fields
    !NLYear = rstNL!NLYear
    !POSTING_CODE = rstNL!POSTING_CODE
    !TRANS_PERIOD = rstNL!TRANS_PERIOD
    !JOURNAL_NUMBER = rstNL!JOURNAL_NUMBER
    !JOURNAL_DATE = rstNL!JOURNAL_DATE
    !JOURNAL_DESC = rstNL!JOURNAL_DESC
    !POST_DATE = rstNL!POST_DATE
    !ORIGIN = rstNL!ORIGIN
    !JOURNAL_AMOUNT = rstNL!JOURNAL_AMOUNT
    !CURRENCY_CODE = rstNL!CURRENCY_CODE
    !CURRENCY_AMOUNT = rstNL!CURRENCY_AMOUNT
    !TRANSACTION_DATE = rstNL!TRANSACTION_DATE
    !ANALYSIS_CODE1 = rstNL!ANALYSIS_CODE1
    !ANALYSIS_CODE2 = rstNL!ANALYSIS_CODE2
    !ANALYSIS_CODE3 = rstNL!ANALYSIS_CODE3
    !EXCHANGE_RATE = rstNL!EXCHANGE_RATE
    !REPORT_AMOUNT = rstNL!REPORT_AMOUNT
    !PRE_BASE_AMT = rstNL!PRE_BASE_AMT
    !PRE_REPT_AMT = rstNL!PRE_REPT_AMT
    !TRANSACTION_GROUP = rstNL!TRANSACTION_GROUP
    !SEQUENCE = rstNL!SEQUENCE
    !BATCH_REFERENCE = rstNL!BATCH_REFERENCE
    !Download = Now()
    ' Save the record
    .Update
    End With

    ' Move to next record
    rstNL.MoveNext
    Loop
    DownloadExtras
    MsgBox ("NL Download Complete"), vbInformation
    DoCmd.SetWarnings True
    ExitHandler:
    ' Clean up
    On Error Resume Next
    rstNL.Close
    rstNL1.Close
    Set rstNL = Nothing
    Set rstNL1 = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    ' Inform user
    MsgBox Err.Description, vbExclamation
    ' To clean up
    Resume ExitHandler
    End Sub


    This way, once I have the data, I can use it for the many other things I need to do without having to wait for the data to be brought back.

    I will have a go at the Timer function, if I need to compact the data I have usually just set the "Compact on Close" option in Tools-Options-GeneralTab to yes, by leaving the form open what function would I use to compact the data?

    Kind Regards
    Hayden

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running a query automatically in the background (2003)

    Would a append query not be faster ?

    For compacting, have a look at How to Compact Databases At a Scheduled Time
    Francois

  5. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running a query automatically in the background (2003)

    I can certainly try an update query and see if it is faster.

    Thanks for the help
    Hayden

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running a query automatically in the background (2003)

    As you delete all the records, you have to use an append query, not an update query.
    Francois

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running a query automatically in the background (2003)

    There is an upper limit on the amount of time you can use in a form timer event, so you would have to set a time in the registry and then keep testing to see if the time has been reached before doing the import and then resetting the registry value.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running a query automatically in the background (2003)

    Hi Charlotte

    I'm not sure that I completely understand what you are saying. Do you mean that if I want the timer to start a query every hour on the hour for example, if I start the thing running in the morning would it stop running by late afternoon once a certain time period has elapsed? Or are you referring to the time intervals between running of the query?

    Regards
    Hayden

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running a query automatically in the background (2003)

    Timers simply fire every however many specified milliseconds. In the timer event, you have to check a time to see whether it is time to do whatever you wanted to do. That means you need a starting time to check against, and one way to do that is to store a date time in the registry, so that you can retrieve it and test to see if the current time (Now()) is greater than or equal to the start time plus the interval you decided to use. Is that clearer?
    Charlotte

  10. #10
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running a query automatically in the background (2003)

    Oh I think I get it now, I'll give it a try

    Thanks alot
    Hayden

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running a query automatically in the background (2003)

    Another approach that I have used to circumvent the Timer limitations is to do the download (or whatever...) immediately when the database is opened. Then use the Windows Task Scheduler to launch the database at the appropriate time of day. Some other stuff to add to the database is a Quit command for when the download is complete and a "manual override". In actual practice, I use a form that has Timer to launch the process about 10 seconds after the form is opened. The form has a "Manual" button that interrupts the timer without launchine the download. Finally, a "go" button executes the actual download process. Bottom line is that the daily scheduling is done via the Windows Task Scheduler which offers a fair bit of flexibility.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •