Run-Time Error 91

[ChiOscar]

I have the following code that use to work fine when my front end was in a .ACCDB file. After I migrated the backend to SQL server I changed the fron end to a .adp file and now my code won't work. I get the following error message "Run-Time error 91". Can someone please help.

Sub GetDBUsers()
Dim db As Database
Dim rst As Recordset
Dim StrSQL As String

StrSQL = "SELECT UserName FROM Employee ORDER BY UserName"

Set db = CurrentDb
Set rst = db.OpenRecordset(StrSQL)

rst.MoveFirst
While Not rst.EOF
Debug.Print rst!UserName
rst.MoveNext
Wend

Set db = Nothing
Set rst = Nothing

End Sub

 

[CJ_London]

error 91 seems to cover a number of sins!

Which version of access were you using? it may be something to do with changing from DAO to ADODB. you could try redefining the Dims to DAO.Database and DAO.recordset or ADODB.Database and ADODB.Recordset.

Also, I've never seen this before:

rst!UserName

it would usually be rst.fields(0) or rst.fields("UserName")

 

[boblarson]

error 91 seems to cover a number of sins!

Which version of access were you using? it may be something to do with changing from DAO to ADODB. you could try redefining the Dims to DAO.Database and DAO.recordset or ADODB.Database and ADODB.Recordset.

Yes, the recordset object declaration is the likely candidate.

Also, I've never seen this before:

rst!UserName

it would usually be rst.fields(0) or rst.fields("UserName")

Actually rst!UserName is quite common and quite fine to use.

 

[CJ_London]

You learn something new every day!

Cuts down a bit on the typing, I'll have to adopt it going forward..

 

[ChiOscar]

It turned out that I should have done ADODB.recordset vs DAO.recordset.