Access, in my opinion, is a great front-end but as an actual database [back-end], it's pretty scary!
10+ reasons to split an Access database: "
Splitting your Access database offers numerous advantages, including increased flexibility, security, efficiency, and scalability.
Access is a desktop database, and you can store data and interface objects in the same file. But most developers agree that a split database is easier to protect and maintain. When you split an Access database file, you end up with two .mdb files instead of just one:
- The backend stores the data in relational table.
- The front end stores the interface objects.
By linking the two files, users can view and manipulate the data in the backend via the forms and reports in the front end. This arrangement solves a number of problems inherent to the Access file structure.
Note: This article is also available as a PDF download.
1: Multiple users share the data
Perhaps the biggest incentive for splitting a database is to supply data to multiple users over a network. By storing the backend on the file server and distributing the front end to workstations and users, many users can access and manipulate the data.
Whether you store the front end on local systems or on a server is an ongoing debate among developers and administrators. There are pros and cons to both sides.
2: Your data is better protected
Whole books have been written on database security, but it’s enough for you to know that you must protect your data. One of the easiest ways is to split your database. Placing your tables in a backend file protects your database design because users can’t directly access the tables via the interface objects in the front end. Therefore, they can’t alter or delete tables, even accidentally. Most of the users working in the front end won’t realize they’re actually working with two separate files, so splitting the database will have no negative impact on your users.
However, this arrangement is not a comprehensive security lock on design. Users who know what they’re doing can still open the backend, if they have access to it. Just bear in mind that splitting the database will minimize accidents — but it won’t stop someone who’s determined to get at your tables.
3: You can plan for the future
If there’s any chance that your Access database will grow out of its skin, consider splitting the database. It’s easier to upsize a split database to SQL Server (or some other larger relational database system) because you can easily link the existing front end to SQL Server tables. That way, the organization has the advantage of storing data in a larger database with most of the perks that come with doing so, while still using the Access front end. (Most Access front ends will require light to moderate conversion to view and manipulate SQL Server data.)
Many organizations stop there. However, a modified Access front end that’s linked to SQL Server, can also buy you time. You can keep the Access front end in service while developing a more robust front end for the SQL Server data.
4: The user interface is easy to modify
Most databases grow and change with the business; they require new features or modified business rules. Changes to existing tables are rare, if you properly normalized them early on. Most changes will be in the front end in the form of new or modified forms and reports.
As long as your database is split, testing and implementing changes to the front end can occur with little or no disruption to users. You simply link the development front end to the production backend and test away. This won’t always be the case, of course, but testing new interface objects is easier in a split database configuration.
5: You can use a shared security model
Access 2003 and earlier have a workgroup security model that’s pretty good. It’s easy to understand and implement and does a decent job of protecting schema and data. If you split a database, security isn’t a problem. That’s because the linked tables in the front end will adopt the same workgroup security rules applied to the backend.
In addition, developers find securing a split database easier because the backend is less crowded. Many developers stop with the backend, but your needs should dictate how heavily you implement workgroup security.
6: Deploying a new front end is a snap
If the user interface and data are stored in the same database, you must replace the entire database every time changes are made. That means you must import data from the existing tables into the new version. That’s a lot of unnecessary work and can require remote access to the database if you’re not physically near the system and there’s no one in-house with the expertise to do it for you.
In a split database configuration, you simply replace the front-end .mdb file and relink the tables. It takes a few minutes and requires little interruption of users.
7: It makes life easier for offsite developers
A split database is easier for offsite developers to maintain and upgrade. The developer works offsite to implement changes and enhancements to the front end and then ships the new version to someone in-house who has the technical expertise to deploy it. This latter process is a simple copy and relinking task that doesn’t require high-end expertise. You can train someone to do it or even talk someone through it over the phone. Many developers write a routine that automates the process. All the in-house technician has to do is double-click the installation file. This opens up a lot of long distance opportunities that a developer just couldn’t manage as easily with a single database file.
8: Job security is enhanced
Once the database is in production and running smoothly, clients will want changes and new features — they almost always do. Knowing that you can develop and implement changes with little to no disruption, management is going to be more inclined to contract you to make those upgrades.
9: Multiple developers can work more effectively
A split database allows multiple developers to link to a backend in a flexible and efficient arrangement. Developing from a single database file would require precise and specific coordination and synchronization. Developing in a split database frees up resources for actual development rather than management.
10: Everyone’s using the same data at the same time
By splitting a database, you know that all users are accessing the most current data because everyone’s accessing the same data. Not only are they all accessing the same data, they can all update it at the same time. That means a change made by one user is almost immediately available to all other users. (Locking may slow things down.)
Having a backend corrals all the data into a single database file. That means there’s only one copy of that data to manage and protect. Changes are immediate and available to all authorized uses. Any administrative duties (which are few to none with an Access database) are implemented in the backend file, once.
11: Geography’s not a problem
A split database allows users in different locations to access the same data. For example, the backend may be on a server at company headquarters in Atlanta, but users from all of the country can access the data via their local systems.
12: Corruption is limited
Access databases are prone to corruption. One of the easiest ways to avoid this problem is to implement a split database, which is less prone to corruption.
13: It’s easier to get individual users back on track
Security in the front end is one way to limit user interference. However, some users require more flexibility than others and there are always trade-offs. Some applications will require tight front-end security, while others will allow more freedom to tinker.
When a user tinkers to the point of destruction, a split database is easier to repair. Rather than bringing the entire application and all its users to a screeching halt, you have only one user who’s unable to work, momentarily. The fix is usually as simple as recopying the front end for the troubled user.
Check out 10 Things… the newsletter
Get the key facts on a wide range of technologies, techniques, strategies, and skills with the help of the concise need-to-know lists featured in TechRepublic’s 10 Things newsletter, delivered every Friday. Automatically sign up today.
"(Via 10 Things.)
No comments:
Post a Comment