How to modify my VBA code that can only target 1 row at a time to keep running for each additional row that is not empty?

Sub modIncTool()
'160908 modified s4p
   On Error Resume Next
   
   Dim nRow As Long _
      , theOriginalQuote _
      , theNewQuote _
      , theSiteGroup _
      , theTechnician _
      , theReason _
      , theDateRequested _
      , Result
      
   Dim objAcc As Object
   Set objAcc = GetObject(, "Access.Application")
   
   nRow = 2
   
   With Sheets("Import")
      Do While .Cells(nRow, 1) <> ""
         theOriginalQuote = .Range(.Cells(nRow, 1))
         theNewQuote = .Range(.Cells(nRow, 2))
         theSiteGroup = .Range(.Cells(nRow, 6))
         theTechnician = .Range(.Cells(nRow, 7))
         theReason = .Range(.Cells(nRow, 8))
         theDateRequested = .Range(.Cells(nRow, 9))
         Result = objAcc.Run( _
            "RecreateTool_AddRecord" _
            , theOriginalQuote _
            , theNewQuote _
            , theSiteGroup _
            , theTechnician _
            , theReason _
            , theDateRequested _
            )
         nRow = nRow + 1
      Loop
   End With
   Set objAcc = Nothing
   
   MsgBox "Did up to row " & nRow - 1, , "Done"
   
End Sub

Select all Open in new window

what do you plan to do with the Result ? It is calculated but goes nowhere ...

ok -- in Excel, it would be good to make the result something meaningful. Currently, you do not even need it -- you could use CALL instead or not surround arguments with parentheses since there is not anything returned ... however, there could be.

In Access, in the RecreateTool_AddRecord code, Make it a function and return a boolean value of true or false

Public Function RecreateTool_AddRecord(  ...) as boolean

Select all Open in new window

then instead of "CurrentDb.Execute sSQL, dbFailOnError"

with CurrentDb
   .Execute sSQL, dbFailOnError
   if .RecordsAffected >0 then
      RecreateTool_AddRecord =true
   else
      RecreateTool_AddRecord =false
   end if
end with 'currentdb

Select all Open in new window

then in Excel:

if objAcc.Run( _
            "RecreateTool_AddRecord" _
            , theOriginalQuote _
            , theNewQuote _
            , theSiteGroup _
            , theTechnician _
            , theReason _
            , theDateRequested _
            ) <> true then
   if msgbox ("Error occurred on row " & nrow _
         & vbcrlf & "Do you want to stop?" _
         vbyesno, "Error -- stop?") = vbyes then
      go to proc_exit
   end if
end if

Select all Open in new window

proc_exit would then be a line label added above the statement to release the Access object, which would be done after the message box

    MsgBox "Did up to row " & nRow - 1, , "Done"
Proc_Exit:
   on error resume next
   Set objAcc = Nothing

Select all Open in new window

space underscore at the end of a line means the statement is continued on the next line

Error handling could also be added. To learn more:

1. basic error handling code for VBA (3:48)
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

2. Run and Fix Code Loop through rows of an Excel spreadsheet using VBA (6:00)
http://www.experts-exchange.com/videos/1498/Excel-Error-Handling-Part-2-VBA-to-Copy-Values-Down-to-Blank-Cells-in-an-Excel-Column.html

3. Error Handling Part 3 - Run and Fix Bugs (7:51)
http://www.experts-exchange.com/videos/1518/Excel-Error-Handling-Part-3-Run-and-Fix-Bugs.html

it was only a suggestion ... the code I gave you should work fine.  Did you try it?