work database - possibly needs changing from attachment to hyperlink (1 Viewer)

I'm hoping someone can offer a bit of advice here. I admit I know nothing about coding but for some reason my boss decided 4 years ago that he wanted me to create an access database using records to keep all our client and worker records on, so that we could easily track who was available, what trades they covered and which clients various workers had been placed with in the past and present. Up to now, what I cobbled together mainly using the wizards and reading up about relational tables has worked. All our workers' CVs have been attached to their records. However, when I had a few spare moments the other day, I noticed that the back end is now approaching 1.5GB. There are only a handful of people using it and all of them are in the same office, so it has been set up as a homegroup with the back end residing on an used pc. I am sure that the size of the database is down to all the attachments and have noticed that some staff have attached jpgs to it as well.

My quandry now is, will going through each individual worker record (now just over 9000 of them) and removing any jpgs reduce the overall size dramatically ,or should I be thinking in terms of adding an extra column to the table and forms etc to have the CVS as hyperlinks? If I go down the hyperlinking route, will that save me much space and would whoever had pulled up the record be able to see the CV etc if they click on the hyperlink? If I do change to hyperlinks, is it easy to do or would it involve coding (which I know nothing about)? I realise that I can't just delete the attachments field until such time as all of the CVs have been hyperlinked if that is the best route to use..

Make a backup copy of your backend data file, and make another copy available off line. Delete the attachment field from your offline copy. Do a compact and repair. Now check the file size.

If as I suspect it shrinks dramatically then you have your size answer, as to the best route to handle it moving forwards, I would probably use a hyperlinked style location.
Store the documents in a fixed style of location on your network.

\\YourNetworkShare\Documents\CVs

Then in code, provided you know the file name something like this (Air Code - untested) on double clicking the file name in a form -

Thanks for your reply Minty, I am on my own in the office tomorrow so will try the back up as soon as I get in.

At the moment the CVs are kept on the machine of the person who has registered or edited the individual workers details in a structure which goes staff member name/supporting docs for workers/county name/trade name/ worker name. We have been told to store our work like this in case the broadband goes down so that we can still fish out suitable workers for our clients in an emergency. I guess that would be too much for the hyperlinks to cope with? If so, I will have to get round the boss to let me create another folder in a shared location which would purely be used to keep CVs in. Another problem I have is that we have some workers with the same name but are different people in different areas of the country who do different trades, so if they are all stored in the same folder, how would people know which CV was the correct one to remove/connect to?

There is no way you can link the files to any number of machines dotted all over the place. The best approach would be that the people who need them have them and to automatically back them up to a central place.

There is no reason you cannot use sub-directories in the central place to differentiate offices but even the different offices may have two people with the same name so I would have thought a naming convention would be required in the first place such as John Smith1 and John Smith2.

It is surprising what a database can do for physical organisation as in reality a company should have all it's CVs in one place and be able to differentiate between members of staff.

Before we wrote a database we had files scattered all over the place and no file naming conventions, now everything is neat and tidy.

I would organise the people / clients into a database table with a unique ID. Then store the documents against the ID.
This would allow you store John Smith 1 2 and 3 in the same table, but you could look up their postcode or town to know which one was which.

Minty said:

I would organise the people / clients into a database table with a unique ID. Then store the documents against the ID.
This would allow you store John Smith 1 2 and 3 in the same table, but you could look up their postcode or town to know which one was which.

Click to expand...

Minty is quite right, the database can deal with the different people easily but your document system cannot. If you are going to centralise the documents you will need naming conventions for them as well, databases can sort people easily but the selected people cannot all have a document called JohnSmithCV.pdf in the same directory.

Thanks for your replies, I have already got auto numbering set on the tables which contain the worker and client details. It's only the form which inputs or edits data to the worker table which would have anything linked to it. I hadn't thought of using the ID numbers to give the CVs a unique name for each worker, so thanks for mentioning that Minty , I take it I would then be able to use the path you suggest to link them and that the code you mentioned would be written into the properties for that field on the form? At the moment the backend is in a shared folder on the spare pc so the front end is linked to it using the ribbon in access itself. I have never tried using a UTC as when I was originally told to make one up, I was the sole user and it sat on the pc I was using. When we took on more staff I just split the database and put it in a shared folder on the pc which was still in use at the time so just browsed on everyone's machine through the ribbon to find the back end to link it.

With the amount of data and records you are dealing with I would strongly suggest getting a small dedicated network fileserver and a backup method. Your data is critical to your business and the loss of it or even loss of access to it temporarily, would have a serious impact commercially.

Once you have that in place your database BE can be stored there and automatically backed up, and everyone would be pointing to a single path.

Thanks Minty, as soon as the boss gets back from leave next week I will mention it to him again. I did mention in an email to him before he went on leave that maybe we should be thinking in terms of investing in one. I forgot to mention that since I first split the database I have moved to a different pc so the one the back end is currently sitting on is a spare one so that the three pcs in use are using the same path to access it.