Fix MSAccess Linked Tables without using TableDef?
Posted on 2013-10-24
So here's a fun one...
I have a client with the following problem:
1.) They have a stand-alone network that takes an act of GOD to get anything new installed on it.
2.) The most recent version of MSAccess they have installed on this network is Access 2000.
3.) They have a few legacy "applications" created in Access 2000.
4.) They have hit the 2 gigabyte upper limit on some of these "applications".
5.) This required them to split the tables into multiple databases, and use linked tables.
6.) There are HUNDREDS of these links now.
7.) Due to the slow-downs caused by MSAccess linked tables (from within MSAccess), as well as the fact that not all users have Access installed, they decided to rewrite the tools in Java but still using JET 4.0 as the back-end database. (I've tried getting them to switch to MySQL, SQLite, SQLServer, etc. to no avail.)
7.) These "tools" may be moved from one folder location to another.
So yeah. It's a convoluted mess, but unfortunately, I don't have the authority to fix any of the above. What I have to do is find a method of updating these links in the databases so that they either:
a.) Have relative paths instead of qualified paths (not possible far as I know)
b.) Check the paths each time the tool is run, and update them appropriately.
I know that the "right" way to change the paths for linked tables is by using the built in Link Updater, or the VBA option is to step through the TableDef collection and change them there. Neither option is viable, though:
Both would require access to the MSAccess database itself, which is not going to be an option for many users in the future, and creating a Standard Operating Procedure that states "If the tool stops working, find someone who has Microsoft Access installed, and have them open each of the following .MDB files then close them" is also not an option.
This leaves me trying to find a way, from within Java itself, to modify the links. I've heard many people state that updating the MSysObjects table directly would "break" things, but I can't find any actual documentation from Microsoft to support that statement. Worse still, I can't find any method of updating this table myself to find out if this is accurate.
So this is my question:
1.) Is there any way to make the MSysObjects table updatable?
or barring that:
2.) Is there any way to execute VBA within a JET 4.0 database externally, from Java, without having to have MSAccess installed?
Looking forward to any creative solutions you guys may have!