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.
johnmadiganAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Routers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.