Overflow error after inserting only a few rows from Excel

Question

I'm attempting to set up a copy of the database onto an excel document that sales reps can update while they are out in the field. I've got the code for an insert statement to be run for the list rows, but I get an Overflow error after only 20 lines. Here is my code:

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

The total table size is only 1600 rows, so I wasn't expecting an overflow error. Any help would be appreciated!

score 1 · Accepted Answer · answered Apr 12 '16 at 18:35

1

You are getting the overflow error because one of the variables is not able to handle the input given to it. I strongly suspect that the culprit is Customer As Integer in

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

Change Customer As Integer to Customer As Long

answered Apr 12 '16 at 18:35

Siddharth Rout

147,039
17
206
250

aren't Ints automatically and quietly transmogrified to Longs in the background? – StormsEdge Apr 12 '16 at 18:40
1

@StormsEdge: [Interesting Read](http://stackoverflow.com/questions/26717148/integer-vs-long-confusion) ;) – Siddharth Rout Apr 12 '16 at 18:40
Correct you are Siddharth! I stumbled upon it when I was explaining the code out-loud to one of my coworkers and when I saw your comment, It was confirmed! Thank you! :) – Boris Demerichsky Apr 12 '16 at 18:42
Thanks @siddharth! – StormsEdge Apr 12 '16 at 18:44

Overflow error after inserting only a few rows from Excel

1 Answers1