QueryDefs("name") always returning first QueryDef in collection

[Tommy Hayes]

Hello all,

I have the following code snippet in my Access app:


Dim dbQueryDef As QueryDef
Set dbQueryDef = CurrentDb.QueryDefs("QUERY_NAME") ' (1)
Set dbQueryDef = CurrentDb.QueryDefs(26) ' (2)


Line (1) does not do as I expect it - it always returns the first
QueryDef in the QueryDefs collection (seen by examining the QueryDefs
object at debug time).


However if I pass the ordinal of the correct QueryDef as in line (2)
above then it works fine.


Anyone know what is going on? Is the string in (1) above being
converted into 1 before being passed to the Item(Item) method of the
collections? I'm a VBA newbie so please excuse the innocence of this
question.


Thanks,
Tommy.


PS: It's an Access 2000 file being run from within Access 2003 if
that's a factor. I need to keep it in 2000 format for the client.

 

[Rick Brandt]

Hello all,

I have the following code snippet in my Access app:


Dim dbQueryDef As QueryDef
Set dbQueryDef = CurrentDb.QueryDefs("QUERY_NAME") ' (1)
Set dbQueryDef = CurrentDb.QueryDefs(26) ' (2)


Line (1) does not do as I expect it - it always returns the first
QueryDef in the QueryDefs collection (seen by examining the QueryDefs
object at debug time).


However if I pass the ordinal of the correct QueryDef as in line (2)
above then it works fine.


Anyone know what is going on? Is the string in (1) above being
converted into 1 before being passed to the Item(Item) method of the
collections? I'm a VBA newbie so please excuse the innocence of this
question.

I use that first syntax all the time and have never had any problems.
You're quite sure you are spelling the query name correctly right?

 

[Tommy Hayes]

You're quite sure you are spelling the query name correctly right?

Yeah I've checked and re-checked. Weirder thing again is the
application runs fine on one computer but not on another, with both
computers having the exact same version of MS Access on them.

I'm currently re-installing Access on the offending computer in case
something was corrupted on that side.

Thanks,
Tommy.

 

[Aaron Kempf]

this type of code isn't necessary any longer

ALTER PROCEDURE SPHAPPY
AS
SELECT *
FROM SYSOBJECTS


move on kids

 

[Aaron Kempf]

and both databases are MDB?

you might have stumbled into someone SMART enough to use ADP and ADP doesn't
need that syntax

in ADP we use TSQL to edit stored procedures