DTS to MS Access via query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS to MS Access via query

Author

Topic

lkdunn
Starting Member

4 Posts

Posted - 2004-02-10 : 11:17:07

I am trying to update a MS Access table (2000) from an SQL(8.0) table via query.
1) How do I reference the Access table within the query?
Example:
SELECT DISTINCT [short_name],
[task_code],
task_name,
pmdb.privuser.TASKACTV.[task_id]
FROM pmdb.privuser.ACTVTYPE,PMDB.privuser.ACTVCODE, pmdb.privuser.TASKACTV, pmdb.privuser.TASK, pmdb.privuser.RSRC
where pmdb.privuser.ACTVTYPE.actv_code_type = 'Project Job No.'
and pmdb.privuser.ACTVCODE.actv_code_type_id = pmdb.privuser.ACTVTYPE.actv_code_type_id
and pmdb.privuser.ACTVCODE.actv_code_id = pmdb.privuser.TASKACTV.actv_code_id
and pmdb.privuser.TASKACTV.task_id = pmdb.privuser.TASK.task_id
and (short_name = '16225')

and Not EXISTS
/* this is the Access table */
(Select *
From [Connection 2].tblJobsCutLists
Where tblJobsCutLists.JobId = short_name and
tblJobsCutLists.CutlistId = task_code)
Order by short_name, task_code;

2) Can a stored procedure on the SQL server reference an SQL table and a linked Access table?
Example:
Procdedure sp_resync_taskname
CREATE TABLE #temp1
( Job char(5),
Taskcode char(10),
Taskname char(60));
GO
INSERT INTO #temp1
SELECT DISTINCT [JobId],
[CutListId],
CutListDescription
FROM ESRegister.dbo.tblJobsCutLists
where ESRegister.dbo.tblJobsCutLists.AddDate is null;
GO
Update ACCESSDB.tblJobsCutLists
Set ACCESSDB.AddDate = GETDATE(),
ACCESSDB.AddUser = 'DTS',
ACCESSDB.Acctnum = Substring (ESRegister.dbo.tblJobsCutLists.CutListId,3,7)
From #temp1
Where ACCESSDB.JobId = #temp1.job
and ACCESSDB.CutListId = #temp1.taskcode;

DROP TABLE #temp1

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-10 : 11:38:35

Did you set up a linked server?

Where are you running your query? QA?

Brett

8-)

lkdunn
Starting Member

4 Posts

Posted - 2004-02-10 : 12:13:38

I failed to say the query is within the DTS export.
Exporting the data from SQL db table to MS Access db table on network drive.

Linda

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-10 : 12:57:35

Why not create the query as a view on the server and DTS that to Acess...did you use the wizard or build it yourself?

Try the wizard first...

Brett

8-)

lkdunn
Starting Member

4 Posts

Posted - 2004-02-11 : 10:27:20

Even as a view, how do you specify an update from a SQL server connection to an MS Access db table connection? I thought a DTS could Update rows as well as insert new rows into an MS Access table. I tried the wizard, but it seems to only want to do INSERTS. I don't want to drop the access table and recreate it each time... only update the columns that changed - or update all rows for one column where they both exist is ok.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-11 : 15:14:30

Why not use an Execute SQL Task for the purpose?

update openquery(acc,'select * from t')
set m='ooo' from tt where n=1 and ...

Here "acc" - linked Access DB,
t - the access table, tt - a native SQL Server table.

lkdunn
Starting Member

4 Posts

Posted - 2004-02-13 : 12:11:41

Thanks for your replies. I found an example of a Data Driven Query Task. I used this to update the Access table from the SQL table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources