?
Solved

Fix MSAccess Linked Tables without using TableDef?

Posted on 2013-10-24
6
Medium Priority
?
993 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month10 days, 6 hours left to enroll

762 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