FE/BE, MDE, What should I use?

[kgraue]

I have been searching this forum for a few months now and the information has been really useful. Thank you to everyone who provides input on this forum.

I have searched for FE/BE and MDE threads on here and a few of them have been useful, but they are only useful if you know that you are going to use that particular method (i.e. FE/BE, MDE, etc.) for your database. My question is which one should I use.

The company I work for uses shared drives, but not my particular group. We use an online document sharing client that allows only one user to have write privileges at a time. Anyone can have read privileges but only one person can overwrite the current file at a time. (Example: If person A had the file open first and person B tried to open the file and overwrite the changes, it would say "Person A is currently using this file. You cannot save your changes at this time.") Also, you are able to turn on a feature that lets you version the file. So anytime a user changes something in the database it would create a new version of the same thing.

Basically, given the above information, my question is which method of deploying my database is best suited for me? Right now I have everything (tables, queries, forms, macros, modules) in one MDB file.

If anyone could point me in the right direction I would appreciate it. I just don't know the pros and cons to FE/BE, MDE, etc. Maybe it would work better if I just kept everything in the one MDB file? Thanks in advance.

Kevin

 

[boblarson]

Okay here goes:

1. A FE/BE split database is one where the front end has the forms, reports, queries, code, etc. and the BE has the data. If you go to a FE/BE situation, you should really consider giving each user a copy of the front end for their local PC.

2. You can use an MDE file to lock down design changes, as the front end, but you will need to set the backend location for the MDB file it is based upon before making the MDE (and it has to be able to compile so you should compile the MDB manually by going into the code window and going to Debug > Compile to see if any errors exist and then fix them before trying to create the MDE file).

3. If you have users who use the full version of Access they can get in and modify tables and queries directly with an MDE file. But, if you use the Access Runtime, then they can't.

4. If you have multiple users needing access to the database at the same time, I would highly suggest a FE/BE split for many reasons, including performance. Whether it is an MDB or MDE, is up to you.

I hope that helps you.

 

[kgraue]

Thank you for the reply boblarson.

I think based on what you said it would make sense to use a FE/BE database. I think that there is going to be around 10 people using this database. That means I would have 10 FE databases (one for each user on their computers) and 1 BE database on our file document sharing client, correct?

Assuming the above is correct, I have a question about the data going from the FE databases to the BE database. This database is a scheduling tool that allocates resources (people) for specific date ranges. For example, lets say we have 10 users. If user 1 enters data into his/her FE database it needs to be reflected on the other 9 FE databases. Meaning, if user 1 says that Resource John Smith is busy from 3/29-3/30 then the other 9 users must know about it. If they try to schedule John Smith on 3/29-3/30 then it shouldn't let them. This functionality is already working in my database but that is assuming that there is only user at a time. Simply, the data needs to be reflected everywhere after a change is made by any user. Is this possible with a setup of 10 FE databases on each computer and 1 BE database on our document sharing client?

Thanks in advance.

 

[pbaldy]

In case Bob is offline, yes, you'd have 10 FE and 1 BE. Your stated scenario should not be a problem. Because you only have 1 BE with the data, as soon as one user saves the record with those dates, any other user trying to save a record should see it (depending on your validation of course).

 

[boblarson]

Thanks Paul, yes I was forced off line as we lost our Internet connection at work temporarily.

Also, if you don't want to try to get users to copy a new front end every time you make changes, but you want them to always have the latest, check out my tool for enabling auto-updating of front-ends here:
http://www.access-programmers.co.uk/forums/showthread.php?p=552006#post552006
There's also documentation in the third post from the top.

 

[kgraue]

Thanks guys for replying. I think I just need to try it out with our document sharing client and see what happens.

My only fear is the BE database. Since that is going to be the file that in on the document sharing client we use I'm worried that the data from the FE databases won't be sent the BE because the document sharing client only allows for 1 user to have write privileges at a time. So in theory, since all 10 users have a FE database linked the BE database that "isn't there" (i.e. not on their computer with the FE) how would the data get updated?

Forgive me if I sound repetitive. I'm just trying to figure out how this will work, and I don't know if you guys are familiar with a type of document sharing client that we use.

Thanks in advance.

Kevin

 

[boblarson]

What document sharing client are you using, what documents are you sharing, where are they stored, what relation does this have to your database?

 

[kgraue]

What document sharing client are you using

It is called eRoom.

what documents are you sharing

The document I will be sharing is the BE database with all of the data in it.

where are they stored

I'm not quite sure how eRoom stores the data. I will have to look into that.

what relation does this have to your database?

This is where the BE database is stored so the ability to transfer data from any FE database to the BE database (on eRoom) is esential.

Thanks.

Kevin

 

[boblarson]

Since I've not seen something like that implemented before, I can't give any real good suggestions. I think you may be in some fairly uncharted territory and hopefully you'll be able to post back with your successes (or things to avoid) as you work through this. Sorry I can't be much more help.