Go Premium for a chance to win a PS4. Enter to Win


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
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?
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!


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 http://blogs.meetandplay.com/WTilton/archive/2005/04/22/318.asp)
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 http://blogs.meetandplay.com/WTilton/archive/2005/04/22/318.asp)
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

916 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