Partially Split FE/BE

[The_Doc_Man]

I have a situation that I think will work correctly, but I wonder if anyone else has used this particular solution. In this case, I know what I want to do. My question isn't how to do this. I'm looking for anyone's comparable experience in this situation.

I have a database to be shared by several folks. The parent table has about 500 records, soon to double. Child records number in the tens of thousands spread out among at least six or seven child tables. A couple of the analysis and operational processes involve some temporary tables that are populated and depopulated dynamically as folks import or export data through another application or table. I could write a lot of code to create and delete actual temp tables qualified by the user login names, but another idea hit me instead.

I will place the primary data tables in a Back-End database file. I will leave some temporary tables in the Front-End database. It is of no concern to me if folks in this environment copy the FE to their local PCs. They know I will simply eradicate them from all existence if they muck the DB by mucking any of the code. I regret to say that my reputation is such that they actually believe I would do something drastic like killing their mainframe accounts without warning. (The truth is, I'm a cuddly teddy-bear type - but don't tell my users that...)

What I want to do with this is allow folks who use temp tables to have private copies thereof. This approach allows me to use fixed rather than variable table names per user, so the VBA code gets LOTS easier. Also, if the FE and some of the bigger temp tables are in private copies of the FE local to each user's PC, the network load caused by queries of the temp tables will be drastically reduced.

Other than just making sure that the security is right, does anyone see any problems with this approach? Does anyone who tried this have negative experiences?

 

[RichMorrison]

Doc,

Sounds like things I have done, but I don't know about the "parent/child" aspect of your data.

In some multi-user entry and update apps, I have created work tables on the user C: drive with code that runs in a start up module. A delete query cleans out the content of the work table when the app starts. The users add, change, and delete records in the work table.

When the user exits the application, the work tables are cleaned out again and compacted.

In the FE part of the app, each user is mapped to their own copy of the work table. Each copy has the same table name of course so this makes queries and code simpler.

I do not see any need to put work tables in the FE part of the app.

I have done this for years with no significant problems.

RichM

 

[The_Doc_Man]

I appreciate the insights. The temp tables are needed because of some validation issues. My alternatives included creating an external text file readable with NOTEPAD to show errors discovered during data validation, or to populate some temporary "Analysis Results" tables. Obviously, the latter cannot be fixed-length since there is no way to know how many users will be sharing at the same exact moment or how many errors would be encountered.

Populating either the file or the recordset is about the same level of difficulty since it is only a couple of fields. However, viewing the results from a temp table is easier since you can pre-define queries that do required lookups and even translate error codes for you. The temp tables hold analytical reports of bad commands as well as bad data. They also hold imported data that will be folded into the main tables.

The issue of compacting databases is actually made EASIER if I take the approach that the user NEVER EVER uses the master copy of the FE. They always use a private copy. That way, if they want to compress the FE, they DELETE the old FE copy and make a new copy. The FE, since it would be created with all temp tables erased, would be about as compressed as it gets. Of course, if the users have customized their FE, the non-standard changes get lost. Which, to my mind, isn't really so bad a thing.

 

[RichMorrison]

Doc,

Whatever the use or source of "temp" tables, I still don't see any upside for storing temp tables in the FE (versus storing temp tables in a private MDB)

All the apps I make are network-only. Nothing is permanently stored on the user desktop except private work MDBs.

Of course the best strategy for deployment depends on many factors. I am used to 150+ users on a high speed network.

It costs a little extra to build network-centric apps but I think the payback is worth it; no issues with users screwing up their workstations or their private FEs.

RichM

 

[Keith P]

I use a similar approach but with two BE mdbs and a FE mde. The prime BE is shared across the network and the second (with temp/work tables) on each of the local PCs, compacting on close. It's worked fine for a number years.
Keith