[AccessD] Which Is Faster - DAO or db.Execute?

Rocky Smolin at Beach Access Software rockysmolin at bchacc.com
Wed Oct 29 12:22:11 CDT 2008


Wow.  Who knew?

Thanks. 


Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
jim at therareshop.com
Sent: Wednesday, October 29, 2008 10:13 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Which Is Faster - DAO or db.Execute?

yup, like this-

Function FindPeriod()
Dim dbs As Database
Dim rstbase As Recordset
Dim strTable As String

strTable = "tblPeriods"
Set dbs = WhichDB(strTable)
Set rstbase = dbs.OpenRecordset(strTable, dbOpenTable) 'find record with
current period rstbase.Index = "fldStatus"
rstbase.Seek "=", "B"
intPeriodNo = rstbase.Fields("fldPeriodno") intMonth =
rstbase.Fields("fldMonth") intyear = rstbase.Fields("fld year")
rstbase.Close  Set rstbase = Nothing End Function

So you pas the name of any table in the target db and then use the standard
db.OpenRecordset on the table you want to 'Seek'?
 


Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com
www.bchacc.com
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
jim at therareshop.com
Sent: Wednesday, October 29, 2008 9:19 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Which Is Faster - DAO or db.Execute?

<The table is a linked table - so no Seek.>

Rocky,
Using the function below allows you to perform a seek on a linked table.
This is a nifty function from the microsoft website that I've been using for
years without a problem.

Set db = WhichDB(strTable)

Function WhichDB(strTableName As String) As Database Dim dbpath$,
SourceTable$, dbTest As Database

On Error GoTo whichDB_ERR
Set dbTest = DBEngine(0)(0)
dbpath = Mid(dbTest(strTableName).Connect, InStr(1,
dbTest(strTableName).Connect, "=") + 1) If dbpath = "" Then
    Set dbTest = CurrentDb()
Else
    Set dbTest = DBEngine(0).OpenDatabase(dbpath) End If Set WhichDB =
dbTest
whichDB_EXIT:

  Exit Function
  
whichDB_ERR:
  MsgBox Err.Description
  Resume whichDB_EXIT
End Function
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list