Select from MS SQL insert into MS ACCESS

faron
faron used Ask the Experts™
on
I need to perform a stored procedure that would select records from a MS SQL table and insert them into a MS ACCESS database / table. If this needs to be done via ODBC, that is fine as well.

Can someone provide me with some MS SQL syntax example to do this?


Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Couple options here, but all need an ODBC link setup:

1) Link the SQL table in ACCESS.

-----------------------------------------------------

2) Use a linked server (Link Access to SQL Server) *I dont like linked servers but its still an option.

-----------------------------------------------------

3) Create a Stored Proc with just a select statement and use it has the source in an access insert query.

-----------------------------------------------------

4) Use DTS. *This is probably the best bet considering you can call the DTS package from either the command line of from with in a stored proc.

Commented:
Oh sorry option 4 does not require the ODBC setup.

Commented:
If you need to know more on the DTS and calling it from a command line or stored proc then please let me know?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
If you need to know more on the DTS and calling it from a command line or stored proc then please let me know?

Author

Commented:
Thanks if you would let me knwo how to setup DTS for this as I have used DTS in the past to move database between MS SQL databases but never MS Access.

Commented:
1) add SQL SERVER Icon
2) add ACCESS Icon
3) Highlight the SQL Icon
4) Highlight the Access Icon along with the SQL Icon
5) Click the transform data task on the toolbar
6) Setup the tables and transformations you need
7) save the package.





If you need to run the package from a stored proc you can generate a script via DTSRUNUI. Like this:

1)From Windows
go to START-
Click RUN
Type DTSRUNUI
2)Select your server and choose the package name then click advanced
3)At the bottom click the generate button and copy the script into your stored proc.
4) modify the copied script to run from xp_cmdshell. The end result should look something like this:

exec master..xp_cmdshell 'DTSRun /S "SQLSVRNameHere" /N "UploadFile" /V "{A526A949-AF07-11D6-B2E2-00500408ADD3}" /W "0" /E'

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial