Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ms access linked tables change to local tables

Posted on 2012-03-15
5
Medium Priority
?
378 Views
Last Modified: 2012-06-21
I have an access db that has a few linked tables to a db2database.  I made a local copy one of the linked tables.  

Is there a way to have the local copy update with the db2 linked table is changed?

This is a work around for a problem we are having trying to connect MAS 90 to and ERP system using db2.  Mas can not see the linked tables in access but can see the local tables.

Trying to see if there is any trigger that will update the local table.
0
Comment
Question by:johnmadigan
5 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 750 total points
ID: 37725672
In versions of ACcess before A2010 it is not possible.   There is nothing that will detect a change to a table.  You can do this easily in a form.

Access 2010 introduced the equivalent of triggers and so it might be possible to initiate another procedure (i can't say for sure )  , but if it were possible then it would mean that your local table definition would have to be maintained locally - you could not re-create it using a make-table query or table copy or import or any other way.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37725777
I have an idea--don't know if it will work.  But before going there I must question why your MAS system cannot connect to db2 directly, rather than using Access as a go-between.  

Anyway, you may be able to use triggers on the DB2 table to apply the correspondng changes to the Access table.  But, like I implied before, that's a lot of chewing gum and bailing wire when you should be able to read the DB2 file directly.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 750 total points
ID: 37725819
<Is there a way to have the local copy update with the db2 linked table is changed?>
Create a form with the linked table as its source.
Then on the "After Update" event of the *Form* (Or Before Update...?) you can do an insert Query into the static table

CurrentDB.Execute "INSERT INTO tblCustomersStatic (CustName, CustAge) VALUES (" & "'" & me.txtCustName & "'" & ", " & me.txtCustAge & ");", dbFailOnError

I may not have the SQL just right here for text and numeric values, but I am sure you get the idea.

I have no issue with another expert going further...
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37726826
But...there are so many ways the DB2 table could potentially be changed, i.e., to circumvent the Access form.  On the flip side, a db trigger captures any change, regardless of how it occurs.  If the goal is quasi-replication of a table in real time, then I think you need to drive the process from the source database:  a trigger, log shipping, or something of that nature. my 2 cents.
0
 

Author Closing Comment

by:johnmadigan
ID: 37733499
I looked at 2010 and the trigger thay have would not work for my application - the folks at Sage could not get mas90 to see the db2 tables.<br /><br />I made linked the db2 tables in an access db and made local copies of the tables.  Mas can see the local tables but not the linked.<br /><br />I made a form with a few buttons to run some queries to move the data back an forth.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

971 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