| Allan Mitchell 2006-01-31, 11:24 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
> 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,cos
tcentername
>
> 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
>
|