A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Exporting a table to another database



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2008, 05:58 PM posted to microsoft.public.access.forms
tgavin
external usenet poster
 
Posts: 57
Default 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  
Old April 28th, 2008, 06:27 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old April 28th, 2008, 07:19 PM posted to microsoft.public.access.forms
tgavin
external usenet poster
 
Posts: 57
Default 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  
Old April 29th, 2008, 05:21 PM posted to microsoft.public.access.forms
George Nicholson
external usenet poster
 
Posts: 791
Default 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  
Old April 29th, 2008, 06:39 PM posted to microsoft.public.access.forms
tgavin
external usenet poster
 
Posts: 57
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.