Believe me, i wish I didn't have to do it.
The database is taking account transactions kept in FundEZ (a non profit
accounting program) and reallocating it based on progam needs so it has a 20
step append then delete process to move the compensation to the programs
based allocation. This is done on quarterly and yearly basis, and in the
future monthly. They then need to refered back to as the state or city send
in questions or audits. In the pass, they have been creating separate
databases each time full of totals queries and make table queries so you
don't know what the original data is. Unneccessary and time consumming
particularly when they don't have a lot of knowledge with Access. You would
not believe the condition of their network with all the saved db's and
spreadsheets duplicating each other. They definately fit your description in
your 2nd paragraph.
Anyway, i set it up so they can just store the ongoing data and pull it
according to the dates they need and run the code to allocate it. I had it
set up to archive the allocated table in the same db but after splitting it,
the backend was already 241 mgs after compacting with only 2007 data in it.
The 2 main tables are duplicates, one of the original and one of the
allocated. That table is 80 fields, mostly double number fields and currently
47, 000 records...and that is only 1 year, so the original table will
continue to grow as we add the compensation numbers every month.
I don't see anyway to do it but to archive the allocated table by the dates
it was run forin another database. I want them to just refer back to the
original allocation instead of taking it out to excel so they can save it,
minipulating it and storing with no one knowing exactly what has been done to
it. The lack of data integrity in the databases and excel files around here
is unbelievable.
If you have any other ideas, I am open to them!
Thanks
Terri
Jeff Boyce said:
Why? As in "why export data to archive it?"
I'm not asking out of idle curiosity, but because a lot of folks who cut
their "db teeth" on Excel believe they have to move a table of data around
(i.e., move copy a spreadsheet) to archive in Access.
Another approach, unless your DB is getting too big, is to use a field in
the table that holds [DateArchived], then modify your queries for forms and
reports to only display records that do NOT have a [DateArchived] value.
More info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
tgavin said:
To archive information, I am trying to export the table to an Archive
database. the code i am using - DoCmd.TransferDatabase acExport,
"Microsoft
Access", "\\...\Databases\Compensation\Compensation Archive.mdb", acTable,
"tblCompensation'& Format([Start], "yyyy mm-dd")& " To " & Format([End],
"mm-dd") -works. My problem is that the tables in the database are linked
tables from Compensation_be.mdb and therefore, it is exporting a link. i
need
to export the actual table.
Any help would be much appreciated.
Thanks,
Terri
