By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,087 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,087 IT Pros & Developers. It's quick & easy.

how can I speed up this function with a dcount in it?

P: n/a
Hi everyone,

I hope I will not get banned for asking toooo many questions. I am just
really excited to have this platform ;-)

I have the following function:
Function IsProperty(pptname) as boolean

-------------------
If DCount("[ID]", "[T-TCP]", "[T-TCP]![D]='" & pptName & "'") > 0 Then
IsProperty = True
Else
IsProperty= False
end if
End function
-------------------

Thereby T-TCP is a table with a list of properties (about 100). How can I
speed up this function?
Several functions in my dbs call this function many times, while formatting
forms.

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004
Nov 13 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
Try this:
Function IsProperty(pptname) as Boolean
dim db as Dao.database
dim rst as Dao.recordset
dim strSQL as string
set db=currentdb
strSQL = "SELECT Count([ID]) AS Properties FROM T-TCP " _
& "WHERE [D] = """ & pptname & """;"
set rst = db.openrecordset(strSQL)
With rst
If !Properties > 0 Then
IsProperty = True
Else
IsProperty = False
End If
End With
On Error Resume Next
rst.close
set rst = nothing
set db = nothing
End Function

Stewart

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:0J******************@news.xtra.co.nz...
Hi everyone,

I hope I will not get banned for asking toooo many questions. I am just
really excited to have this platform ;-)

I have the following function:
Function IsProperty(pptname) as boolean

-------------------
If DCount("[ID]", "[T-TCP]", "[T-TCP]![D]='" & pptName & "'") > 0 Then
IsProperty = True
Else
IsProperty= False
end if
End function
-------------------

Thereby T-TCP is a table with a list of properties (about 100). How can I
speed up this function?
Several functions in my dbs call this function many times, while formatting forms.

---
Please immediately let us know (by phone or return email) if (a) this email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

Nov 13 '05 #2

P: n/a
I will try that... Thank you once more.

"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:ca**********@news.wave.co.nz...
Try this:
Function IsProperty(pptname) as Boolean
dim db as Dao.database
dim rst as Dao.recordset
dim strSQL as string
set db=currentdb
strSQL = "SELECT Count([ID]) AS Properties FROM T-TCP " _
& "WHERE [D] = """ & pptname & """;"
set rst = db.openrecordset(strSQL)
With rst
If !Properties > 0 Then
IsProperty = True
Else
IsProperty = False
End If
End With
On Error Resume Next
rst.close
set rst = nothing
set db = nothing
End Function

Stewart

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:0J******************@news.xtra.co.nz...
Hi everyone,

I hope I will not get banned for asking toooo many questions. I am just
really excited to have this platform ;-)

I have the following function:
Function IsProperty(pptname) as boolean

-------------------
If DCount("[ID]", "[T-TCP]", "[T-TCP]![D]='" & pptName & "'") > 0 Then
IsProperty = True
Else
IsProperty= False
end if
End function
-------------------

Thereby T-TCP is a table with a list of properties (about 100). How can I speed up this function?
Several functions in my dbs call this function many times, while

formatting
forms.

---
Please immediately let us know (by phone or return email) if (a) this

email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004


---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004
Nov 13 '05 #3

P: n/a
rkc

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:0J******************@news.xtra.co.nz...
Hi everyone,

I hope I will not get banned for asking toooo many questions. I am just
really excited to have this platform ;-)

I have the following function:
Function IsProperty(pptname) as boolean

-------------------
If DCount("[ID]", "[T-TCP]", "[T-TCP]![D]='" & pptName & "'") > 0 Then
IsProperty = True
Else
IsProperty= False
end if
End function
-------------------

Thereby T-TCP is a table with a list of properties (about 100). How can I
speed up this function?


First off, it looks like you should be using DLookup instead of DCount
since you don't really want to count the number of times the value exists
just if it exists at all.

Second if you explain why you're using the function to format a form, there
may be a better way to get where you're going. Maybe return all the single
lookups in a recordset and use FindFirst.

Nov 13 '05 #4

P: n/a
It is a long story what I use it for and there is no way that it can be done
differently without a lot of work.

I use Dcount, because it is a lot faster then dlookup.

All I want to know if it is one of the properties that 'matter' for
formatting the form.

- Nicolaas
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.700 / Virus Database: 457 - Release Date: 06/06/2004
Nov 13 '05 #5

P: n/a
In theory, DCount should be faster than DLookup, because DCount looks
through the entire table/query, while DLookup stops when it finds something.

It amazes me, however, that you feel you have a perfomance problem with
either one of those with only 100 records in the table.

Do you have indexes on both [ID] and [D]?

- Turtle

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:np******************@news.xtra.co.nz...
It is a long story what I use it for and there is no way that it can be done differently without a lot of work.

I use Dcount, because it is a lot faster then dlookup.

All I want to know if it is one of the properties that 'matter' for
formatting the form.

- Nicolaas
---
Please immediately let us know (by phone or return email) if (a) this email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.700 / Virus Database: 457 - Release Date: 06/06/2004

Nov 13 '05 #6

P: n/a
I will chekc out the indexes. It is more than the function is called many
many times, lets say 100 times per control x 10 controls x 10 forms,
something like that.
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.700 / Virus Database: 457 - Release Date: 06/06/2004
Nov 13 '05 #7

P: n/a
"WindAndWaves" <ac****@ngaru.com> wrote in
news:np******************@news.xtra.co.nz:
I use Dcount, because it is a lot faster then dlookup.


I use Trevor Best's t-lookup functions, which were designed to work
quickly on linked tables (which used to be problematic, though that
may have been fixed in recent versions of Access).

You can find these functions on the Access Web.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #8

P: n/a
In that case, you might want to open a recordset and leave it open.
FindFirst may be fast enough; Seek is even faster.
What will take time is opening and closing the recordset, so you'll want to
avoid doing that repeatedly.

HTH
- Turtle

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:PM******************@news.xtra.co.nz...
I will chekc out the indexes. It is more than the function is called many
many times, lets say 100 times per control x 10 controls x 10 forms,
something like that.
---
Please immediately let us know (by phone or return email) if (a) this email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.700 / Virus Database: 457 - Release Date: 06/06/2004

Nov 13 '05 #9

P: n/a
MacDermott wrote:
FindFirst may be fast enough; Seek is even faster.


You have a talent for understatement :-)

FindFirst is an old man on a bicycle, by comparison Seek is a Saturn V
rocket.

If the table is linked you will have to open the back end database
through code and base a recordset on that as it doesn't work on linked
tables.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 13 '05 #10

P: n/a
Trevor Best <nospam@localhost> wrote in
news:40***********************@auth.uk.news.easyne t.net:
MacDermott wrote:
FindFirst may be fast enough; Seek is even faster.
You have a talent for understatement :-)

FindFirst is an old man on a bicycle, by comparison Seek is a
Saturn V rocket.


But if your recordset includes more than one table, Seek won't be
available to you.
If the table is linked you will have to open the back end database
through code and base a recordset on that as it doesn't work on
linked tables.


That's trivial, really.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #11

P: n/a
David W. Fenton wrote:
Trevor Best <nospam@localhost> wrote in
news:40***********************@auth.uk.news.easyne t.net:

MacDermott wrote:

FindFirst may be fast enough; Seek is even faster.


You have a talent for understatement :-)

FindFirst is an old man on a bicycle, by comparison Seek is a
Saturn V rocket.

But if your recordset includes more than one table, Seek won't be
available to you.


Yes, Seek works on table type recordsets only, it is the _fastest_ way
to find a record within a recordset but is not widely used due to the
following:

1) It does require an index, if the column you are searching is not
indexed, you cannot use it.

2) It's not portable, can only be used on Jet tables (AFAIK)

If the table is linked you will have to open the back end database
through code and base a recordset on that as it doesn't work on
linked tables.

That's trivial, really.

But relevant all the same.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 13 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.