DAO Recordset efficiency - linked table or direct to split MDB?

[Guest]

I have a split database, Program.mdb (where my code and queries reside) and
Tables.mdb (where the data tables live). To avoid risk of corruption and
ease of build, I don't want to have the data tables in the same mdb as my
code (though its probably faster if I did).

Are there efficiency gains if I open the recordset directly from Tables.mdb
rather than use the linked tables I've set up in Program.mdb (which is how I
currently have it set up)?

My code currently uses DAO to append records to a linked table:

dim rs as dao.recordset

'TableName is a linked table in Program.mdb that is linked to a table in
Tables.mdb
set db = CurrentDb
set rs = db.openrecordset("TableName", dbOpenDynaset, dbDenyWrite)
' Getdataforappends
' DoTheAppends

rs.close

OR should I change the set db statement to

set db = CurrentProject.Path & "\" & "Tables.mdb"


I'd probably prefer the last method, because then I can play with the tables
and fields directly, but I don't want to slow down the performance
significantly. That's why I'm asking if anyone knows which is "better".

BTW - if anyone is interested (I was) -
I compared the current append method (for Access tables):
using DAO rs.addnew and rs.update on each individual record
vs
using ADO rs.addnew ArrayOfFieldNames ArrayOfFieldValues and then
rs.adFilterPendingRecords rs.updatebatch after ALL records had been added (

20K records)

and DAO won hands down (by a factor of 3 - 5x) - even though DAO updates
each record individually (vs ADO's batch method). Just thought I'd throw
that in, because I was trying to find the answer to this before I coded it.

Thanks for any suggestions re linked tables.

 

[Douglas J. Steele]

If you're going to be using VBA either way, I wouldn't expect to see any
appreciable difference.

For what it's worth, you cannot use

set db = CurrentProject.Path & "\" & "Tables.mdb"

you'd have to use

set db = OpenDatabase(CurrentProject.Path & "\" & "Tables.mdb")

 

[Guest]

Thanks - you're right, I was mixing ADO and DAO in the set db line in my
question.

I guess if there is no difference, I'll go with setting the db to the
Tables.mdb, which will allow me (I think) to use dbOpenTable.