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

    Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    I have written a batch of Function routines in an Access database that perform everyday tasks such as transferring new mails into database tables from Outlook, print out letters etc, etc and I want to trigger these to occur pretty frequently and automatically - without my intervention. I can't figure out a way to do this that is secure. If I attach the batch of functions to the Timer event on a form in the same database, or even another Access database, security is compromised because the database is already opened with certain necessary privileges. Or, someone could view tables from the second database by linking them in the tables window. On the other hand, if I put the batch of routines into an Outlook project and run them via Automation then I can only think of the New Mail event as a possible trigger not requiring someone to perform some action first.
    But this would require a fairly constant stream of emails to elicit the new mail event. Can anyone think of another way? Basically I want to fire off one or more function routines in a Standard Module using Automation, but what would be the client program and what event in that program would do the job?
    Thanks for any ideas!

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    Could you create another database that contains the timer events, then opens the secure database, launches the functions, and then exits / closes the database?? Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

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

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    You could create a separate database with Access security enabled on it to limit user access. Run that application and let it trigger the events based on a timer. Don't put all the table links in that database, just use it to launch the actions in the other database. You could even It isn't clear whether the database containing the functions is a production database that might be open or not or what you expect to happen if someone tries to open it while some of this other business is going on.
    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: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    Thanks Charlotte, I had thought of this, or something like it. In my case, I put all the necessary functions in the separate restricted database and called the real database in the procedures using the ADO connection object , complete with the user name and password for a normal unrestricted user. I then password protected the code and, to make sure, made the whole thing an .mde file. But the trouble is that while this 'TRIGGER' database is open (which it would have to be to do its job) anyone can add a new project with code to do unauthorised things to the real database, or, more simply still, just link the tables in the real database into the TRIGGER database window.
    They could then view, edit or delete the data in these tables in the real database which I am trying to protect.
    So that wouldn't work. If, however, there is some way to call up the function routines in the real database from a subroutine attached to the timer event in the TRIGGER database then I think the problem would be solved. I could then create a special user who could open the TRIGGER database and open the form with the TIMER (in fact both could open automatically I think)
    and have permissions set to run the code for the module attached to that form but no other permissions with this or any other database on the system.
    Then the code would have the right to manipulate the data in the real database by virtue of doing an ADO log on using a normal user's username and password.
    But this sensitive data could be locked away from pyring eyes by locking the project and making it an MDE.
    So, the main question here is:
    Can I run code in second Access database via Automation from the first Access database in some way. Can it be called somehow?

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

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    I'm getting confused. Are you saying that you are *linking* the tables to the 'trigger' database? Why? You can talk to them through ADO without any physical link. Anyone linking to the 'trigger' database can't see tables that aren't there.
    Charlotte

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

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    Sorry Charlotte, this thing is making my brain so tired I'm not expressing myself very well.
    No I'm not linking the tables. I am using ADO to talk to the Production Database from the Trigger database just as you say. But it's the knotty and tricky question of permissions making things cloudy. If disallow all permissions for absolutely everything to the Admin user except opening the Trigger database and the Trigger form then I think all will be ok. At present the lack of restrictions means that the Admin user can still create links in the table window to the tables in the Production database and then open these linked tables.
    I was hoping to avoid all the complications that ensue from emasculating the Admin user (automatic log on means that other databases open without a log on box). Is there some dll or VB programme of some kind one can easily install on the system which can hook into a VBA function in either a standard or class module of the Production database and run it according to s repeated set interval of time (eg every 10 mins). Outlook has this feature but only for CHECKING for mail -which isn't an application event.

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

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    Why would you want to have no login to the 'trigger' database? If you are worried about compromising the security on the production database, then a login to the 'trigger' db seems like an obvious requirement. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I'm sorry, David, but I don't seem to be of much help to you in this. You seem to want something to run automatically but without having the production database open. The only thing I could think of off-hand would be a Windows scheduled batch file that would open the production database, but then you'd have the problem of *closing* the database again when you had run your function. DLLs are libraries, which makes them passive unless something is calling them, and I don't see how a VB program would be any different from opening a trigger database.
    Charlotte

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

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    Charlotte replied to this with:

    <<Why would you want to have no login to the 'trigger' database? If you are worried about compromising the security on the production database, then a login to the 'trigger' db seems like an obvious requirement.>>

    If you don't have any security on your database for an Admin user, your are inviting anyone to go into it and make changes, especially if Admin is the DBO. Why don't you use a shortcut that specifies a user other than Admin on the command line so there is no UserID/Password prompt, give that user the ability to do what needs to be done, and disable the Admin account completely?
    Wendell

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

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    Well, Outlook actually can do the functions I require without opening the production database at all.
    It is just that Outlook has no regular timed event that can be used to set off the routine functions required.
    It is really no more complicated than asking Outlook to check for mail every 10 mins (which it does as a matter of course) except I want Outlook to trigger, via automation, a series of routine functions in my production database (every 10 mins say).
    Eg: Someone sends an email requesting information.
    Outlook is programmed to add this request to my Access production database whenever the New Mail event occurs. This works fine. I then lob into an internet cafe in Saudi Arabia or somewhere and read and answer the enquiry using as ASP based web application, adding my response to the same "Messages" table. Access will send this (again via Automation) but only if some trigger occurs to start the Access VBA routine that accomplishes this.
    It is precisely here we come unstuck.
    I suppose I could do it all in an email application but then you'd have to enter it all (again) in the database later. Much better if it is all the one process.
    I can't have a log on password to the Trigger database in case the computer crashes and spontaneoulsly re-boots. It is a long way to come back to Australia from Europe to type in this password.
    It isn't 'one degree of separation' at all!

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

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    Thanks, I was thinking along those lines too and I think this is the best approach.
    It's been pretty confusing with the multiple layers of authority and permissions involved as my overall solution involves three types of security: NTFS permissions, IIS Web permissions and Access database permissions.
    Why is the whole world so paranoid?

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

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    Comiserations! It would be nice to have a single unified security structure, but .... Wait till you add SQL Server security to the mix.
    Wendell

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

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    I hope never to get to that point!

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

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    <hr>Why is the whole world so paranoid?<hr>
    Because there are a lot of people out there trying to see whether they can *get* you! <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Charlotte

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

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    That's the best defintion I've heard of Paranoia!

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

    Re: Trigger VBA Routines Regularly but securely (Office 2000 Sr1a)

    Just because you're paranoid doesn't mean they're not out to get you! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    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
  •