TransferDatabase help...totally lost

[zmenloans]

Hi,

First off, any help that you can offer with this problem would be greatly
appreciated. It does need to be done (if possible). I am relatively new to
Access and can hardly understand coding, so I was hoping for some help.

I read in other posts about a TransferDatabase macro thing, but I don't
quite understand it and I'm not sure if it is what I'm looking for.

What I need to do is transfer records from one table in one database to a
table in another database. I need to do this because the bank keeps records
of Charge Offs and Paid in Full accounts, but does not work them anymore so
does not need them on the current database. They only need it in another
database for archive purposes. I have 10 databases with 10 identical
structured tables all being worked by different people. What I want is at
the end of each month, the 10 employees can click a button that will transfer
any records that contain "Charge Off" in the column titled "ColorCode" to a
different database (called Archive) into a table called "ChargeOffHistory".
When they click that button at the end of the month, it will also move the
"PIF" records from the "ColorCode" column to the Archive database into a
table called "PIFHistory." This will also remove those records from the
previous table. The ChargeOffHistory and PIFHistory tables will be
structured identically to the other tables.

In summation,
-10 Databases (identically structured tables in each database) [Let's call
them Database1, Database2...Database10]
-Column called ColorCode with various status
-At then end of each month, each employee of the 10 databases will click a
button to move the records that contain "Charge Off" in ColorCode and "PIF"
in ColorCode to the ChargeOffHistory table and PIFHistory table in the
Archive Database, respectively
-When the records are moved to the Archive Database, they are also removed
from each of their personal databases

Is this possible? And to answer your questions ahead of time, the
information does NEED to be moved. It can't be in the table and then hidden
using some query. For audit purposes, the entire record has to be moved to
another database, and I figured there was an easier way than copy and paste.

Again, I would apprecaite any input. I am new to access, so if there is any
code or creation of something involved, please try to let me know where is
should go and how I get to the certain points.

Thank you so much!

 

[Fred]

I'm presuming that there's also a good reason for not combining the the 10
databases into one, with a field to seperate the 10 "subsets".

The low tech Fred way would be to:

- use Get External Data -> Link Tables to put a link to the archive table
into the db.
- Make an append query to copy the desired records into the archive table
- Make a delete query that deletes those records
- Make a Macro which runs the above queries in sequence.

 

[zmenloans]

Fred,
Thank you so much for your response. Yeah I can't really combine the 10
databases because everyone works on them at once and it would just get to be
a mess.

I understand what it is you suggest, but I just don't know how to go about
doing it. I'm not sure how to use or write Macros, so could you just expand
on this a bit futhur?

Again, thanks.

 

[Fred]

BTW, folks who can tell the OP a better than my idea, please do. People
who are way smarter than me answer these posts.

BTW Access can handle 10 users ate once.


Once the queries are written, hit the "Macro" icon and "new". On the first
line select "open query" and in the dialog box at the bottom, choose your
append query. Repeat this process on the second line and choose your delete
query. The exit, and it will prompt you for a name for the Macro. Then
you're done.

 

[zmenloans]

Thank you so much for your help, Fred. I have another question. You said
Access can handle 10 users at once, but when I try to open one of the
databases that another using is currently on, it says that it is locked and
cannot be open due to the other user. Is there a way to configure the
database so that 10 users can use it at once?

 

[Fred]

Hello zmenloans

The overview is that you "split" the database into a back end and front end,
and give everybody their own copy of the front end.

There's a splitter wizard in Access that does most of the job.

Sincerely,

Fred