Jacob,
I commend you for your tenacity in developing this. I'm sure it was quite
a task putting all the formulas together to build this database. Someday,
I'd like to take a look at the way you did it.
The answer to your question became clear in the very last sentence of
your post. As soon as you used the word "DLookup" I knew that you could
probably increase the speed drastically. Here's some code I wrote to
replace DLookup almost 10 years ago, and updated last year. The idea was
given to me by Trevor Best who wrote a bunch of lookup replacement
functions for Access 2.0. This function is pretty generic, so you can use
it a lot.:
Public Function FastLookup(strFieldName As String, strTableName As
String, strWhere As String) As Variant
'*******************************************************************************************************
' Name: FastLookup ' Purpose: Fast replacement for DLookup ' Inputs:
strFieldName As String ' strTableName As String ' strWhere As
String ' Returns: Variant ' Author: Arvin Meyer ' Date: April 9, 1997 '
Updated: June 15, 2005 ' Usage: ' If FastLookup("FieldName", "TableName",
"FieldName ='" &Me.txtControlName & "'") = Me.txtControlName Then '
MsgBox "This value exists. Please choose again", vbOKOnly,"Duplicate!" '
Me.txtControlName.SetFocus ' Exit Sub ' End If '
'***************************************************************************************************************On
Error GoTo Error_HandlerDim db As DAO.DatabaseDim rst As DAO.RecordsetDim
Temp As VariantSet db = CurrentDb If strWhere = "" Then Set rst
= db.OpenRecordset("Select [" & strFieldName & "] From [" &strTableName &
"]", dbOpenSnapshot) Else Set rst = db.OpenRecordset("Select ["
& strFieldName & "] From [" &strTableName & "] Where " & strWhere,
dbOpenSnapshot) End If If Not rst.BOF Then rst.MoveFirst
Temp = rst(0) Else Temp = Null End If rst.Close
FastLookup = TempExit_Here: On Error Resume Next rst.Close Set
rst = Nothing Set db = Nothing Exit FunctionError_Handler:
MsgBox Err.Number & ": " & Err.Description Resume Exit_HereEnd
Function--Arvin Meyer, MCP,
MVPhttp://
www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com"JK"
messageArvin,> (Other
please feel free to replay)>> Whilst on the subject, I wander if I can
increase speed:>> I have a table that maintains holidays rules/date
around the world, 800records so far and growing. There are other
"supporting" tables needed, butlet leave it out for now.>>
Background:> -------------> Each holiday has its own rule. Some of the
rules are simple (eg 25 Dec, or> 4th Thursday in Nov), others are more
involved (eg. if an holiday falls ona> weekend, Move it to Monday). Yet
others depend on another holiday (eg. 46> days after Easter Sunday).>> In
addition, each rule has its own calendar (Gregorian, Hebrew, Islamic
sofar) with or without a cross-over, e.g Easter Sunday falls on the
FirstSunday after the Jewish Passover (with exceptions)>> I have the
relevant routines to calculate the fields "ThisYearDate"
and"NextHolidayDate" (not always the same, "US presidential Election" has
Nullvalue in isYearDate" (2006) but has a value in "NextHolidayDate", 4
Nov,2008), *NO PROBLEM* thus far.>> Each time a user enter a record,
through the OnCurrent event, those twodates get updated through a
combination of a Private and a Public Functions.The same happens, through
AfterUpdate event of any field that affects therule (Only Admin can do
that), still no problem there.>> Regardless of the calendar used to
calculate the holiday date, *both*"ThisYearDate" and "NextHolidayDate"
return the date in Gregorian calendar,eg. Jewish New Year which is the
first day of the year in the Hebrewcalendar will return 23 Sep 2006 (this
year) and 13 Sept 2007 (next date)>> The Problem> --------------> Once a
year, soon after new year day, I run an update query to update allthe
dates in the table. That query runs for about *15 minutes * with
800records, not surprising under the circumstances described above.>> The
Question (finally)> ----------------------> Having written those a few
years back when I did not understand whatRecordset or DAO means, I wander
whether I should modify the routine for ayearly update to reduce the time
of a full update. I am in two minds:>> 1. If ain't broken, don't fix it>
2. If I can speed up the operation dramatically using DAO, I can
allowusers to do a general update any time.>> Thus far all the
calculations are done by whatever DLookup()'s returns.Bearing in mind
that if a holiday date is linked to another holiday, thatother holiday
has to be calculated first.>> Appreciated your comments>> Regards>
If you disambiguate, both
can be used. IMO, it would be DAO for most of>> the work. ADO is designed
to be faster with SQL-Server and for the most>> part it is. It does not
have all the functionality of DAO, but will dothe>> majority of what you
may need to do. ADO is not being developed any more>> in favor of
ADO.NET. The 2 are not alike. That alone would put a damperon>> my
deployment plans.>> -->> Arvin Meyer, MCP, MVP>>
http://www.datastrat.com>>
http://www.mvps.org/access>>
http://www.accessmvp.com>>>> "Fred Wilson" <
[email protected]>
wrote in message>>
Hello all,>>>>>>
I have an MS Access 2003 database that I use as a tool to manage some>>>
reports and data dumps from an oracle.>>>>>> Anyway, which is the better
records set to use, ADO or DAO and why?>>>>>> Thanks,>>> Fred>>>>>>>
