Update sql table from another sql table

Question

Update sql table from another sql table

mbarr350

SSC Enthusiast

Points: 198
More actions
December 31, 2003 at 6:42 pm

#177389

I am trying to create an Access ADP for our production group. They use the same info that is in our sql tables from Great Plains Acctg. (eg job no;job name contract amt; cust info, billing etc.)
I can create views/queries from the Great Plains table that can be used in combo boxes in my Access project. But I need to have tables with this info so that I can establish relationships.(so I can have a Form with a subform)
What is the best way to copy the data from my
Acctg Sql table into my Prod. Sql table?
Making sure that I don't have duplicates and always have the most current data in my Prod table?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

Will a view not work?

If not, then a trigger might be the best way to ensure things are up to date. Set up one for inserts, separate fro updtes, third for deletes.

cwedgwood50 SSCommitted Points: 1751 More actions · Answer 2

Hi,

Subforms are set at the form level! In your subform control click properties and set the master and child link properties. This will cause your subform to appear filtered as if an inner join had been performed. You do not need to do this explicitly in SQL, if you are using the sub-form control. For more complex actions views can be created and the result used for the most part as if it were a table to display data, but this does not provide an updatable recordset to you unless you set the unique table property.

ADP files do not work with a local copy of the data! If you point your ADP file at your database your users will have access to live data that will never be out of date. If this is not what you wish then you will need to look at setting up another database on your sql server to mirror your live one, and point the application at that. In that case you will need to keep the copy updated. You could do that with triggers on all your tables in your live database as Steve suggested. This is the best way to get an upto date copy of the data.

Depending on how uptodate you need the data in your copy to be and the number of transactions that are occuring you may want to have some sort of batch update that occurs at off-peak hours. Like a scheduled DTS job.

justevolved SSC Enthusiast Points: 188 More actions · Answer 3

I guess you can use linked table features provided in access database. this would ensure that you have the data from the sql server table all the time.

You have to dig for tons of dirt to get an ounce of Gold