Front End/Back End discussion

[blarson0]

I was wondering if we could have a discussion on the advantages and disadvantages of splitting up a database into a front end/back end format. I currently have a database that only 3 users use to view information about building different parts in our factory. We are hoping to spread this database to 20 or so users in the next month. The database only has 2000 records at the present time, but when we upgrade that could easily go to 20,000 or more. Another problem is that the database has several pictures, this takes the database size from around 3 MB to 24 MB, and viewing the pictures over the network take quite a while for them to come up. We will add many more pictures when we upgrade.

The information in the database will change about every day, but the pictures will always stay the same. I was planning on making a script that will download the new database information from oracle, update the database, and save it to the network each night. When the users log on in the morning make their logon script get the file and replace the old one.

We can only download information from oracle, the oracle database is in the corporate headquarters, and we have no write privileges. I don't think buying SQL server or any other database server is an option.

So, any ideas? Would it be beneficial to split up the database, or should I keep it as one? I would appreciate any input that you have, good or bad.

Thanks!

-Brad

 

[jgoodman00]

I would definitely recommend splitting it. Also, if you adhere to strict naming conventions & storage locations, you can display images as a calculated control (where the image path & name is calculated). This will greatly reduce the database size, although it will not reduce overall network traffic significantly.

Failing this, if you must store something relating to the image, store just the path, then display the image at runtime... James Goodman

http://www.james-goodman.co.uk

 

[Francescina]

Concerning the FE/BE part:
You should ALWAYS split your application because you can not modify the non-table objects lateron without risking to loose ALL data accidentally.
In a multi-user environment you are obligated to install the BE on a server drive and the FE's on each user PC.

Concerning the JPG part of your question you should consider to store the files with a special wizard (see

http://www.unsoftwareag.com/togwa.htm

for details) that allows you to avoid the bloating of your MDB and a much faster display of the graphics.

 

[krsherm]

Split the FE/BE. It's not difficult and it will save you countless headaches, not to mention corruptions.

Don't purchase software to do what Access will already allow you to do. As Mr. Goodmand mentions, your best bet is store a concatenated string that points to the image file (similar to a website model). Why deal with the bloat of large db when you can use logic instead? You don't store Word documents in a database (at least I don't), but you can manipulate Word from Access.

And you are not obligate to install the BE on a server, as Access is not a server database. You are only required to store the BE.mdb on a shared resource (which can be a server or an old Win 95 machine that sits in the corner).

SPLIT SPLIT SPLIT SPLIT SPLIT. And, oh, by the way, split the database.

Rock On!

Kevin

 

[Steve101]

... and split again.

However, regarding the network traffic problem, this in all probability may be largely related to shipping the images over the network. If the pictures are just for display as part of the application (eg. employee photo's, or company logos etc.), then you may need to reduce them in storage size using an appropriate graphics editing tool (eg. Adobe Photoshop, macroMedia's Fireworks, Paintshop Pro, perhaps even Microsoft Paint. The important thing is that you :
(a) save each image in the correct format (usually GIF or JPG) to allow it to be transported around the network in a compressed manner.

(b) In your editor, reduce it so that it physically only the size that it occupies on the screen (eg. 2 by 2 inches say). Sometimes, much larger images (eg. 10 by 8 inches or more) are presented in smaller frames in Access or other products using object properties like Clip, Streth or Zoom) to contain the objects size within the presented object size; beware, underneath the small presented image can lurk a massive physical image, consuming your network and workstation resources.

So, in summary, check the physical size of your images (eg. in Explorer); BPF format images are not compressed, and these should be converted to JPG or GIF.

A picture optimised for screen presentation (say 2 by 3 inches) should typically be only about 30 to 40 Kb. If its many times bigger, then you need to work on the image itself using a graphics editing tool (or at least a copy of it).

If you have easy access to a graphics artist, then they should be able to help you with the image manipulation as required. Also remember, that you should leave the original (high resolution) image intact, and only work on optimising a copy for passing around for viewing over the network (or even over the internet).

I hope that this helps,
Cheers,
Steve