?
Solved

Fix MSAccess Linked Tables without using TableDef?

Posted on 2013-10-24
6
Medium Priority
?
1,004 Views
Last Modified: 2013-10-25
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)
or
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!
0
Comment
Question by:Javin007
  • 4
  • 2
6 Comments
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39598303
<<1.) Is there any way to make the MSysObjects table updatable?  
or barring that:>>

No.

<<2.) Is there any way to execute VBA within a JET 4.0 database externally, from Java, without having to have MSAccess installed?>>

No.

<<looking forward to any creative solutions you guys may have! >>

 Not a Java programmer, but if you can either:

1. Execute SQL statements (CREATE TABLE)

2. Reference the ADOX library

You will be able to create tables in a JET DB.

Jim.
0
 
LVL 58
ID: 39598321
Take back the SQL part.  I forgot that DDL statements only work on local tables.

DAO or ADOX should be workable though.

Jim.
0
 
LVL 4

Author Comment

by:Javin007
ID: 39598340
Creating and modifying the tables isn't the issue here.  The problem is the linked tables.  The linked tables store a fully qualified path (ie: C:\Path\ApplicationFolder\Database.mdb).

Thus, when the Database.mdb gets moved to a new folder, any other applications previously "linked" to it "break."

Since you can't "merge" to database connections (ie: Open one database with "UserName" information, and a second database on a separate connection with "Address" information, then do a join between the two) you would have to use linked tables in order to do any sort of JOIN with your SQL.

So I have to have linked tables... But I also have to be able to modify those links.  

The only methods I've found to create/destroy/modify linked tables require actually being in the MSAccess file itself and using the TableDef object.  This is not an option.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 4

Author Comment

by:Javin007
ID: 39598358
Ah, you got me thinking about an option I hadn't considered.  I didn't realize that one could access external .DLL's from Java.  Perhaps creating an access to the DAO DLL would permit the use of the TableDef object.  

Will attempt this.  If it works, you win the stuffed bunny.
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39598369
I probably should have been clearer in my comment.  As long as you can reference DAO or ADOX as an object library, then you can create/refresh/delete linked tables in a JET based DB.

Take a look at the following:

http://msdn.microsoft.com/en-us/library/office/aa164914(v=office.10).aspx

The code examples are VBA,  but as long as you can work with ADOX in Java, then you can do the same thing.

Jim.
0
 
LVL 58
ID: 39598379
<<Perhaps creating an access to the DAO DLL would permit the use of the TableDef object.  >>

 That is correct and what I was pointing out.  With DAO or ADOX, your working with an object lib, which has functions to manipulate JET based DBs.   You don't need Access itself.

 As I said, I'm not an Java programmer, so I can't give you the exact steps, but for sure you can wotk with JET based objects without Access in the picture.

Jim.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question