If you want what I consider the "best" environment is to:
1. Have all backend tables on a dedicated SQL Sever box.
2. Use linked SQL Server tables in my frontend.mde (note that I use a compiled mdb which is an mde file.)
3. Then, it's a give or take on where the front-end resides. I "may" put it on each user's desktop (thus avoiding the network drive completely!) This works great for unstable or slow networks as you have a direct connection between the user's frontend to the backend data. Keep in mind that every connection to a network drive is subject to problems that network may have.
3. BUT, if I think there's going to be a contsant upkeep project, I might put the frontend on the network drive for ease of updating. OR, I put in a "tbLlocalVersionTable" in the frontend which is basically a table with 2 fields, LoginID and VersionDate. I then also have a SQL Server version table which might be called "tblSQLServerVersionTable" which also has a VersionDate field (and is linked into the frontend). Then in my frontend startup code, I compare the 2 dates and if they are different, I prompt the user to "copy the latest version from the network drive" and have code which does this. This way when I update my code (and VersionDate field), this no longer matches the VersionDate field in the user's frontend local mde copy and prompts them to copy the "source" to their desktop (which is only a few lines of vba code.) If I'm only dealing with 1 or 2 desktop frontends, I may not do this.
mde (which I believe are called: ACCDE in 2007) is usually the best route to go for frontends since it doesn't send the user into the code should an error occur. It's a very simple 1 step process to make an ACCDE file out of an ACCDB file (not sure where it is in 2007 but in prior versions it's in Tools->Database Utilities-> Make mde file.)
You again, have to keep in mind that if your backend is on the network drive, you're still bound to problems on that network drive. But you have improved things by having the frontend on the user's desktop. For the frontend, you also have to look at how often you'll be updating it. If often or you have a lot of users, it may pay to simply put the frontend on the network drive and use a script such as here:
DBForums Code Bank
This keeps each user in a "cloned" copy of the source frontend so you never get the "locked by another user" error. Users simply open the vbs script versus the ACCDE or frontend mde file and it'll clone from a "source" mde file. The great benefit of doing this is that you can then make code changes and copy them over the "source" frontend file (on the network drive), without having to run around and have each user close out of the frontend so you can copy new code. I ALWAYS use this script on network front-ends as it really is efficient and prevents a lot of run-around work for me.
Again though, that would be if I'm constantly making updates to the frontend and also have a good stable network drive.
Keep in mind that if your backend tables are in an mdb (or ACCDB) file on the network, you still have that "connection" to the network drive and are bound to it's problems and memory load sharing limitations. Most networks are built pretty stable these days (good network administators) but smaller companies may have a different setup.
The BEST of all BEST worlds would be to have the frontend on each users desktop (possibly using linked tables to the SQL Server), have your forms "UNBOUND" and have functions that access stored procedures directly to SQL Server (and not using linked tables at all) or simply open a recordset on the linked table and write/update/delete the data from the unbound form. This essentially keeps any "connection" to the data source happen only when a user pushes a "Save", "Update", or other events that trigger your code on the form. Thus, users can update the information on the form without the constant connection (on the form) to the data source.
This also works great for users who have dialup connections as they're not constantly bound to the network connection (if you use VPN).
If I know a lot of users have slow connections to the network drive (ie. dialup), I'll consider the recordset size (ie. are we dealing with millions of records), to then determine whether I user UNBOUND forms or not as it's a bit more time-consuming designing unbound forms!!
Usually I try to avoid any "network" connection to the data source should the recordsize exceed 50,000 records or so and the network connection is not very good (again, VPN is very nice).
If data is important and multiple users are updating it, I ALWAYS
put the data on a SQL Server (or MySQL) so it can efficiently do it's thing when it comes to accessing the data (whether using unbound forms or not.) - Again, you're avoiding a connection to a slow or problematic network drive, not to mention the additional benefits such as security, faster update management of the data, etc...etc....