connect two access databases

[CherylDetrick]

Hi:

I've got two access databases - one for members, one for prospects. I've
got a Yes/No field in the prospect database. I want to move the prospect
from the prospect db to the member db when the Yes/No is marked "yes."

How do I/ Can I do that?

Thanks so much!!!!!!

 

[John Vinson]

Hi:

I've got two access databases - one for members, one for prospects. I've
got a Yes/No field in the prospect database. I want to move the prospect
from the prospect db to the member db when the Yes/No is marked "yes."

How do I/ Can I do that?

Thanks so much!!!!!!

I'd really suggest a MUCH simpler approach: *one* database, with *one*
table for both prospects and members.

A Query selecting records with the yes/no Member field = False will
show you all prospects; a Query with a criterion of True will select
all members; setting the checkbox True will move the person from being
a prospect to being a member.

Two separate *DATABASES* - different .mdb files - is almost certainly
neither necessary nor beneficial. If you want two separate tables in
the one database, run an Append query appending the selected record
based on its unique ID.

John W. Vinson[MVP]

 

[Allen Browne]

Cheryl, I assume you mean you have 2 tables in your MDB database (not 2
separate MDB files.)

The simplest solution will be to combine the 2 tables into one, with a field
that tells whether this entry is a member or a prospect. You can then create
a query to give you the members, and another query to give you the
prospects, and use these queries anywhere you could use your tables. Saves
all the hassle.

If you want to move records between the 2 tables anyway, a move consists of
executing an Append query followed by a Delete query. You will want to do
that inside a transaction, so you get an all-or nothing result. For details
of how to go about that, see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
Note that if you have any related records that also need reassigning, that
will need to be done inside the transaction also.