Worth Splitting database? (1 Viewer)

[alktrigger]

I have a database that I created (it was my first) and it is in desperate need of overhaul. Its only being used by one person, is it worth the effort to split it into a front/back end type database, or is that too much trouble?

If it is suggested, is there a useful tutorial somewhere to explain how to go about this?

 

[shadow9449]

The benefit of splitting a database is primarily to allow the application to be used efficiently by more than one user on a network.

A really good explanation of the concept and process can be found on Albert Kallal's website:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

SHADOW

 

[MSAccessRookie]

The benefit of splitting a database is primarily to allow the application to be used efficiently by more than one user on a network.

A really good explanation of the concept and process can be found on Albert Kallal's website:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

SHADOW

The above statement is ABSOLUTELY true, as the article points out. I would like to add that splitting the database usually does not take much time, and takes very little effort, so it might be worthwhile after all if you are not the primary user of the database. If you split it, you can make changes and test them (most of the time, at least) without affecting the user's work.

 

[HiTechCoach]

Most software development platforms split the data (tables) from the application (forms, reports, etc.)

The benefit of splitting a database is primarily to allow the application to be used efficiently by more than one user on a network.

I do agree that in a multi user environment that you must not share a database so that means it must be split.

IMHO, the reason you split the database is because it make development easier and also protests your data. This has nothing to do with the number of users. To me it is able "best practices" for software development and data integrity.

The dis-advantage of being split (only one):

The only thing that might seam like a disadvantage to splitting your database is that if you want to make table design changes, you have to open the back end. Thats is it!

Some of the advantages of being split:

1) protects the data. Can't make table design changes from the front end. If or when the front end gets corrupted, it does not corrupt the data.
2) can use a MDE/accde as the front end to protect the app.
3) can easily deploy updates (just replace the front end)
4) can easily link to test data while developing, then switch back to the real data when ready to deploy an update.
5) back ups are smaller since you only need to back up the back end (data) regularly

and also do not forget:
6) the database is multi user: each user can have there own copy of the front end so it is not shared. You can even have multiple versions of the front end for different versions of Access that can be used at the same time.

As you can see, most of the advantages have nothing to do with being multi-user.

I start out every database split. I will not work on any database that is not split. Why? see the above list of advantages for a start.

Also see:
Auto FE Updater

Windows Vista now supported.
- Starting an exe, such as the Sagekey runtime system, is now supported.
- Fixed the "The MDW account name and password in the INI file weren't valid for
opening the MDB to determine the version of the MDB" message when in a mixed
operating system work station environment.
- Emphasized using %appdata% as a location for the Front End MDB/MDE in the sample
ini files and documentation
- added error numbers to the messages. It was getting a bit difficult tracking down
specific error messages in the code.

 

[alktrigger]

HTC,

You say that you start with the split database? if so, once split, if I want to add a whole new table, would I need to go to backend to create it?

What happens when I move the files? Since I'm not placing the files in a shared drive, would they automatically update when I move them, or would it prompt me on startup to find the backend, or worst case does it cause a run time error?

 

[SOS]

You say that you start with the split database? if so, once split, if I want to add a whole new table, would I need to go to backend to create it?

Yes, put it in the backend and then link from the frontend. And I would suggest splitting ALL databases as it gives you more protection from losing data from corruption. It isn't perfect but it does provide an extra layer of protection where a single database could corrupt and then you might lose all of your data.

 

[alktrigger]

this doesn't stop me from creating a table in the front end for report purposes, right?

 

[SOS]

this doesn't stop me from creating a table in the front end for report purposes, right?

Nope, you can do that.

 

[alktrigger]

What happens when I move the files? Since I'm not placing the files in a shared drive, would they automatically update when I move them, or would it prompt me on startup to find the backend, or worst case does it cause a run time error?

I think this got overlooked, any comments?

 

[SOS]

I think this got overlooked, any comments?

When you move the backend you would need to relink from your frontend. You can do so with the LINKED TABLE MANAGER.

 

[HiTechCoach]

What happens when I move the files? Since I'm not placing the files in a shared drive, would they automatically update when I move them, or would it prompt me on startup to find the backend, or worst case does it cause a run time error?

Good question.

You can use the built in link table manager to relink the tables to the new back end location.

I normally include my own Link Table manager code. I use a splash screen that checks for the back end. If the back end is not found, then run the Link Manger code. You could first look in the same folder as the front end, if not found then ask for the location.

 

[Atomic Shrimp]

Others mentioned this, but I want to pick it out - conceptually, splitting the data away from the presentation of the data is a neat idea - it's not unlike the concept of disentangling content and presentation in HTML with CSS - and quite apart from all the good reasons mentioned above by others, it just makes sense to maintain a clear distinction between two different kinds of things.

 

[alktrigger]

I'm using this as an excuse to not only build a cleaner database, but to get rid of the mess I made when I first started on it.

 

[gemma-the-husky]

just one other thing

there ARE a small number of things that WON'T work in a split database

One important class of these things, are the various INDEXED SEEK methods used to search recordsets. If you use these, they will not work adfter you split the dbs.

FIND methods still work.


-----------
General well worth splitting the dbs, and a good habit to get into anyway. Once you see what's going on, its easier (or as easy) to do it by hand. Most of us have some standard form, I would think, to pick the backend, and automate the table relinking. Once you get even more advanced, you can use this idea to link to SQL databases.

eg I have one with a my button, and a client button, as the relative paths stored are different for my developemnt copy, to the paths the client uses - so when I redistribute a new front end, he can just reconnect with his client button.