Deleting & Adding Records In SQL Server Table Via ADO

[c68]

Deleting & Adding Records In SQL Server Table Via ADO

--------------------------------------------------------------------------------

Hey There,

I'm faced with a bit of frustration and need your help. An outside party has set up a standard table for us in a SQL Server (see below for description). On our-end, we need to maintain/update the data in that table via VBA. Before updating any fields, all previous loaded data points must be delete first. The table has more than 200 fields and our internal EXCEL file has 105 fields with approximately 50,000 thousand rows (not all fields need to be populated). The routines (Posted below) that I'm trying to utilize to achieve the stated goal appear to be very slow (primitive at best), particularly with deleting the data. Can some please help me speed up this process? Thank you so much.

Regards,
c68

Public dsSQL As String
Public Const ABC_Server_Name As String = "Provider=SQLOLEDB;Server=xxx.xx.x.xxx,1433;"
Public Const Driver = "Network Library=DBMSSOCN;"
Public Const ABC_Database_Name As String = "Initial Catalog=ABC_Group;"
Public Const ABC_User_Id As String = "User ID=ABC_Member"
Public Const ABC_Password As String = ";Password=ABC_Password;"

Private Sub Delete_All_Records_First()
Dim StrConn As String
Application.ScreenUpdating = False
Set rst = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")

myConnection = ABC_Server_Name & Driver & ABC_Database_Name & ABC_User_Id & ABC_Password
cn.Open myConnection

With rst
.Open "Select * From ABC_TABLE", _
myConnection, 1, 2
Do While Not .EOF
.Delete
.MoveNext
Loop
.Close
End With

Set rst = Nothing
Set cn = Nothing

End Sub
Private Sub UploadingNewRecords()
Dim i As Integer, lstCell As Long
Dim myConnection As String
Dim s As Worksheet, wbk As Workbook

Set wbk = ActiveWorkbook
Set rst = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")

Application.ScreenUpdating = False

myConnection = ABC_Server_Name & Driver & ABC_Database_Name & ABC_User_Id & ABC_Password
cn.Open myConnection

rst.CursorLocation = 2
rst.Open Source:="ABC_TABLE", _
ActiveConnection:=cn, _
CursorType:=2, _
LockType:=3, _
Options:=2

For Each s In wbk.Sheets
s.Activate
lstCell = [a65536].End(xlUp).Row

For i = 2 To lstCell

With rst
On Error Resume Next
.AddNew
rst.Fields("a1") = Cells(i, 1)
rst.Fields("a2") = sct
rst.Fields("a3") = Cells(i, 3)
rst.Fields("goes on and on") = Cells(i, etc)
.Update
'.Close
End With
Next
Next

Application.ScreenUpdating = True
Set rst = Nothing
Set cn = Nothing
MsgBox "Upload completed."
End Sub