Split? Why bother? (A2K 9.0.4402 SR-1)

Topic

New Reply

Several books recommend that databases be split before being distributed.

In a non-network environment, why should i bother, or even care? What problem does splitting avoid? [It seems to me that a split DB runs a little slower]

Reply | Quote

Replies

It means that you can split your data from your ‘front end’. If you want to upgrade the database you by distributing an update, you don’t want the user to loose all their data.

By having the data in a seperate .mdb file you can happily update their front end application database without replacing the data (as long as your data structure remains constant).

Reply | Quote

Good! Thanks Jayden!

Reply | Quote

Two other points that are sometimes missed:

• With your user working in the front-end database they cannot actually delete a table – all they can do is delete the link, which you can easily restore.
• Corruption of databases seems to occur more frequently in databases that have forms, reports and code – if corruption occurs in the front-end you can simply replace it. If it occurs in a single database, you may well loose actual data.[/list]Hope this sheds further light on the subject – the performance hit is generally quite small – if it’s large you should probably be working in SQL Server or some other high performance database.

Reply | Quote

Thanks Wendell.

My perception is that anyone can delete any file at any time, but this perception comes because I have operated that way for mmm… let’s just say… since there was a command line.

Reply | Quote

You are correct regarding the file, i.e. the mdb file, as long as you are running a DOS based file system. If on the other hand you are running NT, then an NTFS file system can give you permissions to modify the file, but not to delete it. However, in any access database if you can modify the database, then you could conceivably delete a table (and the data in it). Security permissions can be set to protect all but admin users and the database owner from doing that, but security can be a hassle too – so one advantage of a split database is to prevent users from actually deleting tables unless they get into the actual mdb file that has the table. Does this make it clearer??

Reply | Quote

Indeed – thanks for your help!

Reply | Quote

Just as a sort of related aside, talking about users deleting tables…etc

It is always good to set the ‘statup’ option to show the database window as FALSE, and most users don’t know to press F11 to bring it up (and usually those users that do have enough sense to not delete stuff).

Reply | Quote

> set the ‘startup’ option to show the database window as FALSE

Thanks!

Reply | Quote

That prompts: how much training to give users?

In the apps that i’m producing now, I’m planning on showing them how to use the Sort & Filter buttons on the toolbar. Other developers recommend compiling the app into an mde and providing the filters and sorts in code. The latter seems somewhat restrictive.

Wondering how the experienced folk do it…

Reply | Quote

I certainly try and make any applications that I write in Access work without the toolbars.

For search and filter options, usually it is only 1 or 2 fields that you ever want to ‘filter’ or rather ‘find’ on. I usually build this into the database via a nice big clear button and have code do what needs to be done in the background.

Admittedly, this creates a little bit of extra work, but I think is a nicer solution in the long run.

What does everyone else think?

Reply | Quote

I generally provide custom toolbars which call the sort and find functionality from simple code routines for that purpose. I never allow my users to see the database window or get into the query grid. Everything gets done from the custom interface because that’s the only way I can maintain the integrity of the application. I learned many years ago that users exist primarily to break your application, so I try to limit their damage as much as I can.

Reply | Quote

We generally follow the same process Charlotte describes, though we do have a couple of applications where the users are reasonably well trained in Access. In those cases we do let them actually create queries and simple reports, so we do expose the toolbars and even the database container window. However all of the company jewels tables are SQL Server based, and they have limited ability in SQL to make changes in table data. I should add that we’ve spent years training these users!

Reply | Quote