Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Last Updated (2003 allSPs)

    I have a very large database (by my standards) that reads data from an Accounting system (MYOB) via ODBC. Because the ODBC process is so slow, I have a button on my switchboard that makes local copies of the most important data and all subsequent queries, reports etc are read from that local copy. This seemed to be a reasonable workaround as even the updates to local copies takes over 10 minutes and, although the data was not live, it was accessible. However people are now wanting to know how out od date the data is at any time to determine if they should re-run the update process. Is there a way I can show the time of the latest update on the switchboard. This would be a matter of reading the date/time that a table was mofiied as this obviously would be the time of the last update
    TIA
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last Updated (2003 allSPs)

    I would create a table with a date/time field, say tblUpdate with field dtmUpdated, with a format that includes date and time. Create a single record in this table and set dtmUpdated to today's date.
    In the code that imports the data from MYOB, update the record and recalculate the form:

    Dim strSQL As String
    strSQL = "UPDATE tblUpdate SET dtmUpdate = Now()"
    CurrentDb.Execute strSQL
    Me.Recalc

    Put a text box (disabled, locked) on the switchboard with control source

    =DLookup("dtmUpdated","tblUpdate")

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Updated (2003 allSPs)

    Hans thanks for the quick response
    As you probably know by now I do not know VB but let me tell you what I did so you may be able to point out the error of my ways.
    By background, my current process is that I have a series of Make Table queries that are run by calling the queries up sequentially in a macro linked to a button on my Switchboard. No VB is used.
    I tried to do what you suggested but got an error:
    First I went into VB and created the following
    Option Compare Database

    Dim strSQL As String
    strSQL = "UPDATE tblUpdate SET dtmUpdate=Now()"
    CurrentDb.Execute strSQL
    Me.Recalc

    This appeared to be saved as Module 2

    I then created the text box on the switchboard with control source
    =DLookUp("dtmUpdated","tblUpdated")

    When I click on the text box on the Switchboard I get

    Compile error
    Invalid outside procedure with the words
    "UPDATE tblUpdate SET dtmUpdate = Now()"
    highlighted

    Help
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last Updated (2003 allSPs)

    Where did you create this code? Hopefully you either clicked New in the modules section of the database window, or selected Insert | Module in the Visual Basic Editor.

    Change the code to look as follows:

    Public Function SetDate()
    Dim strSQL As String
    strSQL = "UPDATE tblUpdate SET dtmUpdate=Now()"
    CurrentDb.Execute strSQL
    Forms!Switchboard!Recalc
    End Sub

    where Switchboard is - you guessed it - the name of the switchboard form.
    Now open the macro that runs the make-table queries in design view.
    In the blank row below the last action, select RunCode as action.
    Enter SetDate() in the function box near the bottom of the macro window.
    Save the macro.

Posting Permissions

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