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


DTS: SQL to Access.mdb, INSERT INTO all_tables_destination SELECT all_tables_source WHERE...

Posted on 2007-11-14
Medium Priority
Last Modified: 2013-11-30
Hi All

I have a SQL2K db and an A2K.mdb, both have the same schema.  
(The mdb tables were the 'source' of creating the SQL tables.)

I need to do an INSERT of all records in all tables of the SQL2K source, INTO all tables in the A2K.mdb, WHERE a column = a passed value.

For the moment, I would prefer to avoid using Access VBA code and linked tables to do this, unless there are no other practical options.

My knowledge of DTS is limited to simple data transfer tasks from one source to another.

Question by:Jim Horn
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
LVL 31

Expert Comment

by:James Murrell
ID: 20283665
LVL 66

Author Comment

by:Jim Horn
ID: 20283795
The link you posted is to a question I had previously asked, going from Access to SQL.  This question is SQL to Access.
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 20283905
>WHERE a column = a passed value.
the "key question" you have to "answer" here is: how does the user pass that value?
now, I don't see why VBA is "not an option" ? can you explain a bit what "restrictions"/"thoughts" you have about this?
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


Accepted Solution

mshaebanyan earned 400 total points
ID: 20284141
You can do this in DTS designer without any code -- just create a new DTC package in SQL Server Enterprise Manger, drag a SQL Connection and an Access connection from the toolbar on the left-hand side, set the properties of the objects to match your data sources (you can right click on the icon and go to properties in the GUI designer) and then create a simple data-pump task with one as the source and one as the destination. The properties of the data pump will let you specify mappings of rows from source to destination and data type conversion.  Even with limited experience it should be simple to create a reusable package to accomplish this in a few minutes (and might be a good introduction to DTS, which IMHO is immensely useful).

Meredith Shaebanyan
LVL 66

Author Comment

by:Jim Horn
ID: 20285021
>and then create a simple data-pump task with one as the source and one as the destination.
Data-pump task is not one of the toolbar icons.  Would it have a different name?

If this is 'Transform Data Task', I was able to get it to work except for in the Source tab being able to specify a parameter.

>how does the user pass that value?
I was hoping that it could be passed as a parameter using Access VBA when I call the dts package.

>I don't see why VBA is "not an option" ?
The app that I would like to have kick off this process is currently in Access '97.
Destination .mdb (created by third party) is Access 2000.  So currently users would not be able to open this.
LVL 30

Assisted Solution

nmcdermaid earned 1400 total points
ID: 20287672
From VBA, you can use DTSRUN.EXE to:

-Start the DTS package
-Pass in parameter(s)

Then you can use these parameters inside the DTS package.

Alternatively you can use the DTS object model from VBA.

However, you need to have the DTS runtime installed on this particular computer for both these options... which may be a pain.

If you don't want to install DTS, you could concievable run a 'pass through' query in the server which sucks the data directly out of your access database... with parameters of course.

You set up a 'linked server' to your access database, and use it just like a normal table.
LVL 66

Author Comment

by:Jim Horn
ID: 20358738
I ended up creating the below SP (a few things are mocked up) that passes the value into a single record in a table (I wasn't able to get any other method to work), then runs dtsrun.exe

When run in QA, it works great.

When run in Access off of a connection object, I get the following error message:

-2147467259, xp_cmdshell failed to execute because current security context is not sysadmin and proxy account is not setup correctly.  For more information, refer to Book Online, search for topic related to xp_sqlagent_proxy_account.

I've tried to add the userid in the connection string to the master db, and grant it exec access to xp_cmdshell (btw is this a good idea?), but that didn't work.


CREATE PROC spm_create_stand_alone(@cases_id varchar(50)) AS
use dbname
Declare @cases_id varchar(50)
SELECT @cases_id = '15322 FOO-3'
Declare @bail_message varchar(100)
-- Make sure the case was pushed 
IF NOT EXISTS (SELECT cases_id FROM CLOG_CASE_LOG WHERE cases_id=@cases_id)
	SELECT @bail_message = 'Case ' + @cases_id + 'has not been extracted.'
	goto bail
-- Run the DTS package that writes to the Access database
exec master.dbo.xp_cmdshell 'dtsrun /Sserver /Utheuser /Pthepassword /Nthedtspackage, no_output
goto ex
-- This code processes if the deletion was unsuccessful based on any of the above criteria. 
	SELECT @bail_message as RETURN_VALUE
	goto ex

Open in new window

LVL 30

Assisted Solution

nmcdermaid earned 1400 total points
ID: 20362747
xp_cmdshell is strongly secured, for good reason.

From a security perspective here are the users that are allowed to run it:

1. A sysadmin user (sa is one of these but you can add more users that can be sysadmin)
2. If the user calling it is not a sysadmin user, then you need to:
     1. grant them rights to execute it
     2. Use xp_sqlagent_proxy_account to assign a windows user that xp_cmdshell will run under

LVL 66

Author Comment

by:Jim Horn
ID: 20366033

Which do you think would be easier to pull off, allowing a non-sysadmin user the ability to execute xp_cmdshell, or deploying the 'Microsoft DTS {whichever one is needed}' library to about four different users?

If it helps answer the question, this workplace is 25 users, with no external/web access to the SQL db whatsoever.

Could you give me some 'for example' code of your 2.1. and 2.2.?

On my pc since all the DTS-references are located in %PF%\Microsoft SQL Server\80\Tools\Binn, I'm guessing that this could only be pulled off by installing from the SQL install CD's.

LVL 30

Assisted Solution

nmcdermaid earned 1400 total points
ID: 20372600
Deploying the DTS library is easier, but

1. It runs on the client, not the server
2. I don't know the licensing repercussions
3. Everytime a new PC needs to run it, you need to install it on that PC

Here's a link of what you need to distribute if you want to do it that way.

Regarding giving non sysadmin users access: I've never quite got the proxy thing to work but I'm sure someone else has.

It may be easier to do the linked server thing. Again it can be tricky but it might be the most elegant solution.

1. Create a linked server to source database called MDBSource (see here
2. Create a linked server to destination database called MDBDestination
3. Create an SP like this:

CREATE PROC p_TransferData @Parameter INT

INSERT INTO MDBDestination...Table (C1,C2)
SELECT C1,C2 FROM MDBSource...Table
WHERE C3= @Parameter


Of course it won't be that simple. It may be tricky getting file access to the MDB, or matching up the parameter datatype
LVL 66

Author Comment

by:Jim Horn
ID: 20374080
> (see here
linky no worky

>It may be easier to do the linked server thing.
Okay, I'll give that a whirl.  Source is SQL2K db, Destination is Acc2K mdb.
LVL 30

Assisted Solution

nmcdermaid earned 1400 total points
ID: 20379289
Doh I hate those moving pages. I forgot the X on the end. It was just of a search anyway...

I just recalled that the linked servers have their own set of fun but try it anyway.
LVL 66

Author Closing Comment

by:Jim Horn
ID: 31409226
Sorry for abandoning the question guys.
I switched employers, so I no longer have this issue.  Will split based on perceived troubleshooting merit.

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

715 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