Tables in the front end

[ontopofmalvern]

Hi
Firstly apolgies for the rambly-ness of this post - I not really after solutions just general pointers and links to materail that could help.

I have (by my standards) a complex database which is split between a FE and BE, it has about 30 users who all have a copy of front end and access the BE on network drive. The FE is becoming a little ill - it is bloating and will no longer compact and repair. I have a long list of thngs I should do to it to make it more comfortable and plan some major surgery. 2 things I would be most grateful for further general pointer on before I go in.

Firstly my front end has a small number of small tables - I think this might count as a cardinal sin (?) - all but one of these tables stores tempoary calculated data used for reports and charts . There is also one very small table which stores some user defaults. What is the general advice on good practice for these tables? The data ones could go in back end, be emptied on close or even dropped on close (then recreated as needed). For the defaults is there a standard way of having user defaults (currently for each user it is just 2 interger fields so it is tiny).

Any pointers on how to deal with FE 'bloat' would also be great - it used to be about 10mb but is now 50mb and rising (I do have older versions to roll back to if necessary but I'd like to fix this version). I read somewhere that these problems can be the result of file coruption and can be solved by starting again and copying everyhting accross (no small task) - does that make sense or is it a fools errand?

Many thanks for any help - love this forum it has saved my life several time before (okay not my life as such...)

Richard

 

[plog]

I suggest multiple front ends--one per user if possible, if not 1 per department or other logical division. Presumably all shared data is in the back end and the tables in the front end won't effect other users. If not, move those tables to the back end. Then just copy and paste the front end, change whatever defaults you need in each version and deploy.

I don't think there's a standard method for user defaults--you could put them in a module, but I generally use a table as well.

 

[theDBguy]

Hi Richard. You could put the user defaults into the BE, just add another field to designate the user. As for temp tables, it's better to leave them in the FE (or better yet, put them in a separate temporary database). As for FE bloating, just throw it away, when it does, and simply replace it with a fresh copy from your master file. Hope that helps...

 

[Isaac]

This response mostly echoes what others have said, give or take.

1) I would put all the tables you have mentioned (except temp tables) in the back end. as dbGuy said you can add a column designating "which user" these items are for, if need be. That takes care of any calculations or report data that might be specific to individual usage, and takes care of any user defaults too. I often have a tblPreferences to allow users to set all kinds of preferences. If you need to constantly refer to these preferences, and want it to be fast, then handle that somewhere near the db startup. Users prefer an initial "wait" with subsequent rapid performance, compared to, less initial "wait" but subsequent slower performance (IMO). Making a decision on that tradeoff is a constant thing in db & interface dev.

2) The only table in the FE, in most of my databases, is a local version table, to see if the user's local FE version is the same as the current FE master dev copy. Occasionally I've had reason for just 1 other table--a table that indicates whether code has already run or not for that user, on that specific machine, to create a DSN to a sql server backend.....And in those cases it was because I wanted to 100% eliminate an Access BE.

3) From reading your post, I'm going to hazard a guess--That you don't have a really automated, high performing "auto versioning system" in place. If you DO, then re-deploying new FE's (as dbGuy suggested), feels like "nothing", and is something you can do very frequently - as frequently as necessary - without hardly blinking an eye. This will make your FE bloat situation seem like almost a complete non-issue. In some of my databases during certain time periods I was deploying a new version almost daily. In others I deploy a new one each time they click the startup shortcut on their desktop. Here is a brief summary of this method--let me know if you need help implementing it.

4) You can also choose to implement .ini / config type files (really just a text file somewhere containing important information - perhaps for startup) to complement/supplement the table driven method. Frankly, if you have a fast network situation, sometimes reading info from a text file is even faster than a Dlookup to a table! I usually have one of these, if only for the need of emergency shut-downs of the database for some emergency back-end design problem/situation.

5) Temp tables. I've rarely found a true need for true temp tables--true meaning they are used once and then deleted--but rather handled this mostly through staging tables...Staging meaning it holds some raw import or it holds data that's in the process of being manipulated through a multi-stage process, which really means it can be permanent. You can either A) use the back end and maintain it regularly (compact), B) have your code create separate databases on the fly for temp tables, then delete that db, or C) use the FE. Once you have a highly automated and seamless auto versioning system in place, option C becomes pretty doable. Option B is good too, I know a lot of people do that. I like C just because it's all there together for examination and testing/dev purposes.

 

[June7]

I handle versioning a little differently. Version table is in backend and has one record with version number. Form in frontend has a label with a version number. When frontend opens, this form opens by default and code compares record value with label caption and if different there is a new version of frontend that must be installed on user computer.

 

[ontopofmalvern]

Many thanks for this weekend reading.

I have scanned all the above and one question immediately came to mind (I'm sure there will be more). Several responses say I should have a seperate front end for each user, can I clarify what that means. Presently after updating the front end I post it onto the server where each user uploads a copy of their own onto their laptops - is that 'front end for each user'? Or are you suggesting everyones copy of front end should be unique rather than a clone? (if unique how so?).

Many thanks everyone for you help, I realy appreciate the time you put into helping the community - I wish I could contribute more rather than just consume.

Richard

 

[theDBguy]

Hi Richard. What you're doing is exactly what we're saying. Each user takes a local copy of the FE and runs it from their own machine.

 

[ontopofmalvern]

Hi Richard. What you're doing is exactly what we're saying. Each user takes a local copy of the FE and runs it from their own machine.

Tah, I thought that's what you meant but better safe than sorry.

 

[Isaac]

Compact on close seems to be nothing but nightmares. I would just remove it 100% of the time me personally

 

[ontopofmalvern]

Once again thank you for all the replies, I am now going through all 300+ objects in the FE and tidying them and generally implementing much of what has been said here (along with much advice from the misnamed '10' commandments thread and much else on the forum). For anyone picking up this thread my big learn here is when you think your project might be wobbling don't put off first aid because it still 'works' it will bite you and the bite will be harder the longer you leave it - I have allowed a few things to fester way too long. My major surgery has started by copying everything into a new clean development copy (which was smother ten I expect using 'import' functions), with near hourly back-up to roll back to (I maybe over doing it now?) and running regualar manual compact and repairs (after making a back-up)