Solved

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

Posted on 2007-11-14
13
1,709 Views
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.

TIA
Jim
0
Comment
Question by:Jim Horn
13 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20283665
0
 
LVL 65

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.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 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?
0
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.

 
LVL 1

Accepted Solution

by:
mshaebanyan earned 100 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).

Regards,
Meredith Shaebanyan
0
 
LVL 65

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.
0
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 350 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.
0
 
LVL 65

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.

Jim

CREATE PROC spm_create_stand_alone(@cases_id varchar(50)) AS
 
SET NOCOUNT ON
 
/*
-- TESTING ONLY
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)
	begin
	SELECT @bail_message = 'Case ' + @cases_id + 'has not been extracted.'
	goto bail
	end
 
DELETE FROM MY_TABLE
 
INSERT INTO MY_TABLE (CASES_ID) VALUES (@cases_id)
 
-- Run the DTS package that writes to the Access database
exec master.dbo.xp_cmdshell 'dtsrun /Sserver /Utheuser /Pthepassword /Nthedtspackage, no_output
 
SELECT '1' as RETURN_VALUE
goto ex
 
-- This code processes if the deletion was unsuccessful based on any of the above criteria. 
bail:
	begin
	SELECT @bail_message as RETURN_VALUE
	goto ex
	end
 
ex:
GO

Open in new window

0
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 350 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

0
 
LVL 65

Author Comment

by:Jim Horn
ID: 20366033
@nmcdermaid

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.

0
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 350 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.

http://www.sqldts.com/225.aspx




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
AS

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

GO


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

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.
0
 
LVL 30

Assisted Solution

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

http://blogs.meetandplay.com/WTilton/archive/2005/04/22/318.aspx

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

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.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how the fundamental information of how to create a table.

773 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