How to run my vba script from Windows 2003 for Servers?

[robs23]

Hi Guys,

I'm doing a database that me and few other people of my company will be working with. My current Db will be split into 2 files (one with forms for end users and one with tables to be placed on the network drive on our server). The issue is that, there is supposed to be an automatic notification system, that would open the database (the one with tables) once a day, would do some calculation on it and save it back. It would probably send mails to appropriate recipients based on personal information from the database.


My question is: in what form I should provide the file for "Microsoft 2003 for servers" to be able to read and execute my instructions? Most of my functions I'm hoping to use in the final script is in Access VBA, and I wouldn't like to code them once more in other language. I'm going to use Server's windows task scheduler to trigger the file with my code at specified intervals so probably exe or vbs file would do, but maybe there's other, easier way? This is my first bigger project in Access so sorry it my question is silly.

Robert

[hapm]

The easiest I could think of, is to prepare an access database that calls your code from an autoexec macro. You can then simply open the database starting access with the path to the database as a parameter. Not the nicest or best solution, but IMHO the easiest.

[robs23]

@hapm thanks for answering my question. I found out that I can instal Access Runtime Environment on the server and - thanks to it - it will be albe to read my Access files and execute code they contain. Any experience with this way of doing anyone?

[ItsMe]

Sure, Task Scheduler can successfully launch and run an Access file as long as the file can operate autonomously in a Run Time environment. It may be less of a security hassle to create a .VBS file (as an admin) using the server and on the server that, in turn, launches the Access file. Use Task Scheduler to launch and quit the .VBS file.

Also, edit the server's registry, after installing runtime, to Trust the folder where you will store your Access files (the Access files that the server will run).

[robs23]

Also, edit the server's registry, after installing runtime, to Trust the folder where you will store your Access files (the Access files that the server will run).

And this is exaclty I'm dealing with now. I managed to make my admin add that registry key but unfortunately I have to wait till Sunday when the server can be rebooted I hope this will do the trick.

[ItsMe]

It may require a reboot but most likely will not. Imagine a User with a full version of Access clicking the prompt to "Always Trust". The Access file will launch a second time without the prompt and without a reboot.

[robs23]

I'm not that sure about that. I already changed registry's key but I'm still getting those strange popups when trying to run code. That's why I think reboot is needed. Besides, as far as I know most of direct changes to the registry need to be followed by reboot as many programs read registry only during startup. At least I hope this is the reason..

[hapm]

If a restart is needed highly depends on what you change, or to be more precise: when the value you changed is read from the registry. For access options, this happens when access starts up.

Would be a difference when you change an option for the windows explorer or a windows service. In that case you would need to restart the explorer or the corresponding service. Only options I now that require a full restart are related to drivers, the windows kernel and the critical windows services. As some options influence multible services or the direct influence isn't known, people tend to tell you to restart the computer after changing them.

[hapm]

Is the folder you added a network folder? If so, don't forget to allow network folders as secure locations as well. If you use the office group policy template, all this options can be easily deployed using group policies.

[ItsMe]

...At least I hope this is the reason..

As hapm mentioned, a reboot is not always needed. I do not look forward to or enjoy the reboot process but I typically do them if there is a hint the registry was changed. It is just one of those things. Much easier to take two minutes and do a reboot than 10 hrs of installs or even 20 minutes of installs.

But yah, I would be concerned if you are still getting the warnings. The only thing I can think of is not all the keys needed were added or their value did not get edited correctly. To add to what hapm mentioned, there is a key needed to trust network paths vs. local paths. Does not sound intuitive since you are working on the server itself and the path is likely local. Maybe there is a something to it though, multiple Domain Controllers, Virtual Machine, etc.

[robs23]

As to whether reboot is needed or not - I'm no expert, I just think reboot definitely won't make any harm and possibly can help.

Is the folder you added a network folder? If so, don't forget to allow network folders as secure locations as well.

We'll it is a network location for the end users, but it's local drive for the server. So I think this step wouldn't be necessery, however I added it just in case.

But yah, I would be concerned if you are still getting the warnings. The only thing I can think of is not all the keys needed were added or their value did not get edited correctly.

And this is exactly what worries me. I think 2 things might have gone wrong:

1) My IT guy made a typo or did any other mistake (he tends to work in a quick and dirty fashion at times)
2) The information I base on is wrong or incomplete. I base on posts by Nathan O., Microsoft support from this site. Let me quote:

When using runtime you will need to create the trusted location within the registry.The following link shows the path for Access 2007 Runtime.
http://www.accessjunkie.com/Pages/faq_33.aspx

If you are using Access 2010 runtime, the path will be similar but instead of 12.0 you will use 14.0:

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\A ccess\Security\Trusted Locations\Location0]
AllowSubFolders (REG_DWORD) = 1

Path (REG_SZ) "C:\<path to database here>"

This also assumes the file is on the local machine.If it is on a network share then you will also need to adjust another key:
HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\A ccess\Security\Trusted Locations

I'll be very grateful if you could check this... Thanks for all pointers so far.

Robert

[hapm]

AFAIK the key is correct. Ask your IT guy to export the key and check the values in the resulting .reg file. If you aren't sure past the file here, so we can take a look at it. If you have an acces instance on the server, you can check the security center to see if the location is properly listed and not greyed out (indicating a wrong or non existend path).

[robs23]

There's only Access Runtime instance on the server. As I understood correctly, Security Center opened from that runtime instance should list the location we added, right? Doesn't it make any difference that it's only runtime instance? I may be wrong but I think the Microsoft support on that site said that in runtime sequrity center is always greyed out..

[hapm]

Ou well, if you don't have full ms access you wont have a full sec.center. Another idea that comes in my mind to check: be sure that the reg key is set for the user that executes the script. As HKEY_CURRENT_USER is a user specific space and will be different under different user accounts. So it isn't helpful if the key is set for some admin account that was used to set things up, when the task runs under a different account where the key will not exist.

[robs23]

I don't think this is the case, we added those keys and ran my file almost right away, definitely before user was changed. Unfortunately I can't check this today as admin is off.. He's back tomorrow and I'll check/give my feedback then.