johnL said:
Oh No! I have loads of examples of the Seek Method around
the application I have just split!
Yeah, that is a surprise to the unwary. Some people refuse
to use Seek just for this reason (kinda wimpy, but
understandable). Personally, I split my dbs right at the
beginning of development just so I won't be surprised.
What do you mean by "use code to open back end database"
can you give an eg of the code? Would I do this on each
occasion I use the Seek Method and do you mean just the
table I am wanting to search?
Dim dbFE As Database
Dim dbBE As Database
Dim tdf As TableDef
Dim rec As Recordset
Set dbFE = CurrentDb
Set tdf = DbFE.TableDefs("alinkedtable")
Set dbBE = OpenDatabase(Mid(tdf.Connect, 11))
Set rec = dbBE.OpenRecordset(tdf.SourceTableName, _
dbOpenTable)
rec.Index = "Primary Key"
which can be simplified if you set a global variable to path
for the back end db and you always name the FE linked table
the same as its BE source table. You could even open the BE
database once and make dbBE a global variable. With all
those in place, the code would simply be:
Set rec = dbBE.OpenRecordset"tablename", dbOpenTable)
rec.Index = "Primary Key"
This problem would have been a little more obvious if you
had followed good coding practices and always specify the
type of the recordset. dbOpenTable is different from
dbOpenDynaset, especially in the use of Seek vs. FindFirst.
Would this approach be quicker (both in terms of
programing time and eventual application speed) than re-
writing to use the Find Method on each occasion.
I think so, but like Van said, probably not enough to
matter.
Some of the tables that I search will rarely have their
data changed, so I suppose I could keep them in the front
end (not preferred) or pull those table over each time I
open the F.E. Would I use a make table routine to do that?
If speed is a primary concern, then it would help, but I
would never advocate that just to save a little programming
time. Besides it's easier to modify the code to open the BE
db and use seek or to convert a Seek to a FindFirst than to
write all the code to manage duplicating tables, the
resulting bloat, etc.
Finally (!) are there any other important methods that
won't work now that I've split the database!
The most common difference is the RecordCount property. In
dbDynaset recordsets it returns the number of records that
have been accessed so far. So, If you need to know how many
records are in the recordset, then you need to use
rec.MoveLast before using rec.RecordCount.
