Problem with VB6 and msaccess query

[adbanginwar]

I am writing a code which extracts data from a msaccess database to create certain report. For this purpose I wrote couple of queries and tried them in msaccess. One of the query has multiple occurance of 'avg()' function in it. This query runs fine in msaccess but gives error in vb6. I am using a cursor to fetch the data.

Following is my code :

Public Function get_avg_in_table(ByRef dbcon As ADODB.Connection, ByVal eventid As Integer, ByVal scriptid As Integer) As Integer
    Dim recordset_avg As ADODB.Recordset
    Dim create_cmd As ADODB.Command
    Dim iLoop As Integer
    Set create_cmd = New ADODB.Command
   
    Set recordset_avg = New ADODB.Recordset
    recordset_avg.Source = "SELECT Breakdown_meter.[Event ID],Avg(Breakdown_meter.Value),Avg(Breakdown_meter.Connection) FROM Breakdown_meter WHERE Breakdown_meter.[Script ID]=26 group by Breakdown_meter.[Event ID]"
    
    With recordset_avg
        .CursorLocation = adOpenStatic
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        Set .ActiveConnection = dbcon
    End With
    
    'This is where i get error "unable to open the cursor error"
    recordset_avg.Open
    
    MsgBox recordset_avg.RecordCount
    
    For iLoop = 0 To (recordset_avg.RecordCount - 1)
        Debug.Print recordset_avg.Fields(0).Value & "     " & recordset_avg.Fields(1).Value
        recordset_avg.MoveNext
    Next
    recordset_avg.Close

End Function

I want to average around 8 columns.
can anyone please help?

[DataMiser]

.CursorLocation = adOpenStatic ?

Try

.CursorLocation=adUseClient

[Shuja Ali]

CursorLocation can only be set to one of these values

adUseClient
adUseServer

You have mistakenly written adOpenStatic which is why your code is not working.

[adbanginwar]

Tried both adUseClient and adUseServer. still not working.

I get following error;

Runtime error '-2147467259(80004005)':
Method 'Open of object ' _Recordset failed

[DataMiser]

Well you got past one error, It was the cursor that was giving the error before and now you are getting a recordset error. I do not use the method you are using to open the recordset so I can not tell if you have something out of wack here, hopefully someone else who uses this method will chime in.

[adbanginwar]

Well you got past one error, It was the cursor that was giving the error before and now you are getting a recordset error. I do not use the method you are using to open the recordset so I can not tell if you have something out of wack here, hopefully someone else who uses this method will chime in.

can you tell me about your method? i can change my code accordingly.
a small code snippet would be really helpful.

[adbanginwar]

Its resolved. One of my columns had space at the end.

Thanks for the info but Datamiser please tell me your method.

[DataMiser]

I normally use the single line method for opening the recordset.

Dim db As New Connection
Dim RS As New Recordset

db.CursorLocation = adUseClient
db.Open PioneerCN

RS.Open "select * from specieslist order by description", db, adOpenStatic, adLockOptimistic