Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


DTS Access -> SQL 7

Posted on 1999-07-01
Medium Priority
Last Modified: 2013-11-30
I need to bulk copy several access tables into SQL 7, completing the tasks in the following order :
Drop SQL Table
Re-create SQL Table
Set some Permissions
Move the data from Access into SQL table
Report Success

BUT, if any task fails I need the table to still exist in the state it was before starting the tasks.  I've got a DTS package that looks like it should work, but if a task fails it refuses to call my SQL command, rollback.   HELP!

In Advance - Thanks.
Question by:Dazza051197
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Expert Comment

ID: 1096054
Instead of droping the table, rename it and drop only if import is done without error.

Accepted Solution

tchalkov earned 600 total points
ID: 1096055
Instead of using DTS Package write your own script which is doing the work. First register the access database as linked server - for example accessdata.
then call BEGIN TRAN
and do all the thing you want to do
and at the end call COMMIT TRAN
you can reference tables in access database by using accessdata...tablename
i.e. select * from accessdata...table1


Author Comment

ID: 1096056
how do I register my access database as a linked server ?

Expert Comment

ID: 1096057
the easyiest way to do this is by using Enterprise manager - go to your server then choose security, then right click on "linked servers" and choose "New Linked Server" and then follow the instructions of the wizard. If however you want to do this in a script then you can use sp_addlinkedserver and then sp_addlinkedsrvlogin - you can see books online for help about these functions. There is also an example on how to add a linked access database

Author Comment

ID: 1096058
Took me a while to sort out the parameters, and then how to select the records (I didn't see the 3 dots you put, duh!). Anyway all sorted now, and I thank you very much for you help.  Hope the extra 50 points come in handy.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question