Insert an Excel range to SQL Server using ADO (push from Excel)

Is it that I have set my SQL Server as an ADO connection but not Excel??

Or can I read the range into an array and put the array as the data source in the INSERT statement?

Well I am just going to do it by inserting rows of data in a loop, not an entire range.

Although I have made a loop to insert 10 rows with each INSERT INTO statement.

INSERT INTO table VALUES (row1value1,row1value2),(row2value1,row2value2),(row3value1,row3value2)

This enters my 3,000 rows of data in 2 seconds rather than 5 seconds doing each row separately.
Interestingly inserting 100 rows at a time takes 10 seconds!
For whatever reason 10 at a time seems to work fastest and most consistently.

My somewhat inelegant final code:

Sub InsertIn10s()
'Under Tools > References, set a reference to 'Microsoft ActiveX Data Objects 2.8 Library'.

Dim con As New ADODB.Connection, rs As New ADODB.Recordset, row1 As Integer, cols1 As Integer, shtName As String, server As String, table As String, database As String
     
server = "BKBAS-DATA\SQLEXPRESS"
table = "dbo.ahr1304_FORECAST"
database = "SimpleSQL"
shtName = "Forecast"
row1 = 2   'set first row with records to import
cols1 = 45 'number of columns to import
             
   'make the connection to the SQL Server
   con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
   Set rs.ActiveConnection = con
            
   con.Execute "DELETE FROM " & table 'delete all records first

   Do Until Sheets(shtName).Cells(row1, 1) = ""       'process all rows until you get to a blank one
        
        SQL1 = "INSERT INTO " & table & " VALUES ("   'create the SQL statement
        
        For x = 1 To 10                               'add 10 rows
         If Sheets(shtName).Cells(row1, 1) <> "" Then 'except blank ones
        
           For c = 1 To cols1                         'add all of the columns
              SQL1 = SQL1 & "'" & Sheets(shtName).Cells(row1, c) & "', "
           Next c
           SQL1 = Left(SQL1, Len(SQL1) - 2) & "), ("  'remove the last comma and add brackets
           row1 = row1 + 1                            'increase row counter
        
         End If
        Next x
        
        SQL1 = Left(SQL1, Len(SQL1) - 3) 'remove the last comma and open bracket
        con.Execute SQL1                 'insert row into database
        
   Loop
            
     con.Close         'close the connection
     Set con = Nothing 'release the memory
     
     MsgBox "Import Complete!", vbInformation

End Sub