Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

ms access linked tables change to local tables

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
johnmadigan
Asked:
johnmadigan
2 Solutions
 
peter57rCommented:
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
 
dqmqCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
dqmqCommented:
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
 
johnmadiganAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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