Replace SQL Table with Excel data

[Guest]

I have a SQL database with a table that I need to periodically replace with
data from an Excel spreadsheet.

I wrote a proceedure using ADO to delete each record one by one - then
adding each record one by one, but it takes forever.

Is there a better way to do this?

 

[Dave Patrick]

The delete is easy. Actually I'd probably do it all from the server using
DTS Some ideas here. http://www.sqldts.com/

Dim strCnn As String
strCnn = "Provider=SQLOLEDB.1;" _
& "Integrated Security=SSPI;" _
& "Persist Security Info=False;" _
& "Initial Catalog=East_Intermediate;Data Source=pe1600"
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open strCnn
cnn.Execute "DELETE FROM Table_1"
cnn.Close

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I have a SQL database with a table that I need to periodically replace with
| data from an Excel spreadsheet.
|
| I wrote a proceedure using ADO to delete each record one by one - then
| adding each record one by one, but it takes forever.
|
| Is there a better way to do this?