If splitting a database isn't possible? (And some other basic questions).

[BadSikander]

Hello.

I have spent the last couple of hours looking through the forums but have managed to get myself really confused. Lots of questions, sorry - if anyone can point me in the right direction with one or two of them, I would be hugely grateful!

I have a database in a shared folder on the network at work. It is due to start being used by users other than myself in future weeks, and I see here that it's sensible to split things into a FE and a BE.

1. My initial problem is that I have a nagging memory of being told that we're not supposed to save anything on the individual computers hard drives. Can a split leave both the BE and the various copies of the FE on the shared drive, or does this negate the reasons for splitting in the first place?

2. Additionally, with little space left on the shared drive itself, it's possible we'll not have the room to put 10+ copies of the FE on it anyway. If I just split the database into the BE and ONE copy of the FE which everyone accesses, will the BE at least be made more secure by the split, even if the FE is still vulnerable, and performance isn't improved?

3. If no sort of split is possible, are there any alternatives to splitting?

Lastly, some questions for if we ARE able to split and put the FE on each computer:

(I would be keeping the BE and the "master" version of the FE on the shared drive. All tables in the BE and queries, reports and forms for the "user interface" in the FE. Hopefully this is roughly what I'm supposed to do).

4. With a split database, what happens with compacting? Presumably the BE of the database can be compacted as normal, but what happens with all the versions of the FE? Does each user have to be responsible for compacting them individually? Does an FE even need compacting if the design is unchanged?

5. Does splitting affect what happens if two people either open or amend a record at the same time, or is that still just governed by the record locks setting in Tools (which would need to be set before splitting presumably)?

6. If the design of the FE changes (but none of the underlying tables, queries etc. are touched), do I just give each user a copy of the new version, or does something more complicated have to happen (ie. does the whole splitting process have to take place again)? What about if new tables are added to the BE, or if existing tables / queries are amended? Can I just issue amended versions of the FE that interacts with the new tables as needed?

7. I have drop down lists and combo boxs in the forms in the FE that use tables to populate them. Will it cause problems that these tables are in the BE - such as impractical amounts of time before combo boxs show their options? (The table has to be in the BE (I think?) because the user updates the content of these tables / combo boxs through one of the other forms in the FE)

Many, many, many thanks if anyone can help me out with any of these.

 

[Bee]

1. My initial problem is that I have a nagging memory of being told that we're not supposed to save anything on the individual computers hard drives. Can a split leave both the BE and the various copies of the FE on the shared drive, or does this negate the reasons for splitting in the first place?

2. Additionally, with little space left on the shared drive itself, it's possible we'll not have the room to put 10+ copies of the FE on it anyway. If I just split the database into the BE and ONE copy of the FE which everyone accesses, will the BE at least be made more secure by the split, even if the FE is still vulnerable, and performance isn't improved?
4. With a split database, what happens with compacting? Presumably the BE of the database can be compacted as normal, but what happens with all the versions of the FE? Does each user have to be responsible for compacting them individually? Does an FE even need compacting if the design is unchanged?

Answering you first question: I think as long as each FE is named differently and each user accesses only their own FE, it may be fine. Problems may occur if two users try to run the same FE at the same time.

Second question: You are not making the BE secure by just splitting your DB. You will need to prevent access to it and there a lot of methods to do that (if the other users's knowledge about DBs and computers in general is not very strong, you can just hide the BE. They may not even think about it).

Fourth question: You only need to compact the BE. Only compact front FE once if you change anything. Choose a time when DB is not being used e.g end of day. Then, when compacting the BE, make sure you make 2 copies, leave one of them as a back up and put the other on your desktop temporarily, compact it and then put it back into the shared drive. If you make a mess, you will have the back up.

Hope this helps,
B

 

[statsman]

The concept of having a FE and BE is:
The actual tables make up the BE. These can be shared by everyone.
The FE is everything else (Forms, Queries, Macros etc.) There should only be one version of the FE and thats whats on the individual computers.
You can upgrade the FE at a later date. Once you have it the way you want it you up-grade the FE on ALL the individual computers.
If security is a problem thats an entirely different area of discussion.

 

[pbaldy]

I agree with Bee that having users open separate copies of the FE on the network will solve the corruption issues that occur if users share a copy. However, you'd still have performance issues since all database objects (forms, reports ...) have to travel over the network, rather than just data. The ideal is for the users to have their own copy on their PC.

I disagree with not having to compact the front end however. The answer to this depends on the db. Frequent querying and such will cause a front end to bloat. I have a reservation/dispatch application that can grow from its "base" size of about 7mb to over 100mb in a matter of a month, due to the extensive querying it does to look up reservations. It begins to experience performance problems if not compacted. I have it set to compact on close, which relieves the users of having to think about it.

 

[BadSikander]

Thanks for the help everyone.

Re: compacting the FE. I know I could turn Compact On Close on so the user doesn't have to manually compact the DB themselves. However, when the database has been compacted in the past, as well as being quite slow (which I suppose is unavoidable), the compacting process has occasionally created a duplicate of the database (called "DB1", the default database name) saved in the same folder as the original, which seems to be a copy of the (uncompacted) database.

I assume it's automatically creating a backup although it doesn't do it everytime so I'm confused.

Is there a way to turn this feature off? I'm worried that after about a week everyone will have about 10 copies of the FE on their PC.

 

[RuralGuy]

It only leaves a copy of the unrepaired mdb when an error was encountered.

 

[Dennisk]

Your database will run fine with one FE on the network. If there are more than say 10 users then it is usual to install the FE on each HD. If the FE is secured then that will prevent unorthorised people from accessing your tables.

 

[pbaldy]

As RG said, it only leaves that copy when it had a problem. I see it when the user is impatient and shuts down or logs off before the compact is done.

I disagree with Dennisk however. The way I learned that the FE should be on the client PC was when I had one that kept corrupting, and it only had 3 concurrent users. As soon as I gave them their own copies, the corruption stopped. It's more about what the users are doing than how many there are, and the safest bet is to give each user their own copy.

 

[boblarson]

I agree with Paul on this too. I had an app that was constantly having issues with the front end being corrupted or not even available since one user's settings had them opening it in Exclusive mode and then others couldn't even get in. As soon as we got them all their own front end, the problems stopped and we haven't had a problem (6 months later since we did it).

 

[BadSikander]

Thanks again for all the help.

I think I'll have a go at putting the FE on each computer as it seems safest - maybe the IT Dept won't notice I've done it!

Good to know there's the option to use the Shared folder as a last resort if they order me to remove it though.

Many thanks.

 

[The_Doc_Man]

I have a nagging memory of being told that we're not supposed to save anything on the individual computers hard drives

Verify this with your management.

However, there is a valid argument that allowing users to copy the "official" FE to their machines is not saving anything of value on the individual computers. The BE is where your critical data resides. Nothing critical should ever go into a FE file. Anything that is truly business-related goes into the BE and stays there.

Now, you can have a case where the FE can be used for temporary files, but with proper design, nothing PERMANENT AND VALUABLE would be in the FE file. The FE and temporary tables should be considered as subject to being deleted and replaced at any time, used only for CONVENIENCE and EFFICIENCY, not for any other purposes. If your design implements the FE/BE split correctly, this is exactly what will happen.