sql server dts: Update an MS Access Table using DTS -- sql server dts

Update an MS Access Table using DTS

Kevin
1/31/2006 8:06:27 AM

sql server dts: I am attemping to update a MS Access table from a SLQ Table using DTS. I have
created a connection for both the Access db and the Server db.
Both contain the table costcenters with these fields costcenternumber and
costcentername.

The table in Access is a subset of the table on the Server.
The tansformation task gose from the Server connection to the MS Access
connection.

I tried using the following SLQ statement to limit the selection to only the
records in the MS Access table. But it does not like the reference to MS
Access.

SELECT costcenternumber,costcentername

FROM Server.dbo.costcenters

WHERE Server.dbo.costcenters.costcenternumber =
Access.costcenters.costcenternumber

Server = the cerver connection
Access = the access connection

Is the a way to refernce the Access table in the WHERE clause?
Or am I going about this all wrong?

Thanks Kevin

Re: Update an MS Access Table using DTS

Allan Mitchell
1/31/2006 8:21:49 AM

Hello Kevin,

You cannot do it like this. The Access connection is just a connection string
and not a lot else. You could create a Linked Server of the Access DB from
SQL Server and you could then use it in an ExecuteSQL task. You could also
use OPENDATASOURCE().

You would not really use the Transform data task either although you can
through judicious use of Lookups

Allan

[quoted text, click to view]