[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