cocksy_boy
Registered User.
- Local time
- Yesterday, 20:02
- Joined
- Oct 4, 2012
- Messages
- 11
Hi There,
I’m new to Access databases and am asking for a bit of help; I have a bit of old IT knowledge (which is probably a bit dangerous), but have never worked with Access before and I’m hoping you guys can help out!
A colleague has already set up an Access 2007 DB which I am trying to modify to make more efficient. The main issue is that the database is about 60MB and growing rapidly; this makes it very slow (15 mins or more) to load over a VPN when away from the office – so pretty much completely impracticable. The VPN and IT structure are all in force due to company security and there is no way to change this. The DB is this large because it has a number of attachments (a combination of word, excel, pdf and jpg) for each record in the DB – these are required to be saved for audit purposes and need to be attached to each record for traceability.
The database has been built (I think) on the MS tasks template and has been split into a number of front ends (6 x .accdb files – one for each person accessing the database) and a single back end (with the name _be.accdb). All the database files are saved in a folder on a network that is accessed through a LAN when in the office and can be accessed through a VPN over the internet when out of the office (which is every day for at least one of the team). I have no way to change the LAN or the VPN as this is ‘big company’ policy and well beyond my level of influence!
I think the reason the DB is so slow (impossible) to open is that it is loading up all of the fields from all records when you open the front end – so every single attachment in the DB is being loaded / opened. It has to ‘suck’ all the 60MB over the very slow encrypted (I think) VPN which essentially crashes Access, but works fine when on the LAN.
What I would like to be able to do is separate / split the ‘attachments’ field away from the rest of the fields in the database. The rest of the DB is about 2 dozen text fields, not all of which is used in every record, so isn’t that much data and should load fine over the VPN. I want to do it in such a way so that when the user opens the front end, they have visibility of all the fields (as it is now) and if the user wants to load up the attachments field for each record, it only opens that single record when pulled. This would hopefully mean that it can be accessed over the VPN without any major decrease in speed, and the attachments in each record can be opened and saved as required.
It might be that it’s easier to start again and design it this way from the ground up; I'd rather not if I can help it but will do if required, as long as I can suck the existing data in from the old database; I’ll also need some help and guidance if that is the case though!!
Hopefully this makes sense to someone who knows about relational database design in Access – anything I once knew (which isn’t very much) has been forgotten over the many years since I did any kind of database training – and that wasn’t even with MS Access!! Any help much appreciated - thanks in advance.
I’m new to Access databases and am asking for a bit of help; I have a bit of old IT knowledge (which is probably a bit dangerous), but have never worked with Access before and I’m hoping you guys can help out!
A colleague has already set up an Access 2007 DB which I am trying to modify to make more efficient. The main issue is that the database is about 60MB and growing rapidly; this makes it very slow (15 mins or more) to load over a VPN when away from the office – so pretty much completely impracticable. The VPN and IT structure are all in force due to company security and there is no way to change this. The DB is this large because it has a number of attachments (a combination of word, excel, pdf and jpg) for each record in the DB – these are required to be saved for audit purposes and need to be attached to each record for traceability.
The database has been built (I think) on the MS tasks template and has been split into a number of front ends (6 x .accdb files – one for each person accessing the database) and a single back end (with the name _be.accdb). All the database files are saved in a folder on a network that is accessed through a LAN when in the office and can be accessed through a VPN over the internet when out of the office (which is every day for at least one of the team). I have no way to change the LAN or the VPN as this is ‘big company’ policy and well beyond my level of influence!
I think the reason the DB is so slow (impossible) to open is that it is loading up all of the fields from all records when you open the front end – so every single attachment in the DB is being loaded / opened. It has to ‘suck’ all the 60MB over the very slow encrypted (I think) VPN which essentially crashes Access, but works fine when on the LAN.
What I would like to be able to do is separate / split the ‘attachments’ field away from the rest of the fields in the database. The rest of the DB is about 2 dozen text fields, not all of which is used in every record, so isn’t that much data and should load fine over the VPN. I want to do it in such a way so that when the user opens the front end, they have visibility of all the fields (as it is now) and if the user wants to load up the attachments field for each record, it only opens that single record when pulled. This would hopefully mean that it can be accessed over the VPN without any major decrease in speed, and the attachments in each record can be opened and saved as required.
It might be that it’s easier to start again and design it this way from the ground up; I'd rather not if I can help it but will do if required, as long as I can suck the existing data in from the old database; I’ll also need some help and guidance if that is the case though!!
Hopefully this makes sense to someone who knows about relational database design in Access – anything I once knew (which isn’t very much) has been forgotten over the many years since I did any kind of database training – and that wasn’t even with MS Access!! Any help much appreciated - thanks in advance.
