Runtime Error '9' Index is outside of Array - CopyFromRecordset

[tjuven77]

Hi,

This is my first thread so if something seems to be not following the standard of threads, please let me know!

I am trying to get data from my companies SQL server and showcase it starting at a specified cell.
The SQL_Read works fine but when i come to the 'Print out values beginning at A31' I get an error message.

Have any tips what could be causing it or what needs to be changed?

I do realize that some of the code is printed rather ugly ... Just presume that everything before the error message works :)


When it comes to the line:
Worksheet name = "Oregistrerade fordon"
Cell to start pasting= A31
Recordset to copyfrom= rRecordset



Code:

Sub vehiclesIntGetVehicles()


Dim SQLDB As ADODB.Connection
Dim sclient As String
Dim Product As ProductType
Dim sQuery As String
Dim result As QueryResult

Dim dbConnection As ADODB.Connection
Dim rRecordSet As New ADODB.Recordset
Dim oResult As QueryResult

Application.ScreenUpdating = False

'Connect to SQL
Set SQLDB = SQL_Open("Departments")


With wrsVehicleInt

If .Range("vehicleIntHoldingCompanyName") <> "" And _
.Range("vehicleIntCompanyName") <> "" And _
.Range("vehicleIntOrgNr") <> "" Then


'Finds the latest versions and all other data needed

sQuery = "SELECT a.RegNrMP, v.make as Fabrikat, v.idchassi as EgetID, v.chassinr as Chassinr, v.age as Ålder,v.weight as Vikt, v.value as Nyvärde, vt.VehicleType as Fordonstyp, cover.CoverName as omf, a.valueorweight as Värde,valuetype.ValueTypeName as TypAvVärde , t.TraficName as Avställd, a.TransactionCount as Nummer,w.Text as Typ, a.StartDate as Startdatum,a.Enddate as Slutdatum FROM [Departments].[NonLifePortfolioInsurance].[MP_Vehicle_Transaction] as a " & _

"INNER JOIN (SELECT RegNrMP, MAX(TransactionCount) TransactionCount FROM [Departments].[NonLifePortfolioInsurance].[MP_Vehicle_Transaction]GROUP BY RegNrMP) as b ON a.RegNrMP = b.RegNrMP AND a.TransactionCount = b.TransactionCount " & _
"left join Departments.NonLifePortfolioInsurance.MP_transaction_type as w on w.transactiontype=a.transactiontype " & _
"left join Departments.NonLifePortfolioInsurance.MP_vehicle as v on a.RegNrMP=v.RegnrId " & _
"left join Departments.NonLifePortfolioInsurance.MP_Insurance as i on a.client=i.clientid " & _
"left join [Departments].[NonLifePortfolioInsurance].[MP_Trafic] as t on t.TraficId=i.TraficId " & _
"left join departments.NonLifePortfolioInsurance.MP_Vehicle_Type as vt on vt.vehicletypeid=a.VehicleType " & _
"left join [Departments].[NonLifePortfolioInsurance].[MP_ValueType] as valuetype on valuetype.ValueType=a.ValueType " & _
"left join [Departments].[NonLifePortfolioInsurance].[MP_Cover] as Cover on Cover.Coverid=a.cover " & _
"left join [Departments].[NonLifePortfolioInsurance].[MP_Client] as c on c.clientid=a.Client " & _
"where c.organisationNr='5560861741' group by a.client, v.make, a.TransactionCount, a.Enddate, a.StartDate, a.RegNrMP, v.chassinr, a.startdate, v.idchassi,v.age,v.weight, v.value, w.Text, t.TraficName, vt.VehicleType, valuetype.ValueTypeName ,a.valueorweight, cover.CoverName;"


Set rRecordSet = SQL_Read(SQLDB, sQuery)

If rRecordSet.State <> 0 Then


'Print out values beginning at A31
Worksheets("Oregistrerade fordon").Range("A31").CopyFromRecordset (rRecordSet)




End If

Else
MsgBox ("Information saknas")
End If

End With


End Sub