[mso] Re: Access Automation going OT here
- From: "Colli, Anthony G" <Anthony.Colli@xxxxxxx>
- To: "'mso@xxxxxxxxxxxxx'" <mso@xxxxxxxxxxxxx>
- Date: Wed, 25 Sep 2002 14:54:03 -0400
James-
I am interested in your code. I have a SQL server db that gets populated
every night with new data. Reports are written off this data, some are
daily, most are weekly, others monthly. I usually connect to this db with an
.ADP but these don't support autoexec macros, I suppose I could connect with
a normal .MDB. Ideally I would like to run the SQL server DTS jobs (over
night) and then the reports.
Any help/advice is welcome.
-Anthony
-----Original Message-----
From: James LaBorde [mailto:jlaborde@xxxxxxxxx]
Sent: Wednesday, September 25, 2002 1:39 PM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Access Automation
Glenda,
I have a database that does something similar to this. Anthony's idea of
having the queries you don't want others to run in their own database is
ideal.
There are two ways to do this. The method I use in my database is to list
the queries in a table that I want to run and include a lastrun field. I
then write code to loop through this table and run the items listed that
have a lastrun date that is not today. This gives you a few benefits.
First, it prevents the queries from running more than once on the same day
and wasting time and/or changing data in the middle of the day. Second, it
makes your code restartable. If for any reason something happens during
your update, you simply re-open the database and it will pick up where it
left off.
Your loop should look something like:
With Name of QueryTable
Do
IF rec.lastrun<> Date() Then
Run Query
End If
rec.edit
rec.lastrun=Date()
rec.update
rec.movenext
Loop Until rec.EOF
End With
You then put runcode in the Autoexec macro and call your code for this.
The second option is to put all of your queries in the autoexec macro and
have it run them all. While this is simpler than writing your own code, it
also has some drawbacks. The first of which is that there is no error
handling. Also, if you are running a number of queries, it is not
restartable. If something happens during your update, this will start over
when you try to run it again. Remember that the more you run and the more
is updated, the more likely you are to have your macro fail at some point.
I guess it is obvious which method I believe is best. It has several other
advantages too. Since you are in code, you have the option of adding to it.
My database like this I have a timer setup so that I know how long each item
took to run. Mine runs primarily reports and other snippets of code
including code to run a series of queries. I have a script to email a
designated recipient(s) when the reports finish , including a link to the
network location of the report. You can get very complex with it and the
only limitations tend to be how much VBA you want to learn.
If you need some help with it let me know and I will gladly assist you with
it
James La Borde
South Western Federal Credit Union
-----Original Message-----
From: Glenda Wells [mailto:gwells@xxxxxxxxxxx]
Sent: Wednesday, September 25, 2002 7:48 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Access Automation :VSMail MX1
Hi All.
I would like to have several queries via macro in one Access database run
once a day automatically. If anyone attempts to run any of these particular
queries manually I would like them to not be able to do that and a message
to pop us saying something like "This data has already been refreshed". At
a specified time, I need all make tables to be deleted automatically.
Alternatively, anyone could run the specified queries via macro but if they
have already been run once that day, then no one else can with the pop up
message.
The DB can be stored on a network directory.
Any ideas on how to make this happen?
Thanks in advance. /gl
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions. Once you are a member of the files group, you can go
here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions. Once you are a member of the files group, you can go
here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
- » [mso] Re: Access Automation going OT here
