Finalise database and implement??

[turbo910]

Hey guys

I need your awesome expert advise once more.

Basically ive finished my database for the mean time and i want to implement it into the business i have created it for for a tral period of a month.
Heres the database in boht 2007 and 2003 form:
Attachment 441

K so give me your views on the databse and give me any tips and changes i should make, in order for it to run successfully within a business environment.

NOW the major question and i guess theres been alot of posts about this which i have seen a few of but i dont understand alot of them.

WHere do i go from here in order to implement the db into a business enviironment?
Do i create and exe. file somehow?
How do i make the db secure so the users cant effect it?
Do i have to split into back end and front end??
All of these kind of questions, but the end result what i want the user to be able to do is look on the desktop and double click an icon exe. or equivalent and load the Main menu form and then go to all 4 forms from that point.


Any help guidence would be appreciated greatly and please spell out all instructions for me as i am not that good with access yet

Thank you

[Rainlover]

Sorry but it won't run on my machine.

Please test this on another maching and repost after fixing the bugs.

[NTC]

all multi user Access applications must be split

if the users are in a windows environment but you can not guaranteee that they have an Access license - then you must use the runtime license method

users must all be on the same LAN (or its effective equivalent of a private high speed WAN with terminal services) - - - otherwise you need to consider the www.AccessTables.com service to share/sync tables; or change to a web architecture.

it sounds like you are wanting a commercial install feature; that would be a separate product like installshield that must be integrated; in normal Access installation you just put the FE on the local C: and make a Windows shortcut to the desktop manually if so desired.

[turbo910]

Ok so hey ive split my database.
Packaged the front end as an ACCDE
And trialed it.

Some things i would like to clear up now

Firstly are there any security features that i can put in place so no one tampers with it??? i have already made it ACCDE so anything else i should do??

I have the db set up with a macro so when runtime is installed and the db opens the main menu opens automatically and is a pop out screen but half of it is cut out becasue the pop out makes it centered, is htere any way i can get the whole form to show on screen without automatically making it maximise?, if not how do i make it automatically maximise??

Also obv i have split the db so i have both the be and fe on my computer that i created the database on. Now is it as simple as creating a folder in the user computers documents and placing the be in there or should i do something else to install the be on the users computer?? If you dont understand me please just say and ill try to explain more

thank you

[Rainlover]

I didn't write this but it is good.

Congratulations! Your database has become so popular that others would like to use it. Just copy it to a shared location on the network and create shortcuts, right? Wrong! This is perhaps the largest cause of Access database corruption. Multiple users accessing the same .MDB at the same time is just asking for problems.

What is the correct method to share an Access database?

NOTE: This FAQ is meant to provide the best practices for sharing a database with others. Incorporated into these best practices are the concepts of Maximum Security, Future Manageability, and Higher Degrees of Scalability. Simply put, your database will be as secure as possible, as easy to modify as possible, and as easy to migrate to a larger platform when and if you ever outgrow Access.

A correctly shared database will be split into two parts. One Back-End database (hereafter referred to as BE) located at a shared location, and one copy of the Front-End database (hereafter referred to as FE) for each user.

How do I split a database?

*** Always backup any database before making changes of any kind. ***

Microsoft Access has a built-in utility for assisting you with the splitting process.

Choose Tools>Database Utilities>Database Splitter

This tool provides the same functionality as splitting the database manually as described below.

Splitting a database manually

Create the BE
Create a brand new, empty .MDB.
Import all the tables from your original .MDB into it by doing the following:
..Choose File>Get External Data>Import…
..Browse to your existing database.
..Select all tables.
..Choose OK.
You now have a BE.

What do I do with the BE?

The BE should reside permanently at your network share.

Note: This new BE should be named differently than your FE. (Ex: FE name = Accounting.MDB, BE name = AccountingBE.MDB) Additionally, all of your users will need Read/Write/Delete permissions at this location.

Create the FE
Open your original .MDB.
Delete all tables and their relationships (if any).
Link to the tables in your newly created BE. Make sure your BE is in its permanent network location. If you change its location after the links in your FE are created, they will be broken and you will have to relink them. Link them by doing the following:
..Choose File>Get External Data>Link Tables…
..Browse to your BE.
..Select all tables.
..Choose OK.
You now have a FE.

What do I do with the FE?
This new .MDB file becomes your new development copy. Guard it with your life. Back it up frequently, as well as the new BE you have just created which houses all of your data. Design changes from now on will take place in this copy and this copy alone.

What do I distribute to my users?
Individual copies of this new FE are what gets distributed to your users.

IMPORTANT: EACH USER GETS THEIR OWN COPY OF THIS FILE. THIS COPY SHOULD BE PLACED ON THEIR OWN LOCAL MACHINE.

Note: To guard against unwanted changes to your code or to prevent design changes to your forms or reports, please see the Securing Your Code section below for details about distributing an .MDE instead of an .MDB.

This new architecture creates some new obstacles of its own. When you make a change in your FE .MDB development database, these new design changes are not immediately reflected in each users personal copy of the .MDB. This is good and bad. Good because you can now fully test a new method under controlled circumstances before it is ever distributed out into a production environment. Bad because every time you wish to deploy a new version of your database, you will need to redistribute copies of the .MDB file to each of your users. There are many good reference articles in UA for redistributing an .MDB automatically without going machine to machine. (Use the UA Search function to find them)

Relinking a FE

Another advantage that this structure gives you is the ability to easily create a separate testing environment by relinking your development FE to a different BE other than the production BE. Simply copy the BE to your local machine and relink the FE to this local copy. To relink your FE to the new local BE, perform the following steps: Choose Tools>Database Utilities>Linked Table Manager.
Select all of your tables.
Check Always prompt for new location.
Click OK.
Browse to your local BE.
You now have a fully functioning split database to test with. Any changes made to the data will be local changes only. Nothing in your production BE will be affected and your users can continue to work. When you have fully tested your modifications, relink your development FE to the production BE on your server and redeploy your modified FE to your users.

Note: If all the linked tables are from the same BE, you will be prompted once for the BE location. In an installation that has multiple BE’s (discussed later), you will be prompted for a location for each table (not each BE). To avoid this, you can re-link the tables in groups based on the BE.

Q: "If my users make a change in their copy of the .MDB, won’t they be overwritten when I redistribute a newer version?"

A: Yes. Any changes/customizations made to a user’s local copy of the MDB will be lost. In this scenario, good communication with all of your users is critical.

There are options. One solution would be to provide them with an additional brand new, empty .MDB that has been linked to your BE. This will allow them to create any adhoc object they want. To prevent unwanted changes to your data, a good practice is to provide Read-Only links to the BE.

Q: "Can I use a shortcut on my user’s desktop to open the FE?"

A: Yes. But the shortcut should point to the LOCAL COPY of the FE.

Q: "But I’ve been using a single shared copy for a while now, without any problems. Why should I change?"

A: Consider yourself fortunate. If you haven’t experienced problems yet, as the usage and number of users of your database increase, so will the likelihood of corruption. Keep in mind one of the purposes of this FAQ is to provide best practice guidelines for maintaining your database. Have you ever tried making design changes to a database that is being actively used? It is awkward at best. Who is logged in the database? Are they using the object I want to modify? Will my changes affect the workings of my database? All of these questions and more can be averted by implementing the changes suggested here.

Special Circumstances

Q: "My users access my database through a Terminal Server or other Thin-Client technologies. (i.e. Citrix) So they do not have a local machine to load the .MDB on. Can I give them a shared copy of the .MDB to use?"

A: No. In a Terminal Server environment every user will still need to have their private network share or their own desktop where their own private copy of the .MDB file should reside. They will access this file as usual.

Q: "What do I do differently if User Level Security (ULS) has already been applied to my .MDB?"

A: In order to preserve the ULS security in the BE, a slightly different approach to splitting the database should be used. Make a duplicate copy of the original .MDB.
Open the new copy.
Delete all objects except the tables and relationships.
Compact & Repair the database.
Rename the new file to distinguish it as the BE.
Move the BE to its permanent network location.
Link the FE to this new backend as detailed above.

The Workgroup file (.MDW) should be located in the same folder as the BE. Your .MDW file should be named differently than your BE. (Ex: BE name = AccountingBE.MDB, WorkGroup name = AccountingWG.MDW) Your shortcut to open your database will look similar to:

"Full Path to MSAccess.EXE" "Full Path to local copy of the FE" /wrkgrp "Full Path to Workgroup file"

Note: In Access 2007, the User Level Security feature has been removed from databases stored in the new .ACCDB format. It is still available in databases that are saved in .MDB (ACC2003) format

Securing Your Code

Many times it is desirable to secure the objects you have created to prevent unwanted changes or disclosure of methods within your code. Creating an .MDE from your .MDB solves this.

Follow these steps to create an .MDE. Open your FE .MDB.
Choose Tools>Database Utilities>Make MDE File…
An .MDE file is the exact same thing as an .MDB, only all the internal code has been compiled. It will run faster and will not allow Design changes to any forms, reports, or modules. This is a very good thing, as it prevents unwanted changes from occurring in your database that you have worked so very hard on. This new .MDE file is what gets distributed to your users instead of the .MDB in the above example.

Since .MDE’s get compiled, there is no longer any means to gracefully tell the user that something bad has happened. Therefore when using an .MDE, error trapping is a must.

Note: Security in an Access database is kind of misleading. Protecting your code can be accomplished as described above. Protecting you data is something else entirely. There are steps you can take to make it more difficult to get at the data, but a determined person, given enough time, can overcome any hurdles you place in their way. Some common steps to take are: Password-protect the BE. *See Note.
Password-protect the FE.
Hide the database window.
Remove all the Toolbars and provide your own custom ones.
Disable the Shift-Bypass key.
Disable context menus.
Do not allow access to any object except a form or report.

There are many good reference articles in UA for securing a database. (Use the UA Search function to find them)

Note: To apply a database password you must sign in to the database in Exclusive mode. This can be accomplished by doing the following: Open Access. (MSAccess.EXE, NOT your database)
Choose Open an existing file.
Browse to your BE or FE. (Single-Click only)
Click the dropdown menu on the Open button.
Choose Open Exclusive. (Your database will open)
Choose Tools>Security>Set Database Password…

When applying a password to the BE, this should be done before the links from the FE are created. If the links are already in place, manually delete the linked tables from your development FE. Then follow the steps outlined above in the Create the FE section to re-establish the links in your FE to your new password protected BE. You will be prompted for the password during the linking process once, but after that you should not receive any more prompts. The password will be permanently stored in the link. Keep this in mind if you ever have a need to change your database password. You will need to delete and recreate all the links in the FE.

If your data requires vigilant protection, you may need to consider a BE other than Access that can provide more security.

Access 2007 boasts of increased security potential, but as of this writing, no definitive answer is available.

Q: "I cannot create an .MDE file from my .MDB."

A: Access can only create an .MDE file when the database version is the same as the version of Access you are using. (Ex. Access 2003 cannot create an .MDE when the database format is 2000) Convert the .MDB into your version of Access and create the .MDE.

Another reason that .MDE creation may fail is because there are problems with your code. Since Access has to compile all the code, there cannot be any errors. Open the VBA window, (Alt-F11)
Choose Debug>Compile
Fix any problems that are found with your code.
After a successful Compile, this option will appear grayed out on the menu.

TIP: Make sure at the top of EVERY module these two lines of code appear:

Option Compare Database
Option Explicit

Without going in to a lot of detail, these lines will make it much easier for you to find errors with your code. Take the time to set this as the default, by going to the VBA window (ALT-11) Choose Tools>Options…
Choose the Editor tab
Select Require Variable Declaration
This will add these lines to any new form/report/module that you create. You will be a better programmer for it.

Conclusion: Splitting a database may at first seem to be a lot of work, but the advantages to doing so are very great. Besides avoiding possible data corruption issues, this method will provide maximum security, a single point of manageability, and make it much easier to scale up to a larger back end when you outgrow the Access database container.

Q: "How do I maintain my new architecture?"

A: Even the best designed database structure will need maintenance from time to time. Backing up your database is perhaps the most critical step. You will need to back up your BE (which houses all of your data), as well as your FE development database (which contains all of the queries/forms/reports/macros/code). Compacting of the BE should be done on a regular basis (Tools>Database Utilities>Compact and Repair Database…), more frequently if your database performs large amounts of action queries (Make Table/Insert/Append/Delete). Compacting should take place wherever the database resides, when no one is using the database. It should NOT be performed across the network, which could lead to corruption problems. If you do not have direct access to the server where your BE resides, you can copy the BE to your local machine, perform the Compact, and then move the BE back to its original network location.

Q: "How do I know when I have outgrown Access?"

A: Ask yourself these questions:
How many concurrent users do I have? Microsoft states that the maximum concurrent connections to an Access database is 255 users. This is much higher than can be expected. Though there have been documented examples of over 100 concurrent users when using very advanced programming techniques, generally the number of users is much lower when performance degradation begins to be seen. It has been my experience that with a moderately complex database using the standard "bound form" design, a more realistic number is more like 15-20 users. There are many methods for improving performance and these should be closely examined before deciding to upgrade. Realize, though, that upgrading to a more powerful engine will not solve the problems of a poorly designed database. A non-normalized structure, poor architecture, and bad data access techniques will still bring a fast engine to a screeching halt.

How large is my database? An Access database is not limited by number of records. It is limited by file size. Starting with Access 2000, Access has a maximum file size of 2 GB. (Previous versions were 1 GB) Choosing Tools>Database Utilities>Compact & Repair Database… may reduce the size of your database dramatically. If this needs to be done on a regular basis in order to keep your database under 2 GB, then it may be time to start looking for more robust options. (Ex. SQL Server, MySQL, etc.) Another less-used, but available option is to create multiple Access Back-Ends for a single application. Compartmentalizing the tables into separate databases can be a good interim step if moving to a larger BE is not an immediate option.

How secure are my data needs? No Access database is 100% secure. Many roadblocks can be put into place to keep honest users honest, but if data protection is a concern, consider moving to an engine that supports higher levels of security.

Where do I go from here?

Maintaining a split database will require a little bit more effort. You have to be aware of all the entry points to your application and take them all into account when making changes. For example, making a structural change to the BE may "break" the local FE on your user’s machine. It may not work until they receive a new copy of the FE.

There are many examples of tools you can employ to help facilitate the process of a change. Things like: Auto-Updating Front-Ends that get deployed in a touchless fashion.
Automatic Back-End Relinking code.
Read-Only linking techniques for adhoc .MDB’s.
Code that can temporarily halt access to your database while you make structural changes .
Code that tells you who is currently logged in to your database.
Routines for automatically compacting and backing up your database on a schedule.