Solved

Fix MSAccess Linked Tables without using TableDef?

Posted on 2013-10-24
6
926 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 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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 57
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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 57
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now