Unanswered: Safely and Routinely Insert Data in to SQL from Access
Here's my scenario. I have a large SQL database that is at the core of our business. Until recently a rogue employee was linking tables in his Access DB to this SQL DB, running queries against the linked tables (frequently locking up the SQL tables) to extract records, and then running INSERT queries in Access that wrote the data back to the SQL DB. Now that he is gone I've created a DTS package that copies certain tables over to a separate SQL DB and created an Access DB that links to the new SQL DB so we can query the data without affecting the mission critical SQL DB. However, I still need to update the mission critical DB with the results of the queries in the Access DB. I would prefer these updates to occur in the off hours of the night instead of during the day. What would be a safe way to do this? If I can't do this quickly I will have to link tables in the Access DB to the mission critical DB to insert the data but this scares me since Access likes to lock up the SQL tables.
Backup/restore? Since you want it to occur off-hours, this'll get all your data over at the time you designate - whenever that may be. Or if it's only "some" of the tables that are needed, what about DTS/SSIS or even BCP placed on the scheduler to get the data?