MoveFirst in VBScript does not work with a query on a query?-CodePudding

I have a piece of VBScript that queries an MS Access database. When the recordset query is on a table, I can go through my recordset and do a rs.MoveFirst to go back to the beginning. But when the recordset query is on a query, rs.MoveFirst fails with the error "Operation is not supported for this type of object" Code: 800004005.
Is this a known limitation? Can I get get around it by opening the recordset in a different way? I have tried rs.Open like many examples online, but rs.Open strQuery, Cn, adOpenDynamic, adLockPessimistic, adCmdText fails with "Arguments are of the wront type, are out of acceptable range, or are in conflict with one another."

This code works because MyTable is a table:

Set rs = CreateObject("ADODB.Recordset")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyAccessDB.accdb;Mode=Read;"
connection.Open strConnection
Set rs = connection.Execute("SELECT * FROM MyTable")

MsgBox(rs.fields(1))
rs.MoveNext
rs.MoveFirst
MsgBox(rs.fields(1))

This code fails because MyQuery is a query in the database

Set rs = CreateObject("ADODB.Recordset")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyAccessDB.accdb;Mode=Read;"
connection.Open strConnection
Set rs = connection.Execute("SELECT * FROM MyQuery")

MsgBox(rs.fields(1))
rs.MoveNext
rs.MoveFirst
MsgBox(rs.fields(1))

Using rs.Open and defining the constants does not work. This shows the same error "Operation is not supported for this type of object" on the rs.movefirst command.

const adOpenDynamic = 2
const adLockPessimistic = 2
const adCmdText = 1

Set connection = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyAccessDB.accdb;Mode=Read;"
connection.Open strConnection
strsql = "SELECT * FROM MyQuery"
rs.Open strsql, connection, adOpenDynamic, adLockPessimistic, adCmdText

Do While Not rs.EOF
    msgbox(rs.fields(1))
    rs.movenext
    msgbox(rs.fields(1))
    rs.movefirst
    msgbox(rs.fields(1))
Loop

CodePudding user response：

There is a far easier way to deal with this problem and that is to negate ADODB.Recordset entirely and not have to worry about cursor and locking support. It's worth mentioning this will only work for reading the data.

Use GetRows() to retrieve a two-dimensional Array and use that to navigate the data.

Dim strConnection, connection, rs, data
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyAccessDB.accdb;Mode=Read;"
Call connection.Open(strConnection)
Set rs = connection.Execute("SELECT * FROM MyTable")
If Not rs.EOF Then data = rs.GetRows()

'Release recordset as it's no longer needed.
Call rs.Close()
Set rs = Nothing

Dim row, rows
Const fld_field1 = 1

If IsArray(data) Then
    rows = UBound(data, 2) 'Number of rows
    row = 0 
    Call MsgBox(data(fld_field1, row) 'Second column of First Row
    row = 1
    Call MsgBox(data(fld_field1, row) 'Second column of Second Row
    row = 0
    Call MsgBox(data(fld_field1, row) 'Second column of First Row

    'If you want to loop the data
    For row = 0 To rows
        Call MsgBox(data(1, row) 'Second Column of N Row
    Next
End If

CodePudding user response：

This will work.
You don't need to declare const, variables, whatelse.
You just need to set a reference to ADODB, in your case a reference to Microsoft Activex Data Objects 2.8 Library.
There is no reason this would not work.

Set Connection = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Dim strConnection
Dim sql

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyAccessDB.accdb;Mode=Read;"
Connection.Open strConnection

sql = "SELECT * FROM MyQuery"

rs.Open sql, Connection, adOpenStatic, adLockReadOnly, adCmdText

MsgBox (rs.Fields(1))
rs.MoveNext
MsgBox (rs.Fields(1))
rs.MoveFirst
MsgBox (rs.Fields(1))

rs.Close
Set rs = Nothing
Connection.Close
Set Connection = Nothing

EDIT: I overlooked the fact you wrote "piece of vbscript". If you are using this code in a vbs file, then you need to declare the constants

Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = &H0001