从Excel中只插入几行后出现溢出错误

Question

我正试图将数据库的副本设置到excel文档上，销售代表可以在现场更新该文档。我已经获得了为列表行运行insert语句的代码，但仅在20行之后就出现了Overflow错误。这是我的代码：

Sub ConnectTODB()
  Dim CustomersConn As ADODB.Connection
  Dim CustomersCmd As ADODB.Command
  Dim lo As Excel.ListObject
  Dim ws As Excel.Worksheet
  Dim lr As Excel.ListRow
  Set ws = ThisWorkbook.Worksheets(8)
  Set lo = ws.ListObjects("TCustomers")
  Set CustomersConn = New ADODB.Connection
  Set CustomersCmd = New ADODB.Command
  CustomersConn.ConnectionString = SQLConStr
  CustomersConn.Open
  CustomersCmd.ActiveConnection = CustomersConn
  For Each lr In lo.ListRows
    CustomersCmd.CommandText = _
    GetInsertText( _
    Intersect(lr.Range, lo.ListColumns("Type").Range).Value, _
    Intersect(lr.Range, lo.ListColumns("Customer").Range).Value, _
    Intersect(lr.Range, lo.ListColumns("Name").Range).Value, _
    Intersect(lr.Range, lo.ListColumns("Contact").Range).Value, _
    Intersect(lr.Range, lo.ListColumns("Email").Range).Value, _
    Intersect(lr.Range, lo.ListColumns("Phone").Range).Value, _
    Intersect(lr.Range, lo.ListColumns("Corp").Range).Value)
    CustomersCmd.Execute
  Next lr
  CustomersConn.Close
  Set CustomersConn = Nothing
End Sub
Function GetInsertText(CType As String, Customer As Integer, Name As String, Contact As String, Email As String, Phone As String, Corp As String) As String
  Dim SQLStr As String
  SQLStr = _
  "INSERT INTO Customer (" & _
  "Type, Customer,Name,Contact,Email,Phone,Corp)" & _
  "VALUES (" & _
  "'" & CType & "'," & _
  "'" & Customer & "'," & _
  "'" & Name & "'," & _
  "'" & Contact & "'," & _
  "'" & Email & "'," & _
  "'" & Phone & "'," & _
  "'" & Corp & "')"
  GetInsertText = SQLStr
End Function

表的总大小只有1600行，所以我没想到会出现溢出错误。任何帮助都将不胜感激！

Answer 1

您得到溢出错误是因为其中一个变量无法处理给它的输入。我强烈怀疑罪魁祸首是中的Customer As Integer

Function GetInsertText(CType As String, Customer As Integer, Name As String, Contact As String, Email As String, Phone As String, Corp As String) As String

将Customer As Integer更改为Customer As Long