Hi all,
I would like to hear your thoughts about the impossibility (?) to share an Access database with different entities in our organisation – unknown territory to me.
It is very likely it’s just a no go and/or I can’t provide much more context (which could be time consuming and hard to get)… but let’s see :-).
The process involves 4 users: the coordinator (‘we’) and three experts, each in their own entity in our decentralised organization.
We and one expert work both in the ‘environment’ of a big contractor to which our ICT has been outsourced. Besides, we also have our own ICT department with its own separate environment (let’s say to allow more flexibility and leaner/cheaper solutions…).
The two others experts manage their own, different ICT ‘environments’.
We use a Access database with 4 users, in a yearly recurring process:
– we populate it with data about our clients’ activities and distribute it to 3 ‘experts’;
– the three experts approve these activities (or not), change and add some data, and send it back;
– we solve discrepancies, ask the experts to re-evaluate certain data… to come to a final dataset,… and merge all in the end, for further processing.
The database is quite small (ca 5000+ records/year in the main table)… but has some advanced (?) features like:
– VBA processing of ca 250 formatted excel client reports into the database tables;
– audit trail for specific form fields;
– a report with quite some calculated fields and text, combined text messages and subreport;
– VBA processing of the report into individual pdf files (and planned: mailing this to the clients);
…
Some ‘suboptimal’ data structure choices were made to simplify data handling, coding, etc… but all users are very happy with the result.
New goal: limiting the time we lose because the experts can’t start until (most of the) clients’ data are processed and the database’ll be distributed… and because we can’t review our expert’s additions before they send the DB back (provisional or definite for further processing).
So, I considered migrating to:
– postgres/… backend (in the environment of our own ICT department – no support of SQL server/…)
– ODBC-connected front end, run locally by us and each expert (in their own respective environments).
Problem: our own ICT department says no: the data connection from their environment to the users’ environments (of our big contractor but mainly the two others’), through firewall after firewall… creates such a long path that they foresee too many problems. They did it in a previous project… and eventually managed to get it ‘run’… but not on a solid basis.
=> Any comments or suggestions to that?
Is this an expected/recognizable situation?
Thanks!
Hasse
ps Possible alternatives I see:
– to arrange a personal account for the two ‘other’ experts so they can log into our environment & let them use our Access database ‘directly’ through an online connection using VPN (with a registered laptop of our own). Yearly cost range 500-800€ (personal favourite);
– RAD tool: might not provide the needed interface & automation features;
– Citrix
– starting project at our big contractor: expected to be a no go due to too much overhead;
– keep it the way it works now.
