Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1730
  • Last Modified:

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

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.

Jim Horn
Jim Horn
6 Solutions
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
The link you posted is to a question I had previously asked, going from Access to SQL.  This question is SQL to Access.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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?
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
>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.
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.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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

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

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:

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.

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
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
> (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.
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.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now