Solved

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

Posted on 2007-11-14
13
1,707 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
Comment Utility
0
 
LVL 65

Author Comment

by:Jim Horn
Comment Utility
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
Comment Utility
>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
 
LVL 1

Accepted Solution

by:
mshaebanyan earned 100 total points
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 65

Author Comment

by:Jim Horn
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
> (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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now