Should I split my database?

[Lateral]

Hi guys

I'm a newbie to Access and have an Access 2007 database that has 20 or so tables, 40 forms, 30 reports etc. It is only going to be used on a single PC at any time.

My question is this...I need to make any updates to the Front End (not the tables) as easy as possible as I am going to be adding additional functionality etc, so therefore am I correct in saying that I should be splitting the database into a separate back-end and front end?

Oh, I am going to be using the Access 2010 Runtime on the single PC.

Also, when your run a Backup via the Manage option when in Design mode on a split database, it looks like it only backups up the Front end???

Thanks for any answers you provide.

Regards
Greg

 

[namliam]

Splitting a database into frontend and backend, particular when you have forms and stuff that are going to be changed would be a good idea imho. Since the data can stay untouched while you replace the frontend

 

[Mr. B]

I would take an even more definitive position than namliam has taken. In all circumstances you should split your database. There are many reasons for this but the primary one is the one that you already understand. The only way to easily allow users to maintain there data and still be able to update or add functionality is to have the data in one file and everything else in another.

One of the most important consideration for splitting your project into a front-end and back-end is that this is how MS Access has been designed by Microsoft.

 

[stopher]

No you don't "need" to split to front/back end. Access is designed to work quite happily as a stand alone single file and in my experience does that very well i.e. I have never experienced a problem that I would put down to the fact that it was a single file. I've had some single apps running for more than 12 years. If you know you are 100% working on a single PC/user and you have a good backup protocol in place then I think there is little to be gained by splitting your d/b (since you will want to backup both front/back end anyway). Some benefits of single d/b:
- easy to distribute (as a standalone app), the samples on these forums being an example
- easier to develop/maintain, only need to create table and no links
- easier to back up

That said there are many reason for wanting to split your d/b, some already mentioned. Also read here:
http://www.techrepublic.com/blog/10-things/10-plus-reasons-to-split-an-access-database/

hth
Chris

 

[Pat Hartman]

Apparently stopher never has to change any objects in a database that contains live data.

Split is best practice. If you are using the database for yourself, you could leave it unsplit but never if you are creating the database for someone else. You always want to be separate from their data because you don't want to become responsible for it or hold them up while you do something to the FE.

 

[stopher]

Apparently stopher never has to change any objects in a database that contains live data.

By objects, if you mean reports, forms etc then yes, sure I have. But of course if I'm editing a form I can't possibly be maintaining live data at the same time in a single user/pc environment. Maybe I misunderstand your point.

 

[Pat Hartman]

And for each of the 20 users with embedded data, you have them send their monolithic database to you and you modify each separate database and send it back and hopefully they don't need to use it while it is in your custody. Sounds efficient to me. Or, maybe you are being efficient and make all the changes to your own master copy and then import the users data.

Do whatever works for you if you are the owner of the database but best practice is to split the database when it is being used by a non-developer.

 

[stopher]

And for each of the 20 users with embedded data, you have them send their monolithic database to you and you modify each separate database and send it back and hopefully they don't need to use it while it is in your custody. Sounds efficient to me. Or, maybe you are being efficient and make all the changes to your own master copy and then import the users data.

Do whatever works for you if you are the owner of the database but best practice is to split the database when it is being used by a non-developer.

But the OPs question is about single user as are my posts. Your comment is relevant to multiuser and or distributed.
Telling newbies to split when there is no valid reason is not good imho.

 

[Lateral]

Thanks guys

What about my question:

"Also, when your run a Backup via the Manage option when in Design mode on a split database, it looks like it only backups up the Front end???"

Regards
Greg

 

[Pat Hartman]

That is correct. You need to back up the BE separately.