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

Question

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

[`rs.Open()`](https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/open-method-ado-recordset?view=sql-server-ver15) is the correct approach but in VBScript named constants like `adOpenDynamic` need to defined as VBScript does not know about them for example `Const adOpenDynamic = 2`. Use the [ADO Documentation](https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/cursortypeenum?view=sql-server-ver15) to lookup the values of named constants to workout how to define them. — user692942, Feb 22 '22 at 08:45
Does this answer your question? [ASP 3.0 Declare ADO Constants w/out Including ADOVBS.inc](https://stackoverflow.com/questions/5145607/asp-3-0-declare-ado-constants-w-out-including-adovbs-inc) - Explains defining constants from ADODB. — user692942, Feb 22 '22 at 08:50
Deleted my first comment because my test was wrong. I did forget that the constants were not defined in VBScript, but defining them did not help. — Maarten Deen, Feb 22 '22 at 09:37
I changed the post to add the code with the rs.open with the constants defined and specified what error I got. I also pointed out the strange difference between doing this on a table as opposed to doing this on a query in the databse. I'm not sure what you're angry about. I guess the big question is: has anyone got this to work when you do this selection on a query in the database? — Maarten Deen, Feb 22 '22 at 10:37

user692942 · Answer 1 · 2022-02-22T14:06:06.020

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

That's a good option as well, except that we commonly access the recordset using the named fields e.g. `rs.Fields("Field1")` and this does seem not support that. Pity, since it is a bit tidier with closing the recordset early and with full loop control. — Maarten Deen, Feb 22 '22 at 13:44
@MaartenDeen That's fair enough, only suggested it as you were accessing the fields via ordinal position in your sample code. You can name the ordinals by specifying them as named constants so something like `Const fld_field1 = 1` and use that in place of the first element i.e. `data(fld_field1, row)`. — user692942, Feb 22 '22 at 14:04

score 1 · Accepted Answer · edited Feb 22 '22 at 15:59

1

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

edited Feb 22 '22 at 15:59

user692942

16,398
7
76
175

answered Feb 22 '22 at 11:20

Francesco Giossi

158
9

1

Your code is not vbscript and will result in syntax errors – Geert Bellekens Feb 22 '22 at 11:45
After adapting it to VBScript it does work. And it seems the trick is to use adLockReadOnly. When I change that to adLockPessimistic I get the "Operation is not supported" error again. Thanks! – Maarten Deen Feb 22 '22 at 12:20
@GeertBellekens Yup, my bad, I got it after I posted, then edited, but I forgot to remove "as string". Anyway, remove "as string" and it works. – Francesco Giossi Feb 22 '22 at 12:58
Const adCmdText = &H0001 comes from adovbs.inc Microsoft file. It's very interesting, I never really had a look to the file. It contains const declared as hex or number. For example, Const adOpenStatic = 3, Const adCmdText = &H0001, Const adHoldRecords = &H00000100. The 1st one has 1 byte, the second 2 bytes, the third 4 bytes. Is that the reason? I really don't know. Maybe is a merge coming from different teams. LOL – Francesco Giossi Feb 22 '22 at 16:23

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

2 Answers2