Solved

ms access linked tables change to local tables

Posted on 2012-03-15
5
373 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
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…

734 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