Tables in the front end (1 Viewer)

[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.

 

[Pat Hartman]

I posted a sample recently that shows how to use a template database for your "temp" tables. Based on your description, I'm pretty sure you are probably making temp tables when you should be using queries but that's neither here nor there. If you ever have a need for temp tables, use a template database. I copy the template to the local drive so that each user has his own copy of the temp data. Otherwise, you need to be much more careful about what you actually store or the users will clobber each other's data.

Another thing you might want to do is to create a batch file that your shortcut runs. The batchfile deletes the local FE, copies a new version from the master on the server and then opens the app. This means that the user NEVER needs to compact the FE and so you would remove the compact on close option. Each time he opens the app, he gets a fresh copy and each time he runs the process that needs the "temp" tables, your code copies an empty template into which you append data using append queries rather than make table queries. The data stays in the temp database until the next time you run the process that copies the empty template. This helps with testing since your temporary data doesn't disappear at the end. It is replaced at the beginning.

Post back if you can't find any of my posts about bat files or other posts about version databases. The bat file is the simplest method and it requires NO FE changes. The version methods will require FE changes to implement.

 

[Isaac]

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

 

[Pat Hartman]

Once the bloat issue is gone either by using the template I (and others) suggested or by using the bat file to get a new copy of the FE every time, then there is no need to keep the Compact on Close as I already said.

 

[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)

 

[Pat Hartman]

Creating frequent backups during development is critical. You always need a recovery point if you do something stupid.

Here's a sample that shows a technique of doing this automatically. You'll have to make a modification to make it work for you in your databases. The code backs up regardless of who opened the FE. You probably want it to backup only for YOU since the users don't change the FE. You can hard code the user ID to back up for or you can put it into a table. Since this is a programmer only feature, either works.