问将大型数据存储到SQL数据库

EN

'Opens the SQL server

dbs.Open "Data Source =; Initial Catalog = ;Trusted_connection = Yes; Provider = ;; Integrated Security=SSPI;"

dbs.Execute "INSERT INTO Acdbo. CHECKLIST([FileTime], [FileName], [AccName], [EffDate], [PolicyType], [Premium], [Underwriter], [Auditor],[UT_Score],[Underwriter_Score] ) " _
            & "VALUES ('" & FileTime & "','" & FileName & "','" & AccName & "','" & EffDate & "','" & policy_type & "','" & premium_amt & "','" & UW_Name & "','" & Aud & "','" & ut * 100 & "','" & uw_score * 100 & "')"

Set rcd = dbs.Execute( _
        "SELECT Acdbo.AUDIT_CHECKLIST.FileID " _
      & "FROM Acdbo.AUDIT_CHECKLIST " _
      & " WHERE Acdbo.AUDIT_CHECKLIST.FileTime  =  " & Chr(39) & FileTime & Chr(39) _
      & " AND Acdbo.AUDIT_CHECKLIST. FileName = " & Chr(39) & FileName & Chr(39))


If rcd.EOF Then
    MsgBox "Error", vbCritical
 End
End If

rcd.MoveFirst
FileID = rcd!FileID
rcd.Close


Dim iRowNo As Integer
Dim sLabel As String
Dim sData As String
Dim sAdditionalComments As String
'Dim sLink As String

  With Sheets("Data_upload")

  'Skip the header row
    iRowNo = 2

   'Loop until empty cell in CustomerId
     Do Until .Cells(iRowNo, 2) = ""

        sLabel = .Cells(iRowNo, 2)
        sData = .Cells(iRowNo, 4)
        sAdditionalComments = .Cells(iRowNo, 5)
        'sLink = .Cells(iRowNo, 6)

       'Generate and execute sql statement to import the excel rows to SQL Server table
        dbs.Execute "Insert into Acdbo. CHECKLIST_DATA([FileID], [Label], [Data], [AdditinalComments]) values ('" & FileID & "', '" & sLabel & "', '" & sData & "','" & sAdditionalComments & "')"
         On Error Resume Next

        iRowNo = iRowNo + 1
    Loop 
 End With


   endTime = Timer
   dbs.Execute "UPDATE Acdbo. CHECKLIST SET [UploadTime] = " & endTime - startTime & " WHERE FileID = " & FileID      'Upload the time it takes to upload Checklist
   dbs.Close

Dim Response As VbMsgBoxResult

Response = MsgBox("File Uploaded", vbOKOnly, "Database Upload")
End

'The following block of code provide procedures once an error occurs
Error_Handler:

'Upon error, hide RDT's "DatabaseExtract" tab and lock down Audit checklist's structure
'ActiveWorkbook.Sheets("DatabaseExtract").Visible = False
'ActiveWorkbook.Protect Structure:=True, Windows:=False, password:=pwd_WorkBook

'Then display with the error message and exit the macro
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & Application.VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"

Application.ScreenUpdating = False

 End Sub