Solved

ms access linked tables change to local tables

Posted on 2012-03-15
5
375 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
[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
5 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 250 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 250 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

Technology Partners: 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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
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…

626 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