error when adding a single row of record using iteration within a range

[dingalpha]

Hi there,

I am connecting Excel 2013 to SQL Server 2012, trying to adding a sheet of data to a corresponding table in SQL Server. The code looks like this

Dim MoviesConn As ADODB.connection
Dim MoviesData As ADODB.Recordset  Dim r As Range

Set MoviesConn = New ADODB.connection
Set MoviesData = New ADODB.Recordset
MoviesConn.ConnectionString = ConstrSQL

MoviesConn.Open
On Error GoTo closeconnection


  With MoviesData
        .ActiveConnection = MoviesConn 
        .Source = "PM_portfolio"
        .LockType = adLockOptimistic
        .CursorType = adOpenForwardOnly 
        .Open
         On Error GoTo closerecordset

    Sheets("portfolio").Activate ‘this is the sheet in Excel that will be imported into the SQL Server table 

    For Each r In Range("A2", Range("A2").End(xlDown)) ' In sheet "portfolio", A1 is the field name, and new records starting from A2
         .AddNew
         .Fields("portfolio_name").Value = r.Offset(0, 0).Value
        .Fields("setup_date").Value = r.Offset(0, 1).Value
        .Fields("end_date").Value = r.Offset(0, 2).Value
        .Fields("margin_rate").Value = r.Offset(0, 3).Value
        .Fields("inner_end_date").Value = r.Offset(0, 4).Value
        .Fields("product_name").Value = r.Offset(0, 5).Value
        .Update
    Next r
  End With

The above code worked just fine when there were two or more records in "portfolio", i.e. at least A3 is not blank. However, if there was only a single record, i.e., only A2 contains data, there would be an error message when the program is tested.

I tried both google and mrexcel, however, found no luck. Anyone could help me out? I greatly appreciate any suggestion.

Thanks,

Yi

 

[StephenCrump]

Welcome to MrExcel!

If you have nothing below A2, your loop will try to add >1,000,000 records:

For Each r In Range("A2", Range("A2").End(xlDown))

Next r

 

[dingalpha]

thank you so much for pointing out this. I am not very familiar with that.

Can you please suggest any improvement so that it works even if there is only a single record.

Thanks,

Yi