Exists() function seems broken.. Any help?

[BrianDP]

This is my attempt at an exists() function, I can't seem to find one
that will tell me if a given name, exists, as a table, or query, or
report. I Don't even need to search tables, but the only other
routines I have seen search tabledef anyway!! Here goes. (Sorry if
I've double-posted this, I can't find my first post!)

Public Function eXists(objectName As String) As Boolean
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
' Set rst = db.OpenRecordset("select * from [msysobjects] where
([name] = """ & objectName & ")")
Set rst = db.OpenRecordset("select * from [msysobjects] where
[msysobjects].[name] = """ & objectName & """")

' "cindyrpt - 336 mailing label Listing - all contacts"
If rst.NoMatch Then
eXists = False
Else
eXists = True
End If

End Function

 

[tina]

you can't use the NoMatch property because you didn't use a Seek or Find
method on the open recordset. remember, you're already opening a filtered
recordset, so you only need to see if a record was returned. also, Exists
may be a reserved word, suggest you rename the procedure. try the following,
as

Public Function isFound(ByVal objectName As String) As Boolean

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim str As String

str = "SELECT MSysObjects.Name FROM MSysObjects " _
& "WHERE MSysObjects.Name = '" & objectName & "'"

Set db = CurrentDb
Set rst = db.OpenRecordset(str, dbOpenDynaset)

isFound = Not (rst.BOF And rst.EOF)

rst.Close
Set rst = Nothing

End Function

when a recordset is empty, both BOF and EOF are True. in that case, the
object was not found, so we want to report False, so we "flip" the return
value using Not.

hth
tina

 

[BrianDP]

Thank you very much.

This is part of a larger process I was writing. Here is a brief
explanation of my story. I'll probably repost this so, maybe you
don't need to read.

This db is a database that tracks sales information, the back end is
SQL, and the front end is plane jane access, 2002, and most of the
clients run the db with the stripped down runtime executable.

I have one user who makes changes to her front end of the sales
database. When I make changes to the database, Which I do all the
time since It's pretty much my database. Okay, when I release an
update, and she overwrites her old one, she loses all her queries and
Reports. She doesn't seem to create macros or tables.

Luckily, all of her objects are prefixed with the name "Cindy."

So, you can see how the isfound() fits in with all of this, I can't
import that report/query if it's already there or I'll get a 1.

Well, now the program is starting to barf because it can't get read
access to Msysobjects in a database that isn't the current one. I
did...

dim fDB as database

set fDb = "C:\accessxp\sales\salesold.mdb"

set rst = db.openrecordset("msysobjects")

and it gets upset right there when it tries to open the other
database's msysobjects saying it doesn't have read permission.

any help?

Thanks again Tina.

-Brian P.
Best Data Processing - Holiday, FL

you can't use the NoMatch property because you didn't use a Seek or Find
method on the open recordset. remember, you're already opening a filtered
recordset, so you only need to see if a record was returned. also, Exists
may be a reserved word, suggest you rename the procedure. try the following,
as

Public Function isFound(ByVal objectName As String) As Boolean

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim str As String

    str = "SELECT MSysObjects.Name FROM MSysObjects " _
        & "WHERE MSysObjects.Name = '" & objectName & "'"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(str, dbOpenDynaset)

    isFound = Not (rst.BOF And rst.EOF)

    rst.Close
    Set rst = Nothing

End Function

when a recordset is empty, both BOF and EOF are True. in that case, the
object was not found, so we want to report False, so we "flip" the return
value using Not.

hth
tina

This is my attempt at an exists() function, I can't seem to find one
that will tell me if a given name, exists, as a table, or query, or
report.  I Don't even need to search tables, but the only other
routines I have seen search tabledef anyway!!  Here goes.  (Sorry if
I've double-posted this, I can't find my first post!)

Public Function eXists(objectName As String) As Boolean
 Dim db As Database
 Dim rst As Recordset

 Set db = CurrentDb()
'  Set rst = db.OpenRecordset("select * from [msysobjects] where
([name] = """ & objectName & ")")
 Set rst = db.OpenRecordset("select * from [msysobjects] where
[msysobjects].[name] = """ & objectName & """")

' "cindyrpt - 336 mailing label Listing - all contacts"
If rst.NoMatch Then
 eXists = False
Else
 eXists = True
End If

End Function- Hide quoted text -

- Show quoted text -

 

[David-W-Fenton]

when a recordset is empty, both BOF and EOF are True.

A more reliable test if whether or not .Recordcount = 0, and it's
only one property that needs to be tested.

 

[David-W-Fenton]

m:

Well, now the program is starting to barf because it can't get
read access to Msysobjects in a database that isn't the current
one. I did...

dim fDB as database

set fDb = "C:\accessxp\sales\salesold.mdb"

set rst = db.openrecordset("msysobjects")

and it gets upset right there when it tries to open the other
database's msysobjects saying it doesn't have read permission.

You should not bother trying to use the system table. Just use the
QueryDefs and AllReports collections of the other database. The
former you can use with a DAO variable, but the former requires an
ADO connection object. I don't actually know how to do the latter,
since I never use ADO, nor any of the AllXXXXX collections except
with CurrentProject.

An alternative that works with DAO is to look at the contents of the
Documents collections. I can look up the except syntax if you need
it.

 

[mbyerley]

A more reliable test if whether or not .Recordcount = 0, and it's
only one property that needs to be tested.

Really.... Can you provide a cite on that hypothesis?
I would like to see the condition whereupon the opening of a recordset
(prior to the cursor moving in either direction) can produce an EOF
condition.

I have never used the suggested:

not (.BOF and .EOF)

but I can't see how on RecordSet.open on an empty recordset can produce any
other result except EOF.

Further, I can't recall (doesn't mean it doesn't exist to be sure) that
RecordCount is ever (exactly) accurate without using MoveLast first...

 

[Douglas J Steele]

While you're correct that RecordCount isn't accurate without using MoveLast
first, if RecordCount reports 0, then there's nothing in the recordset.

"mbyerley" wrote in message

Further, I can't recall (doesn't mean it doesn't exist to be sure) that
RecordCount is ever (exactly) accurate without using MoveLast first...

 

[John W. Vinson]

Further, I can't recall (doesn't mean it doesn't exist to be sure) that
RecordCount is ever (exactly) accurate without using MoveLast first...

In my experience, .Recordcount will be 0 for an empty recordset; if the
recordset returns any records, it will be nonzero. Usually it will be 1
initially, i.e. if you open the recordset and check its recordcount in the
next line; the recordcount will increase as the query engine traverses the
recordset, but will only give a reliable recordcount if you do a .movelast.

If the question is "are there any records, or none?" then checking for zero
(or >0) immediately after opening should work, though.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[mbyerley]

In my experience, .Recordcount will be 0 for an empty recordset; if the
recordset returns any records, it will be nonzero. Usually it will be 1
initially, i.e. if you open the recordset and check its recordcount in the
next line; the recordcount will increase as the query engine traverses the

"the recordcount will increase"
That doesn't sound right. RecordCount would be as it's name implies,
which should be the number of records returned by the query, but during the
traversing of the RecordSet shouldn't change that number, excluding the
count error that could occur prior to the traversing of the entire
recordset.

I have only used Access since AC97, but my practice prior to traversing a
recordset was to use EOF and BOF as a negative condition before issuing a
MoveFirst. Your (and Doug's) point on an empty recordset having RecordCount
0 is valid, I can see that OK, but I will likely continue to use the same
practice to which I've found reliable for me as well.

 

[Stuart McCall]

"the recordcount will increase"
That doesn't sound right. RecordCount would be as it's name implies,
which should be the number of records returned by the query, but during
the traversing of the RecordSet shouldn't change that number, excluding
the count error that could occur prior to the traversing of the entire
recordset.

Recordcount does not mean what you think. From A2003 help:

"Use the RecordCount property to find out how many records in a Recordset or
TableDef object have been accessed. The RecordCount property doesn't
indicate how many records are contained in a dynaset-, snapshot-, or
forward-only–type Recordset object until all records have been accessed.
Once the last record has been accessed, the RecordCount property indicates
the total number of undeleted records in the Recordset or TableDef object."

 

[mbyerley]

Recordcount does not mean what you think. From A2003 help:

"Use the RecordCount property to find out how many records in a Recordset
or TableDef object have been accessed. The RecordCount property doesn't
indicate how many records are contained in a dynaset-, snapshot-, or
forward-only-type Recordset object until all records have been accessed.
Once the last record has been accessed, the RecordCount property indicates
the total number of undeleted records in the Recordset or TableDef
object."

That's different from ADO:

"Use the RecordCount property to find out how many records are in a
Recordset object."

I got crossed up on that one. I use ADO frequently in ASP. Thanks.

 

[David-W-Fenton]

Really.... Can you provide a cite on that hypothesis?
I would like to see the condition whereupon the opening of a
recordset (prior to the cursor moving in either direction) can
produce an EOF condition.

We know how Recordcount behaves. It never returns anything but 0 for
a recordset with no records.

But EOF and BOF can behave differently depending on circumstances.

I don't use them (except when walking a recordset, which I do only
very rarely), so don't have the specifics here, but I've seen enough
posts in the newsgroups over the years with problems to have
developed a policy of using Recordcount as the more accurate and
predictable property.

I have never used the suggested:

not (.BOF and .EOF)

but I can't see how on RecordSet.open on an empty recordset can
produce any other result except EOF.

There are interesting problems with BOF and EOF. I have seen reports
of problems in the newsgroups where they aren't exactly accurate.

Secondly, you're testing two properties instead of one. Testing one
is more efficient.

Further, I can't recall (doesn't mean it doesn't exist to be sure)
that RecordCount is ever (exactly) accurate without using
MoveLast first...

But you don't care what the actual number of records returned is --
you only care if there are 0 records. Recordcount never returns
anything other than 0 when there are 0 records.

 

[David-W-Fenton]

If the question is "are there any records, or none?" then checking
for zero (or >0) immediately after opening should work, though.

Not just SHOULD, it always does with a DAO recordset.

ADO is completely different, of course.

 

[David-W-Fenton]

Recordcount does not mean what you think. From A2003 help:

"Use the RecordCount property to find out how many records in a
Recordset or TableDef object have been accessed. The RecordCount
property doesn't indicate how many records are contained in a
dynaset-, snapshot-, or forward-only-type Recordset object until
all records have been accessed. Once the last record has been
accessed, the RecordCount property indicates the total number of
undeleted records in the Recordset or TableDef object."

This is because of Rushmore.

And it's why scrolling to the end of a large table or a long combo
box can take some time.

 

[David-W-Fenton]

That's different from ADO:

"Use the RecordCount property to find out how many records are in
a Recordset object."

I got crossed up on that one. I use ADO frequently in ASP.

ADO != DAO. The recordsets behave very differently.

 

[tina]

well, i have to wonder why your user doesn't just write her as hoc queries
and reports into a separate frontend. then your revised "official" frontend
can overwrite the obsolete version without issue.

hth
tina


Thank you very much.

This is part of a larger process I was writing. Here is a brief
explanation of my story. I'll probably repost this so, maybe you
don't need to read.

This db is a database that tracks sales information, the back end is
SQL, and the front end is plane jane access, 2002, and most of the
clients run the db with the stripped down runtime executable.

I have one user who makes changes to her front end of the sales
database. When I make changes to the database, Which I do all the
time since It's pretty much my database. Okay, when I release an
update, and she overwrites her old one, she loses all her queries and
Reports. She doesn't seem to create macros or tables.

Luckily, all of her objects are prefixed with the name "Cindy."

So, you can see how the isfound() fits in with all of this, I can't
import that report/query if it's already there or I'll get a 1.

Well, now the program is starting to barf because it can't get read
access to Msysobjects in a database that isn't the current one. I
did...

dim fDB as database

set fDb = "C:\accessxp\sales\salesold.mdb"

set rst = db.openrecordset("msysobjects")

and it gets upset right there when it tries to open the other
database's msysobjects saying it doesn't have read permission.

any help?

Thanks again Tina.

-Brian P.
Best Data Processing - Holiday, FL

you can't use the NoMatch property because you didn't use a Seek or Find
method on the open recordset. remember, you're already opening a filtered
recordset, so you only need to see if a record was returned. also, Exists
may be a reserved word, suggest you rename the procedure. try the
following,
as

Public Function isFound(ByVal objectName As String) As Boolean

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim str As String

str = "SELECT MSysObjects.Name FROM MSysObjects " _
& "WHERE MSysObjects.Name = '" & objectName & "'"

Set db = CurrentDb
Set rst = db.OpenRecordset(str, dbOpenDynaset)

isFound = Not (rst.BOF And rst.EOF)

rst.Close
Set rst = Nothing

End Function

when a recordset is empty, both BOF and EOF are True. in that case, the
object was not found, so we want to report False, so we "flip" the return
value using Not.

hth
tina

This is my attempt at an exists() function, I can't seem to find one
that will tell me if a given name, exists, as a table, or query, or
report. I Don't even need to search tables, but the only other
routines I have seen search tabledef anyway!! Here goes. (Sorry if
I've double-posted this, I can't find my first post!)

Public Function eXists(objectName As String) As Boolean
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
' Set rst = db.OpenRecordset("select * from [msysobjects] where
([name] = """ & objectName & ")")
Set rst = db.OpenRecordset("select * from [msysobjects] where
[msysobjects].[name] = """ & objectName & """")

' "cindyrpt - 336 mailing label Listing - all contacts"
If rst.NoMatch Then
eXists = False
Else
eXists = True
End If

End Function- Hide quoted text -

- Show quoted text -

 

[mbyerley]

ADO != DAO. The recordsets behave very differently.

I know David.. I use ADO in ASP and DAO on the desktop.. I should have
said so.

 

[Marshall Barton]

mbyerley wrote:
[snip]

Further, I can't recall (doesn't mean it doesn't exist to be sure) that
RecordCount is ever (exactly) accurate without using MoveLast first...

RecordCount is accurate with TableDef objects and Table type
recordsets. According to Help neither of those objects even
have a MoveLast method.

 

[David-W-Fenton]

mbyerley wrote:
[snip]

Further, I can't recall (doesn't mean it doesn't exist to be sure)
that RecordCount is ever (exactly) accurate without using
MoveLast first...

RecordCount is accurate with TableDef objects and Table type
recordsets. According to Help neither of those objects even
have a MoveLast method.

Actually, it's not accurate with linked tables, which is a type of
TableDef object. It's only accurate for those objects that can be
used as a table-type recordset (i.e., non-linked tables).

 

[mbyerley]

mbyerley wrote:
[snip]

Further, I can't recall (doesn't mean it doesn't exist to be sure) that
RecordCount is ever (exactly) accurate without using MoveLast first...

RecordCount is accurate with TableDef objects and Table type
recordsets. According to Help neither of those objects even
have a MoveLast method.

Pretty sure MoveLast is available on RecordSets devoid of a forward only
cursor......