Import data from SQL Server to Access DB

[scottsanpedro]

Hi,
Hoping there is a possible way to transfer data between sqlserver 2000 and MS Access 2002.
Currenly any new data appends itself to the table that is already there. Is there a way to delete the table in Access from DTS?
Bit of a long short maybe
Thanking you for any advice.
Scott

 

[scottsanpedro]

think I might have figured it out.
Create a blank DB with the same name. Get DTS to FTP it and overwrite. Then export the fresh tables.
I will give it a go

 

[AlexCuse]

scottsanpedro -

Did you know that if you have the access connection set up in your DTS, you can run delete queries in it just like it is a SQL database?

So you could just add a SQL Task, set the connection to your access connection, and execute the query 'delete myTable from myTable', and voila, your table is empty.

I imagine this would be much less resource-intensive than ftp-ing ;-)

HOpe this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[scottsanpedro]

no I didnt know that..I will certainly try that out.
excellant

 

[scottsanpedro]

works 100%. many thanks