If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Exporting a table to another database
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 |
#2
|
|||
|
|||
Exporting a table to another database
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" wrote in message ... 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 |
#3
|
|||
|
|||
Exporting a table to another database
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" wrote: 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" wrote in message ... 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 |
#4
|
|||
|
|||
Exporting a table to another database
Consider opening Archive.mdb programatically and importing/copying the table
directly from the be file (or vice versa). I'm not saying there isn't a more elegant solution, just that bypassing the fe is the first one that comes to mind since you only have a link to work with and no "make local" options via vba. -- HTH, George "tgavin" wrote in message ... 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 |
#5
|
|||
|
|||
Exporting a table to another database
I had thought of that but I could not find any that worked that allow me to
open the archive and run the import from the regular. But I will admit to no formal training, i have learned my code from Help, books and banging my head against a wall. if you have some to share, i would appreciate it. Terri "George Nicholson" wrote: Consider opening Archive.mdb programatically and importing/copying the table directly from the be file (or vice versa). I'm not saying there isn't a more elegant solution, just that bypassing the fe is the first one that comes to mind since you only have a link to work with and no "make local" options via vba. -- HTH, George "tgavin" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|