Import excel records with VBA into a SQL data table

[JannetteChristie]

I have the following code, but am getting a run-time error 438 - object dosen't support this property or method, can anyone tell me where I am going wrong please ?

Dim conn As New ADODB.Connection
Dim iRowNo As Integer
Dim cmd As ADODB.Command
Dim StrCon As String


Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
StrCon = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=Reporting;Data Source=dpsql02"
conn.Open StrCon
rs.Open "TenderingXLSelector", StrCon, adOpenKeyset, adLockOptimistic, adCmdTable




With Sheets("Main")
iRowNo = 10
With rs
.AddNew
Do Until .Cells(iRowNo, 3) = ""
.Fields("Oppo_number") = ActiveSheet.Range("OppoNumber")
.Fields("Product_code") = .Cells(iRowNo, 3)
.Fields("Quantity") = .Cells(iRowNo, 9)
.Fields("Selling_price") = .Cells(iRowNo, 10)
.Fields("Discount") = .Cells(iRowNo, 11)
.Fields("Order_line_value") = .Cells(iRowNo, 12)
.Fields("Process") = .Cells(iRowNo, 14)
.Fields("Dutyhead") = .Cells(iRowNo, 15)
.Fields("Flowrate") = .Cells(iRowNo, 16)
.Fields("Flowrate_per_pump") = .Cells(iRowNo, 17)
.Fields("Configuration") = .Cells(iRowNo, 18)
.Fields("Voltage") = .Cells(iRowNo, 19)
.Fields("Suction_size") = .Cells(iRowNo, 20)
.Fields("Discharge_size") = .Cells(iRowNo, 21)
.Fields("Maxhead") = .Cells(iRowNo, 22)
.Fields("Maxflow") = .Cells(iRowNo, 23)
.Fields("Motorspeed") = .Cells(iRowNo, 24)
.Fields("IP_rating") = .Cells(iRowNo, 25)
.Fields("Pressure_vessel") = .Cells(iRowNo, 26)
.Fields("Motor_KW") = .Cells(iRowNo, 27)
.Fields("FLC") = .Cells(iRowNo, 28)
.Fields("Suction_PN_rating") = .Cells(iRowNo, 29)
.Fields("Discharge_PN_rating") = .Cells(iRowNo, 30)
.Fields("Tendering_date") = Format(Now, "yyyy-mm-dd hh:mm:ss")
.Update
iRowNo = iRowNo + 1
Loop


End With
End With
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

 

[Joe4]

Are you able to complile the code?
If not, you may be missing some required libraries that you need to add/select in VBA.
See here: https://docs.microsoft.com/en-us/sq...h-microsoft-visual-basic?view=sql-server-2017