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

dingalpha

New Member
Joined
Nov 27, 2014
Messages
2
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

HTML:
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,759
Office Version
365
Platform
Windows
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

New Member
Joined
Nov 27, 2014
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,549
Messages
5,469,378
Members
406,647
Latest member
ssinovec

This Week's Hot Topics

Top