[AccessD] TableDefs vs. QueryDefs

Arthur,

DAO Code?

Set rst = CurrentDb.OpenRecordset("qryLCs" ,dbOpenDynaset)

That works for tables of queries, though I would not use CurrentDb. Rather set a variable first

Dim Db as DOA.Database
Set Db = CurrentDb
Set rst = Db.OpenRecordset("qryLCs" ,dbOpenDynaset)
...

A bunch of reasons for this, not least that when you call CurrentDb you get back a reference to the database in its current state. The next call to CurrentDb returns a *different* reference to the database. Not an issue most of the time, but can introduce subtle bugs. So it's best to get a Db object initialized and use that throughout your code.


Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Tuesday, October 23, 2012 5:02 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] TableDefs vs. QueryDefs

Apparently I mistakenly assumed that a TableDef and a QueryDef were equivalent, and would respond identically. This seems not to be so.

I have a table called Peripherals, and a pair of queries based on it. Both queries select all the fields explicitly (i.e. not *). One of them filters the PeripheralType column to "LC"; the other filters it to "THC". Both queries run fine by themselves.

<vba>
  Set rst = CurrentDb.TableDefs!Peripherals.OpenRecordset          ' this
line works fine
  Set rst = CurrentDb.QueryDefs!qryLCs.OpenRecordset                 ' this
one doesn't work at all
</vba>

What should the syntax be, to open a named query and create a recordset from it?

TIA,
Arthur
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com