Using a Timer with Registry

[Don]

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]

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

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.

 

[Don]

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

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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]

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

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.

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

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.

 

[Don]

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

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.

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

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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]

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?

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.