etiquette/rules on posting

[DChitwood]

please don't consider this a "smart-a" question because
it's not---really! Why are some questions answered and
others not? Obviously there are some spam posts, but I've
had and seen legitimate questions go unanswered. Is there
a rule of thumb that the experts use to as to which
questions to answer? If you could give me an idea, then I
could make sure that all my bases were covered before I
posted or not post one that is out of this newsgroup's
purview.

Thanks!

 

[Kevin3NF]

My rule of thumb:

If it hasn't been answered, I look at it. If I don't know the answer, I
don't reply (or if the reply would take me an hour to type out).

So basically, I really only look at posts that have not been replied to yet.

Putting good solid detail and asking specific questions helps us out a lot
too

Hope that helps!

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[Kevin Sprinkel]

Most rules of etiquette boil down to the Golden Rule.

- Make it easy for potential respondants to identify your
problem. Place as specific a description in the Subject
heading as possible. Include a generic description of
your business process before including the details of your
table structures, code, etc.
- Direct your post to the appropriate newsgroup(s). If it
falls in multiple areas, include them in the Newsgroup:
field rather than posting them separately to each.
- Avoid spamming, profanity, or abusive language.

Newsgroup participants respond on a volunteer-only basis.
There is no explicit or implied obligation for a given
question to be answered. In such an environment, the more
clearly stated problem is more likely to get a response.

And be sure to thank anyone who assists you and let them
know if their suggestion helped you resolve the issue.

HTH
Kevin Sprinkel

 

[Cheryl Fischer]

You've asked a good question and I do not consider it a "smart-a**" request.

Here is a link which may answer it:
http://www.mvps.org/access/netiquette.htm.

 

[DChitwood]

Thanks for the tips on posting!

 

[John Vinson]

Is there
a rule of thumb that the experts use to as to which
questions to answer?

The rule of thumb that I use is that I'll only answer questions that I
see (due to the vagaries of Usenet propagation, I know that there are
questions posted which never make it to my server); that I can
understand (some questions have so little content or are phrased so
confusingly that I can't make a guess at what's needed); and to which
I know the answer (there are lots of areas of Access that I don't
know). Of course meaningful Subject lines (as opposed to "HELP" or "It
doesn't work") will give a post a leg up.

Also, like everyone else who answers here - this isn't my job. I am a
self-employed consultant donating (sometimes too much of) my time on
the newsgroup; it'll often happen that I can only rush through the
posts looking for unanswered messages that I can answer quickly, or
that I just have to Mark All Read and come back another day.

If you have posted what you consider to be a reasonable question and
it hasn't been answered, it's socially acceptable to repost with
REPOST: <same subject line> so the volunteers can give it special
attention.

 

[Tony Toews]

please don't consider this a "smart-a" question because
it's not---really! Why are some questions answered and
others not? Obviously there are some spam posts, but I've
had and seen legitimate questions go unanswered. Is there
a rule of thumb that the experts use to as to which
questions to answer?

I only reply to questions with subjects I find interesting. For
example If you ask about DLookup and DSum or Macros I've never used
them and consider them quite boring.

And your subject I found to be interestnig.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Fred Boer]

Dear Tony:

Interesting... you *never* use DLookup? What if you need to do a.. um..
lookup?

Fred Boer

 

[Van T. Dinh]

Fred

You can use a Recordset to do lookup if you want to. You only need to
create the Recordset then use Seek or one of the Find Methods to find the
required Record / row using the criteria you would use in DLookUp. You can
then get the value of the required (look-up) Field of the current row.

 

[Intriqued]

Can you elaborate or point me to some more information on this lookup explanation
I'm fairly new at this so the answer confused me but I may be able to use this....
thanks!

 

[Arvin Meyer [MVP]]

Build a recordset:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim x As Variant

Set db = CurrentDB
Set rst = db.OpenRecordset("Select Whatever From ...")

Me.txtControlName = rst!Whatever

Then close the recordset, etc. etc.

This should be much faster than DLookup because it uses SQL and indices
which is tons faster than hunting one row at a time for a value.
--

Arvin Meyer, MCP, MVP
Microsoft Access
Free Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Tony Toews]

Interesting... you *never* use DLookup? What if you need to do a.. um..
lookup?

I've always used strings passed to recordsets with the table/query
name and the where clause. Thus the recordset usually has one record.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

You can use a Recordset to do lookup if you want to. You only need to
create the Recordset then use Seek or one of the Find Methods to find the

I never use seek or find either.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[John Vinson]

This should be much faster than DLookup because it uses SQL and indices
which is tons faster than hunting one row at a time for a value.
--

Just FWIW, in my experience DLookUp will in fact take advantage of
indexes on its criteria fields.

 

[Fred Boer]

Hello!

Don't know if anyone will look this far back, but... I've been looking into
this thread and studying the use of a recordset vs. dlookup... I believe I
understand the idea of creating a recordset that contains one record, and I
understand that this is faster than DLookup. I took a look, and I don't use
DLookup in my application,. However, I *do* use the following as the control
source of a textbox on one of my forms to identify if a book is available
for borrowing, and it needs to be recalculated every time I browse to a new
record:

=IIf(DCount("*","Qry_Transaction","[Book_ID]=" &
[Form].[Book_ID])=0,"Yes","No")

1. Is DCount, since it is also an aggregate function, also subject to the
speed limitations of DLookup?
2. How could you, (or would you?) use a recordset to replace DCount in this
situation? Would you create a custom function containing the recordset code?

Thanks!
Fred Boer

 

[Douglas J. Steele]

All the aggregate functions suffer from the same limitations.

To write your own equivalent to DCount, you could use something like the
following untested air-code:

Function EDCount(FieldName As String, _
TableName As String,
Optional WhereClause As String = "") As Long

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim lngCount As Long
Dim strSQL As String

strSQL = "SELECT Count([" & FieldName & "]) " & _
"FROM [" & TableName & "] "
If Len(WhereClause) > 0 Then
strSQL = strSQL & " WHERE " & WhereClause
End If

Set dbCurr = CurrnentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
With rsCurr
If .EOF And .BOF Then
lngCount = 0
Else
lngCount = .Fields(0)
End If
End With

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

EDCount = lngCount

Exit Function


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hello!

Don't know if anyone will look this far back, but... I've been looking into
this thread and studying the use of a recordset vs. dlookup... I believe I
understand the idea of creating a recordset that contains one record, and I
understand that this is faster than DLookup. I took a look, and I don't use
DLookup in my application,. However, I *do* use the following as the control
source of a textbox on one of my forms to identify if a book is available
for borrowing, and it needs to be recalculated every time I browse to a new
record:

=IIf(DCount("*","Qry_Transaction","[Book_ID]=" &
[Form].[Book_ID])=0,"Yes","No")

1. Is DCount, since it is also an aggregate function, also subject to the
speed limitations of DLookup?
2. How could you, (or would you?) use a recordset to replace DCount in this
situation? Would you create a custom function containing the recordset cod e?

Thanks!
Fred Boer

Just FWIW, in my experience DLookUp will in fact take advantage of
indexes on its criteria fields.

 

[John Vinson]

1. Is DCount, since it is also an aggregate function, also subject to the
speed limitations of DLookup?

Even more so, since it must traverse the entire recordset and count
every record. DLookUp quits when it finds the first record.

2. How could you, (or would you?) use a recordset to replace DCount in this
situation? Would you create a custom function containing the recordset code?

You *could* open a recordset, use the MoveLast method to make sure
it's been populated, and return the RecordCount - but IME Dcount is
quite reasonable. The only times that the domain functions have given
me an unacceptable performance hit is when I'm doing a DCount or a
DLookUp in *every record* of a large query. One by one they're not
bad, it's when you try to run a thousand DCounts or DSums at the same
time that it hurts!

 

[Rick Brandt]

Even more so, since it must traverse the entire recordset and count
every record. DLookUp quits when it finds the first record.
code?

You *could* open a recordset, use the MoveLast method to make sure
it's been populated, and return the RecordCount - but IME Dcount is
quite reasonable. The only times that the domain functions have given
me an unacceptable performance hit is when I'm doing a DCount or a
DLookUp in *every record* of a large query. One by one they're not
bad, it's when you try to run a thousand DCounts or DSums at the same
time that it hurts!

Could you please cite a reference that indicates that the domain aggregates
use table scans instead of SQL to produce their results? This is posted
often enough to have become "common knowledge" but I have not seen where
the original idea stems from.

Because they instantiate a db object I agree that the domain functions
would perform less than optimally in a query or loop situation where they
will be called multiple times in a short time span, but I have never seen
anything to suggest that when looking up a single value in a non-repetitive
situation that a RecordSet will be any faster than a DA function.

There was actually an article in the VB/Access/SQL Server Advisor magazine
a few years ago where they compared DLookup, Seek, FindFirst, and
RecordSets and DLookup actually came in second place only losing out to
Seek when used against a local indexed table.

Is it possible that this was true in an early version of Access, was
subsequently changed in a later version, but the perception has stuck
around?

 

[John Vinson]

Could you please cite a reference that indicates that the domain aggregates
use table scans instead of SQL to produce their results? This is posted
often enough to have become "common knowledge" but I have not seen where
the original idea stems from.

I don't think they DO use table scans. I haven't got a cite or hard
evidence, but I've used DLookUps on very large tables in circumstances
where they get called once, and get instant response - faster than a
scan could be run. IMO they'll use indexes just as well as a Query.

 

[Fred Boer]

Thank you to both of you! Just for fun, I'll try working with your code,
Doug! And John, after reading your response, I have a better idea of how
DLookup could really bog down a query.

Thanks again,
Fred