listbox recordset

[JB]

I am currently working on an Access app using Access 2003 with security. I
have many listboxes that are populated with RWOP queries using code similar
to the following
Dim qry As QueryDef
Dim prmClientID As Parameter
Set qry = CurrentDb.QueryDefs("qryData_GetEmployeeList")
Set prmClientID = qry.Parameters!prmClientID
prmClientID = 1
Dim rst As DAO.Recordset
Set rst = qry.OpenRecordset()
With EmployeeList
.RowSourceType = "Table/Query"
Set .Recordset = rst
End With
Set prmClientID = Nothing
Set rst = Nothing
Set qry = Nothing

this works fine in Access 2003 but I have just discovered that Access 2000
apparently doesn't support the recordset property and so the above code won't
run.
Given that I am using security and so must use stored queries, what is the
equivalent syntax that will work under Access 2000. The sticking point is
how to implement reference to a parameterized query without the recordset
property?

 

[Douglas J. Steele]

Access 2000 supports it. It's just that the reference to DAO isn't set by
default.

Go into the VB Editor, select Tools | References from the menu, scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, select it, then back out of the dialog.

Because ADO will be higher in the list of References than DAO, you'll need
to change your declaration from

Dim prmClientID As Parameter

to

Dim prmClientID As DAO.Parameter

When you have both references, it's especially important that you
"disambiguate" your declarations, because objects with the same names exist
in the 2 models. For example, to ensure that you get a DAO recordset, you'll
need to use Dim rst as DAO.Recordset, whereas to guarantee an ADO recordset,
you'd use Dim rst As ADODB.Recordset.

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

 

[[MVP] S.Clark]

Query Parameters, and the Recordset property aren't really related.

Also, I don't know why you're retrieving a value into prmClient, then
immediately setting the value to 1.

For both versions, you could put the parameter in the query, pull it off a
different form, then populate the EmployeeList form, by setting the
recordsource to the query. Unless I'm missing something, all this code is
overkill.

 

[JB]

Thanks for the reply. I am already referencing DAO 3.6 and am not
referenceing ADO at all. I have been developing under Access 2003, XP Pro
where everything is ok but this morning when I tried to run the app on
another machine with Access 2000 installed, it failed because it didn't
recognize the Recordset property. The version I tried was compiled and uses
a startup form set using the startup dialog. I will need to find some way to
deal with this that doesn't on the setup of the machine on which the app is
to run. Another issue is that the version I tried is a .mdb file whereas
eventually the version to be installed will be a .mde file so vb won't be
around for setting references. I used to use SQL strings to initialize
listboxes of the sort strSQL = "SELECT .... FROM .... WHERE ClientID = " &
Cstr(lClientID) & " ;" but with security, that isn't allowed which brings one
to parameterized stored queries and recordsets.

 

[JB]

I simplified my code example to show the essential idea. The value of 1 for
the clientID is just an example, it real life, this bit of code would be in a
method with input parameters that determine, in this case, the clientID. In
some cases, there are only one or two parameters but in others, there are
many more.

 

[Douglas J. Steele]

I didn't read your example closely enough: I missed the fact that you were
trying to use a DAO recordset with a list box.

You can set your parameters to refer to fields in a form, even if the form
isn't visible.

For the record, references only have to be set once: they travel with the
database. As long as your references are correct in the MDB, the MDE
references will be correct (and, in fact, you won't be able to create the
MDE unless the references are correct)

Sorry for the confusion.

 

[JB]

I am not sure that I have made clear what the problem. Without security I
would use code like
Dim strSQL as string
strSQL = "SELECT ... FROM... WHERE ClientID = " & CSTr(lClientID)
with EmpList
.RowSourceType = "table/query"
.Rowsource = strSQL
end with

with security sql strings cannot be used so change to stored RWOP query
Dim qry as QueryDef
Dim prmClientID as Parameter
set qry = CurrentDb. etc
set prmClientID = etc
prmClientID = lClientID

Now need to bind to list box
Using
with EmpList
.RowSourceType = "table/query"
.rowsource = qry --does not work, wrong type
.rowsource = qry.SQL -- does not work because qry.SQL does not contain
the
values of the parameters so user is
prompted for the parameters

In Access 2003, I can use
dim rst as recordset
set rst = qry.OpenRecordset
with EmpList
set .Recordset = rst
end with

In Access 2000, this doesn't work because EmpList does not have the
..recordset property, or at least the access 2000 on the machine I tried this
on did not have the recordset property. So the question is how to bind the
stored, parameterized query to the list box without using set .recordset = rst

 

[Brendan Reynolds]

You can still use dynamic SQL in a secured MDB, just use a saved, RWOP query
in place of the table in the FROM clause of the dynamic SQL ...

strSQL = "SELECT ... FROM SomeSavedRwopQuery WHERE ClientID = " &
CSTr(lClientID)

The user needs read permission on the query ("SomeSavedRwopQuery" in the
example above). You do not have to assign any new permissions on the
underlying table.

 

[JB]

Thanks for the reply. I didn't know about this and it should fix up my
problem. It will also take care of another problem I had with regard to
error 3048(cannot open any more databases) that arises from using recordsets
to populate listboxes. Each open recordset increases the database count by 1
with a limit of around 85 databases. Using tab controls containing list
boxes and a few bound forms and subforms, it doens't take very long to reach
the limit.