• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1015
  • Last Modified:

Fix MSAccess Linked Tables without using TableDef?

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
Javin007
Asked:
Javin007
  • 4
  • 2
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Take back the SQL part.  I forgot that DDL statements only work on local tables.

DAO or ADOX should be workable though.

Jim.
0
 
Javin007Author Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Javin007Author Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now