[AccessD] TableDefs vs. QueryDefs

<<So it's best to get a Db object initialized and use that throughout your
code.>>

 You have to be careful how you handle that.  I wrote an article on what I
think is the best overall solution for both performance and avoiding the
pitfalls:

CurrentDB() vs. dbEngine.Workspaces(0).Databases(0) and an alternative
http://rdsrc.us/OjCqDe

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Tuesday, October 23, 2012 05:11 PM
To: Access Developers discussion and problem solving
Subject: Re: [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

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