Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / March 2005

Tip: Looking for answers? Try searching our database.

Using a Timer with Registry

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Don - 30 Mar 2005 05:23 GMT
I have a complex query that takes about 7 to 10 minutes to run as a result of
a "Like" expression over a network. I would like to run this query early in
the morning when the staff are not yet at work and after an auto update of
imported data. So far what I can figure out is I can use code to have the
timer perform the below:

Private Sub Form_Timer()
Dim stDocName As String
‘ If time is after 05:00 AM Then run Macro named "Append & Update Linked
Tables  to Me tables"

If Time() > #5:00:00 AM# Then

   stDocName = "Append & Update Linked Tables to Me tables"
   DoCmd.RunMacro stDocName

End If

End Sub

The problem I am having is I need to somehow use a registry setting to store
the date last run, this way it won't try to run more than once per day.  I
can even hide the form so it is not intrusive.  

The following is what the help suggests but I do not know what to put where
and I need someone to breakdown it down to me and how do I know where to save
the settings in the Registry?

'GetSetting(appname, section, key[, default])
Dim MySettings As Variant
' Place some settings in the registry.
SaveSetting "MyApp","Startup", "Top", 75
SaveSetting "MyApp","Startup", "Left", 50

GetSetting(appname := "MyApp", section := "Startup", _
                      key := "Left", default := "25")

DeleteSetting "MyApp", "Startup"

Thanks,

Dennis
Dirk Goldgar - 30 Mar 2005 07:04 GMT
> I have a complex query that takes about 7 to 10 minutes to run as a
> result of a "Like" expression over a network. I would like to run
[quoted text clipped - 38 lines]
>
> Dennis

I don't think I'd bother with the registry, unless you really want to.
I'd just have a table in my database to store such things.  Then the
code might be along the lines of:

'----- start of example code -----
Private Sub Form_Timer()

   Dim stDocName As String

   If Time() > #5:00:00 AM# Then

       If DLookup("MacroLastRunDate", "tblSettings") < Date Then

       stDocName = "Append & Update Linked Tables to Me tables"
       DoCmd.RunMacro stDocName

       CurrentDb.Execute _
           "UPDATE tblSettings SET MacroLastRunDate = " & _
           Format(Date, "\#mm/dd/yyyy\#")

       End If

   End If

End Sub

'----- end of example code -----

You're aware, I hope, that this is only going to work if the database
will be open at the appropriate time.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Don - 30 Mar 2005 14:55 GMT
> > I have a complex query that takes about 7 to 10 minutes to run as a
> > result of a "Like" expression over a network. I would like to run
[quoted text clipped - 69 lines]
> You're aware, I hope, that this is only going to work if the database
> will be open at the appropriate time.

Dirk,

First, thanks for your help. I tried to run the code but have had some
errors as there are some things happening that I am not sure of.

One is a  run time error 3078 that can't find the input table or query
settings for "tblSettings". In the Debug, the following line is yellowed:

If DLookup("MacroLastRunDate", "tblSettings") < DATE Then

Not sure what tblSettings is? I thought is was somthing in the Registry. I
tried to run the Macro first thinking that it needed a run date created
before the code would work. I am thinking that I need to create a table to
record the last macro run date for the code to work?

What do you think?

Dennis
Dirk Goldgar - 30 Mar 2005 16:01 GMT
>>> I have a complex query that takes about 7 to 10 minutes to run as a
>>> result of a "Like" expression over a network. I would like to run
[quoted text clipped - 90 lines]
>
> Dennis

Yes, that's right, Dennis.  What I was suggesting was that you create a
table named "tblSettings", with a date/time field named
"MacroLastRunDate" (or whatever would make sense to you).  There could
also be other fields to store other settings used by your application.
You'd put one record in this table when you create it, with the field
MacroLastRunDate initialized to some date arbitrarily long ago.  Note
that the code I gave doesn't allow for the possibility that this table
might not exist -- That's why you're getting error 3078 --  nor that the
field might have a Null value.  You could add code to cope with that
situation, but the simplest solution is just to create the table in
advance and put a record in it with MacroLastRunDate set to some "old"
date.

Incidentally, as a side issue, I'm a little concerned about the
implications of the capitalization of the word "DATE" in this line you
posted:

> If DLookup("MacroLastRunDate", "tblSettings") < DATE Then

Normally, the Date function will only have the "D" capitalized.  Does
that mean you have some control, field, or variable that is named
"DATE"?  If so, that could potentially interfere with the proper
interpretation of that keyword as a call to the function.  Make sure
that the code is interpreting it properly.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Don - 31 Mar 2005 04:07 GMT
> >>> I have a complex query that takes about 7 to 10 minutes to run as a
> >>> result of a "Like" expression over a network. I would like to run
[quoted text clipped - 115 lines]
> interpretation of that keyword as a call to the function.  Make sure
> that the code is interpreting it properly.

Dirk, this database is on a network and I am trying to update this query on
the back end. This back end never is opened unless I am maintaining it or
upgrading the design. I realize that this form must be open for the update to
work (by the way, it works nicely thanks!!) and I was going to put it on one
of the front end remotes but there are problems with that senerio as well
(user does not show up to work and unit is turned off by accident or worse on
vacation). Unfortunatly this looks to be the best way so far. Is there
another way to run this macro or other means  to run an update at the back
end without opening it?

Thanks,

Dennis
Dirk Goldgar - 31 Mar 2005 06:52 GMT
> Dirk, this database is on a network and I am trying to update this
> query on the back end. This back end never is opened unless I am
[quoted text clipped - 6 lines]
> way to run this macro or other means  to run an update at the back
> end without opening it?

If the back-end is on a server that has a task scheduler service running
on it, you can use the task scheduler to start Access with command-line
arguments that tell it to open your back-end database and run a specific
macro.  That macro can run your process, whatever it is, and then close
the database and quit Access.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.