Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Background Program to Trigger Access VBA routines? (Office 2000 Sr1a)

    Is there some simple way I can trigger a VBA function in a Standard module of an Access database periodically without using something that requires the database to be left open as the timer event on an open form would require? ie. some VB routine that calls up the VBA function every 10 minutes, for example?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Background Program to Trigger Access VBA routines? (Office 2000 Sr1a)

    have you tried using windows task scheduler along with the command line properties of Access ?
    I have never had to use this myself but that is where i would start to look.
    HTH

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

    Re: Background Program to Trigger Access VBA routines? (Office 2000 Sr1a)

    Didn't you ask the same question last week? I take it that whatever answers you got didn't work?
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Background Program to Trigger Access VBA routines? (Office 2000 Sr1a)

    No, it was an extremely similar question though.
    I got eveything working to my satisfaction like this:
    1) I created another database ('Trigger') which starts up automatically and logs on a special user
    ( I put the username and password in the shortcut's command prompt)
    2.)This user has no rights anywhere in <font color=red>any database </font color=red> except the Trigger database where they are restricted to opening a particular form with a timer and running its code.
    3.) This code triggers, via automation, my routines in the production database by logging onto it as a user with full rights. The username and password of this full user are hidden by password protection of the form's code.

    But there remains the annoyance of having that Trigger program floating around on the task bar all the time where, for example, it may be inadvertently closed.
    I wondered if you could either minimise it somehow to the system tray or dispense with it altogether by creating some VB program that sits in the background and does the same job.
    <font color=448800>
    Besides, I'm quite curious to know if Access can act as an Automation server for some such background VB program or service? </font color=448800> <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22>

  5. #5
    New Lounger
    Join Date
    May 2002
    Location
    CT, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Background Program to Trigger Access VBA routines?

    To cut to the chase:
    create a file with notepad and call it something.vbs
    The contents of the file (this file is called RefreshServerTables.vbs because that's what it does):
    '-----------------------------------------
    '-----------------------------------------
    Dim objAccess
    Dim strDBName
    Dim strProcedName

    Set objAccess = WScript.CreateObject("Access.Application")
    strDBName = "H:PledgeDesignationInstallments.mdb"
    strProcedName = "RefreshServerTables"
    objAccess.OpenCurrentDatabase strDBName
    objAccess.Application.Run strProcedName

    Set objAccess = WScript.CreateObject("Access.Application")
    strDBName = "H:ConstituentContacts.mdb"
    strProcedName = "MakeLocalTable"
    objAccess.OpenCurrentDatabase strDBName
    objAccess.Application.Run strProcedName

    '-----------------------------------------

    const ForAppending = 8

    filelist = ""
    set fso = CreateObject("Scripting.FileSystemObject")
    set tempfiles = fso.GetFolder("H:").Files
    set Tstream = fso.OpenTextFile("H:RefreshServerTables.log", ForAppending, True)

    for each file in tempfiles
    pos = InStr(Ucase(file), ".MDB")
    if pos then
    filelist = filelist & file.name & " Size: " & file.size & " Last Modified: " & file.datelastmodified & " "
    Tstream.WriteLine file.name & " Size: " & file.size & " Last Modified: " & file.datelastmodified
    end if
    next

    Tstream.WriteLine " "
    Tstream.Close
    MsgBox filelist

    '-----------------------------------------
    '-----------------------------------------

    Lastly, add the file to the Windows Scheduler (under programs/accessories/system tools/scheduled tasks) with whatever schedule you want it to run. The script I posted runs every morning at 6:45 and it does a data refresh of ms access tables buy running VBA procedures. It also appends the database characteristics to a log file.

    Peter

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Background Program to Trigger Access VBA routines?

    Excellent!
    Just what I was looking for. Thank you.
    I will try it out today and see how it goes.
    As this is a VBScript script I am wondering (as I am
    not terribly au fait with this side of things) if it can be used in some sort of Active server page to trigger
    VBA procedures in my office database via the web?
    I have an application set up at the moment on IIS which
    gives me partial control via the web of my database but
    would like to be able to trigger various routines from time to time by clicking a button on a web page. Is this easy to do?

  7. #7
    New Lounger
    Join Date
    May 2002
    Location
    CT, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RE: WSH (the new DOS)

    Edited to include Hyperlink

    David,

    See: Windows Script (MSDN)

    Also, Cue has a book called something like "Windows 2000 Professional...." with an excellent chapter in it on WSH. Go to Barnes & Noble and do a search. There are a couple (literally one or two) of books dedicated to the subject too.

    Since WSH is the PC version of an operating system control language (like Kornshell for Unix, JCL for IBM, DCL for DEC/VAX) it can do almost anything and you can write it with both a VB or Java script flavor. That Microsoft web site primarily talks about it's web abilities.

    Glad to be of help. I struggled off and on for over a year with the very same issue but it wasn't until I posed the question to Helen Feddema, who writes the Woody's Access Watch e-zine (and just came out with an excellent MS Access book), that I was put on the right path. Wish I had done it a year ago!

    Peter

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: WSH (the new DOS)

    Peter,
    Thanks for all that.
    You're the first person who has set me on the right path too - fortunately I have only been thinking about it
    for a few months!
    The wonder is that this question has not been posed more frequently on this forum or the others I have researched for the answer.
    It seems that we have all this technology at our fingertips and hardly anybody is using it in ways I feel
    should be common and obvious.
    Isn't this the whole push with .NET and so on?
    (To control your business from any internet connnected computer in the world?)

  9. #9
    New Lounger
    Join Date
    May 2002
    Location
    CT, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: WSH (the new DOS)

    David,
    It's my pleasure to be of help whenever I can; we're all in the same pot of technology soup, so to speak, and I too have been very thankful to people like Woody and Helen for helping me with problems.
    When logging on to Woody's site I saw an ad for his new book, "Special Edition Using Windows XP" and that is the same publisher of the book I mentioned in an earlier post to you; the name is "Special Edition Using Windows 2000 Professional" and it has an excellent chapter on WSH.
    As far as why there is so little information on it, I have heard that Microsoft Certified Solution Providers (and the Microsoft Developer's site I sent in the earlier post) like to keep this under wraps; it's something they charge good prices to do for clients and they aren't in a hurry to explain it to others "not in the clan" so to speak. That msdn web site is really for the birds it's so bare-boned with no explanations, etc. I for one am making it a personal priority to really learn this and test scripts on my home network, login scripts to remote administration scripts, so I'll not only be able to use it but also to explain it. I'm one who believes in open systems. Anyway I'm ranting. Once caveat though, it might be more worthwhile for the long run to learn the Jscript instead of the VBscript because I've heard the latter would be "going away" at some point in the not too distant future.
    Good luck Dave!

    Peter

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: WSH (the new DOS)

    Peter,
    I don't mind your ranting at all. I sometimes indulge a bit myself when driven mad by these things and the lack of logic that abounds in every direction.
    Relieves the mind!
    Just for your info I have come across this routine which is really your script but will run VBA routines on your home/office computer via an Active Server Page
    you can log into at any old Internet Cafe - Spain, Patagonia or Outback Australia. You'll find it here:
    http://www.aspfree.com/authors/rober...ult.asp?aid=17
    The problem, which I think I have overcome just a few minutes ago, with this script and the one you put on the forum is how to deal with databases secured by user-level security. There seems to be no provision for
    the option in the OpenCurrentDatabase method.
    Yet, ( I'm ranting now) who would be using such a method on databases containing information not important enough to safeguard with such security.
    It's like being given a Golden Key to open an old empty shed instead of the Emerald City!
    My technique is this: I've created a special database called TRIGGER which the Admin user can open but that's all. The Admin user has no other rights in this or any other database. I've cleared the password for the Admin user so no log on box appears when you try to open any Access database on the machine.
    Then, so I can open these database as another more privileged user in the workgroup information file I've placed shortcuts on the desktop or task bar with command lines specifying the location of the WIF and a default privileged user (in this case, me!) but left the
    /pwd switch of the command line blank.
    This forces the other databases to present a logon box so you can log on and use them normally.
    But TRIGGER will open without any logon box.
    Then I did this:
    Dim objAccess
    Dim strDBName
    Set objAccess = WScript.CreateObject("Access.Application")
    strDBName = "C:Put your pathname hereTrigger.mdb"
    objAccess.OpenCurrentDatabase strDBName
    objAccess.Application.Run "THEVBAroutine"
    objAccess.CloseCurrentDatabase
    objAccess.Application.Quit

    Set objAccess = Nothing

    The actual routine I call (THEVBAroutine) actually calls a dozen other routines in the same module.
    (As Charlotte in the Access forum said, "You're re-inventing batch processing?" - Yes, I had to)
    Some of these routines need to open tables and queries in my main production database (there are no tables or data in TRIGGER).
    Admin has no right to do so.
    But it doesn't matter because these routines use an
    ADO based OLE DB connnection string complete with the user name and password of a privileged user.
    So it works.
    The sensitive nature of this user name and password
    is protected by password protecting the VBA project in TRIGGER (you could also take it further and make TRIGGER an MDE FILE)
    Unless I've missed something it seems to function
    fine. What do you think of this approach?

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

    Re: RE: WSH (the new DOS)

    <hr>To control your business from any internet connnected computer in the world?<hr>
    The rest of us are letting intrepid experimenters like you hang out on the bleeding edge while we watch with great interest! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Charlotte

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: WSH (the new DOS)

    Oh no Charlotte - don't call me intrepid, it's too scary. Maybe the correct word is "desperate"?
    It's taken 5 years off my life figuring this out.
    For relaxation I grab a beer (Australian, you know)
    and prop in front of the telly. What do I get?
    Commercials from dear old William descanting on the virtues of "One Degree of Separation".
    Gadzooks already!

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: RE: WSH (the new DOS)

    I'm with Charlotte on this one - VBS can be very handy, BUT . . . .
    It is one of the favorite ways for hackers to get into a PC and wreak havoc. So if you are going to enable it on your PC, you want to make sure that it can't be run from email messages or from IE or another browser. That may be one of the reasons it isn't better documented also - make the hackers work a little harder so to speak.
    Wendell

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: WSH (the new DOS)

    I must confess that I don't really understand the implications of what you are saying 100%.
    I am just an ordinary person trying to get my computer
    to perform a function that will make life easier in my business ,which actually has nothing to do with computing.
    If Charlotte means by "while we watch with great interest! " that I am putting my business and livelihood in danger by using VBscript and other apparently doubtful technology, then she ought to tell me straight
    rather than make a joke of it.
    I don't understand what is meant by
    "if you are going to enable it on your PC, you want to make sure that it can't be run from email " for example.
    As far as I know VBS is automatically enabled in the operating system. I took no step to 'enable' it that I am conscious of.
    In my limited experience it seems to me that whatever
    script language you use the hackers will run rings around you if they wish to.
    As my non-computer friend keeps saying:" do these computers really make life any easier?"
    He's a Luddite who I often find very hard to refute!
    Thanks for your pointers anyway.

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

    Re: RE: WSH (the new DOS)

    Sorry, David. I wasn't making a joke of it, so please acquit me of being frivolous about a serious issue. I simply didn't understand the answer well enough to know whether it put you at risk or not. I meant it when I said we'd watch with interest. You're trying to do something on the edge now, but it may become commonplace sometime in the future, whatever the method finally employed.

    You asked how to do something and you received an answer that may work for you. There are security considerations with VBScript, which is why Outlook now blocks vbs files automatically and some IT departments disallow scripting as a policy, but I assumed you were already dealing with those issues since you seemed to understand what you were trying to do.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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