February 28, 2008 at 11:50 am
Something that was simple in DTS has become complex with SSIS. I've been told by someone more knowledgeable than I to use a stored procedure for this instead of a SSIS package.
I need to run an update query. Table1 is in an SQL database and Table2 is in an Access database. The tables can be joined on a single common field. Table2 would be updated with values from Table1.
Is there a way, using a stored procedure, to reference a table in an Access database?
Any ideas are appreciated.
Eric
February 28, 2008 at 12:03 pm
Depending on a lot of factors, but yes - you can do it without SSIS. Whether you SHOULD is an entirely different topic.
Assuming the Access database is in a place it can be set up as a linked server on SQL server, then that would likely be your best bet. Once you do that - use the same rules that apply to referring to data on a remote server.
On a secondary approach - if you don't want to have a linked server in place - then look as using the "more dynamic approach" or rather, more temporary approach, OPENDATASOURCE. Essentially the same concept as a linked server, except that it's temporary and will go away after execution is over.
---------------------------------------
As to the SHOULD question... have you considered hosting the data now in Access in SQL Server instead, and having access use linked tables to "come get the data"? It would solve this issue, and would give you performance options (to make your Access app faster, etc....) you didn't have previously.... It would also make the SP you're talking about here run a LOT faster.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply