Newbie now ready to start the show - autoexec suggestions - ?

[David618]

I think I now have everything the way I want it (many thanks to several on this forum!), but now I want to create and autoexec macro to run when I kick off the database. Basically, I would like it to hide all of the ribbons, menus, shutter bar, etc. so that no wise guys/gals are able to mess up anything - or venture where they're not supposed to and just open up the Log In form (frmLogIn the only form that pops up and appears centered on the screen). At the same time, I would like to be able to bypass that action myself in order to get in there and make any revisions (or debug!) when I need to.



I'm sure there are different ways/ preferences, but being a newbie, I want it to be as simple (and low fear) as possible. My initial thinking was to create a simple autoexec macro that can be bypassed by holding the shift key. I'm certainly willing to keep an open mind about it, but (again) simplicity is certainly my preference. Any suggestions?

[June7]

Many of those options can be done in the project settings - File > Options > Current Database.

The only thing I have used AutoExec for is to disable Ctrl+p shortcut because VBA can't do this for the entire project in one action. I use VBA to customize ribbon.

Holding down shift key while opening db will override macro execution and most project optional settings.

[David618]

Yes, but I want to change those settings just for the end user so they only see the Log In form and not see the ribbons, menus, etc. I don't want to change them back for myself, so that when I'm in, I can bypass those settings. Is there some (relatively simple) VBA that can do the same thing, or (as a novice) am I better off experimenting and figuring out which actions will do what I need with and autoexec macro?

[June7]

That's what the project settings will accomplish. User won't have most of the ribbon, navigation pane is hidden (although F11 will expose unless you turn off function keys), db opens to LogIn form. Hold down shift key when you open and everything is back available. Of course, anyone can hold the shift key. Access was not really designed to be a secure multi-user db.

Review http://office.microsoft.com/en-us/ac...010341741.aspx

[DepricatedZero]

Go through your project settings and get what you want in order - hide the navigation pane, etc - and publish to an ACCDE. The ACCDE will prevent end users from opening it up with the shift key and bypassing control to edit it themselves. In 2007 the button to "Make ACCDE" is on the far right of the Database Tools ribbon. You may need to split the DB in order to do this, I don't know if you're working with a split DB or not.


That said, I also run this command on the Form_Load event of our Splash Screen

    DoCmd.ShowToolbar "Ribbon", acToolbarNo

[David618]

Thanks both for the helpful advice. I don't see a "Make ACCDE" button under Database Tools in 2010 and I'm not sure what "splitting" the DB is. Sorry - I'm a true novice!

[DepricatedZero]

Should also be under Database Tools. I'm using 2007 not 2010 so YMMV but there's an icon to split the database(shown below)

The purpose of splitting the DB is pretty much for allowing multiple users to access the same DB. It creates 2 accdb files: one Front End, one Back End. The Back End consists of your tables, while the front end consists of forms, queries, macros, etc. We keep our backend on a server, with the front end distributed to desktops as ACCDE so that it can't be modified.

In the above picture, on the far right of the bar is the Make ACCDE button also - which creates the 'locked' copy of your DB.

[David618]

That sounds like it might be what I will eventually need. This database will be used (initially) by just a handfull of users (while it's being tested) but eventually used by a multiple # of users (perhaps up to 50 in a week?). I don't know how many that will amount to at any given time (maybe 10 concurrent? - although people do tend to do things at the last minute). What would be the best way to set that up (in order to avoid any crashes - or at least minimize 'em)?

[DepricatedZero]

If the team you're building this for has a shared drive on your company's network of some sort, you could set up a directory on the team's shared drive to put the Back End on. You could put the front end up there as well so people have the ability to copy it down. We actually distribute a batch file that forces the users to download the FE locally every time they run it. The benefit being that if we need to make a change to the Front End, we can slip the change in without having to make people log out, and people will be automatically updated the next time they log in

[ssanfu]

Split the database. Each user gets a copy of the FE. Do not have a copy of the FE on the server and use a shortcut to open the database.

Splitting the database makes it a whole lot easier to change the forms, queries and reports without worrying about affecting the data.

For more info, see
http://allenbrowne.com/ser-01.html
http://www.kallal.ca/Articles/split/index.htm
http://office.microsoft.com/en-us/ac...026.aspx?CTT=1
http://www.opengatesw.net/ms-access-...-Practices.htm

My $0.02

[David618]

I like that idea - just never done it! Please pardon my ignorance, but how would you create the batch file? We would probably have the front and back end at the same location on the company's network drive that everyone in the company has access to. I can forsee times when I would probably be making changes to both ends as an "upgrade," especially after the initial roll out...

[June7]

Here's how I manage split db: http://forums.aspfree.com/microsoft-...ue-323364.html

I used to use a batch (VBScript) file but finally figured out how to do the copy code inside the Access file. It is described in link.

[DepricatedZero]

I like that idea - just never done it! Please pardon my ignorance, but how would you create the batch file? We would probably have the front and back end at the same location on the company's network drive that everyone in the company has access to. I can forsee times when I would probably be making changes to both ends as an "upgrade," especially after the initial roll out...

sorry I totally meant to include the bat script

@IF NOT EXIST "Z:\NETWORKPATH" GOTO NOTCONNECTED
@ECHO Connected.
@GOTO LOADFILE

:NOTCONNECTED
@ECHO Not connected to Z Drive
@PAUSE
@EXIT

:LOADFILE
@ECHO Transferring Service Manager.
@xcopy /y/v "Z:\NETWORKPATH\ServiceManager.accde" "%userprofile%\Desktop\"
@ECHO Finished copying Service Manager.
@START "Service Manager" "%userprofile%\Desktop\ServiceManager.accde"
@EXIT

Use something like that. To create it just enter it into a blank notepad txt file and save, then change the extension to .bat

It's important to note that this creates a local copy and doesn't just open the copy on the server. You don't want anyone opening the copy on the server - that will lock it for use.

[David618]

No, I don't want to lock anybody out. The way this will work is, employees will update by department "waves" (e.g., 1st week department A, next department B & C, next department D, etc.). So far there are 135 users (not all entering at once). At some point there will be 450, but again, that will be handled in "waves." They only need to get into it and update their information once a year. Thanks for the batch script example!

[robsworld78]

This will lock it up and make it professional. Always make a backup before trying things.

First follow these instructions so users can't hold the shift key and undo everything.

http://support.microsoft.com/kb/826765

Then split your database as it should be. Its good to put a password on the backend so nobody can open it up.

Also go into all your forms settings and change "popup" and "modal" to "yes". This will make it look professional when everything is finished.

Now when your are ready to deploy the package to users make a copy of it because after you do this you can NEVER get into it to make changes. NEVER do the following steps to your developer copy.

1. Open your main splashscreen/menu screen and set "popup" to "no" and "modal" to "yes". Then on the "OnOpen" event add the following line in the vba editor.

DoCmd.ShowToolbar "Ribbon", acToolbarNo

2. Close it and make sure you don't open it till your finished.

3. Go into the database options and uncheck "Display Status Bar", "Display Document Tabs" and "Display Navigation Pane". Make sure you have "Compact on Close" checked on frontend and backend. Also make sure you select your splashscreen/menu screen next to "Display Form". Add in an application title and icon and select "Use as Form and Report Icon".

4. Now close the database and re-open it, it will look professional, nobody will even know its microsoft access.

This only works if all forms and reports are set as popups, setting the modal to yes or no is optional but I recommend yes so 2 forms can't be opened at once. Because you set the main menu screen as no popup it becomes the face of the program and that's all you will see. It took me a while to find the solution and this works great.