G
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 (
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.
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.