Move record from one table to another

I am trying to move a record from one linked table to another within
access, but I'm a complete beginner to VBA and don't know exactly where
to begin.
>
I have an access file that has the two linked tables, which each link
to separate SQL databases using separate ODBC connections.
>
I have a form that has a view of table1 and a set of buttons for
manipulating the data in database1.
>
The tough part is the button that's going to go on this form which
moves the record that's currently being viewed into a new record in
table2 (database2).
>
Through extensive testing I thought I found it's impossible to simply
do this with the built-in form builder tools - it only works if one of
the tables are local and not linked. However, queries which manipulate
data between linked tables work fine, which is what gave me the notion
I could do this with a little VBA code and a couple custom queries.
>
So far, the plan is the make the button do this:
1) Set a "pushFlag" column in the record in table1 to a specific
identifying value ("1" for now)
2) Runs an insert query to insert "all records with pushFlag = 1"
into table2. (this would create a copy of the record)
3) Sets the "status" field in the new record in table2 to a specific
value. ("done" for now)
4) Runs a delete query that first checks for duplicates (records that
exist in both the table1 and table2) and if a duplicate is found the
table1 record is removed. If a duplicate is not found, then the insert
must have failed for some reason. Instead of deleting the record the
user is notified that the insert failed, probably because of mal-formed
data in the record.
>
Does this sound remotely feasible? Would anyone be willing to help me
get this to work?

A better question is; Why? Why move a record from one table to
another? If you're trying to differentiate between one type of record
and another, why not just add a flag to the table?
>
What you're asking for is fairly easy to do, but moving records from
table to table isn't a very logical or stable way to track data.
>
I think rather than ask someone help you commit suicide, you should ask
someone for some guidance. Building a database isn't like building
with Legos, not everyone fully understands the concepts needed to build
a database properly. If they did, true DBAs wouldn't be making
$80K/yr.

The two databases which are on different computers are already in
place.
>
It's not just a move from one table to another within the same
database, it's a move from one machine to another. Access is simply
the 'bridge' between these two databases.
>
It's a very complicated setup, but if this intermediate step is
relatively easy in Access then it would make our work here very easy.
>
>
ManningFan wrote:

Dim rec, rec2 as recordset
Dim db As Database
>
Set db = CurrentDb
Set rec = db.OpenRecordset("FirstTable")
Set rec2 = db.OpenRecordset("SecondTable")
>
You can then check, add and edit data using the proper calls
i.e.
>
rec2.addnew
rec2("field1") = "Done"
rec2.update
>
You can probably set up a query in advance that finds dupes (there's a
wizard that will do this) and then open the query just like you opened
the tables above, and delete the records.
>

Thanks for the prompt response.
>
What exaclty in your code makes it so that it knows WHAT record it's
working on? I assume a record set is collection of records (like a
table), so how does access know I only want to move the record that's
currently being viewed? This is all in the context of the form viewing
table1, right?
>
Did you mean "Done" litterally?
>
>
>
ManningFan wrote:

I am trying to move a record from one linked table to another within
access, but I'm a complete beginner to VBA and don't know exactly where
to begin.
>
I have an access file that has the two linked tables, which each link
to separate SQL databases using separate ODBC connections.
>
I have a form that has a view of table1 and a set of buttons for
manipulating the data in database1.
>
The tough part is the button that's going to go on this form which
moves the record that's currently being viewed into a new record in
table2 (database2).

Through extensive testing I thought I found it's impossible to simply
do this with the built-in form builder tools - it only works if one of
the tables are local and not linked. However, queries which manipulate
data between linked tables work fine, which is what gave me the notion
I could do this with a little VBA code and a couple custom queries.
>
So far, the plan is the make the button do this:
1) Set a "pushFlag" column in the record in table1 to a specific
identifying value ("1" for now)

2) Runs an insert query to insert "all records with pushFlag = 1"
into table2. (this would create a copy of the record)

3) Sets the "status" field in the new record in table2 to a specific
value. ("done" for now)

4) Runs a delete query that first checks for duplicates (records that
exist in both the table1 and table2) and if a duplicate is found the
table1 record is removed. If a duplicate is not found, then the insert
must have failed for some reason. Instead of deleting the record the
user is notified that the insert failed, probably because of mal-formed
data in the record.

>
Does this sound remotely feasible? Would anyone be willing to help me
get this to work?
>

Joshua.Buss@gmail.com wrote:>
You can do this.
>>
This is all untested.

>
CurrentDb.Execute "UPDATE TABLE1 SET PUSHFLAG = 1 WHERE ID = " & Me.ID &
";"
>>
Table2 will need the PUSHFLAG column.
>
CurrentDb.Execute "INSERT INTO TABLE2 IN '<PATH>' FROM TABLE1 WHERE
PUSHFLAG = 1;"
>>
CurrentDb.Execute "UPDATE TABLE2 SET STATUS = 'DONE' WHERE PUSHFLAG = 1;"
>>
Why not just scan Table2 for "done"?
>
Either way, you will need a key that is /not autonumber/ to identify
matching records. Let's assume ID is the key in both tables, but is not
autonumber.
>
CurrentDb.Execute "DELETE FROM TABLE1 WHERE ID IN (SELECT ID FROM TABLE2
WHERE STATUS = 'DONE';"

CurrentDb.Execute "DELETE FROM TABLE1 WHERE ID IN (SELECT ID FROM

>
I didn't work in the user notification piece.
>>
>
>

Joshua.Buss@gmail.com wrote:>
You can do this.
>>
This is all untested.

>
CurrentDb.Execute "UPDATE TABLE1 SET PUSHFLAG = 1 WHERE ID = " & Me.ID &
";"
>>
Table2 will need the PUSHFLAG column.
>
CurrentDb.Execute "INSERT INTO TABLE2 IN '<PATH>' FROM TABLE1 WHERE
PUSHFLAG = 1;"
>>
CurrentDb.Execute "UPDATE TABLE2 SET STATUS = 'DONE' WHERE PUSHFLAG = 1;"

>>
Why not just scan Table2 for "done"?
>
Either way, you will need a key that is /not autonumber/ to identify
matching records. Let's assume ID is the key in both tables, but is not
autonumber.
>
CurrentDb.Execute "DELETE FROM TABLE1 WHERE ID IN (SELECT ID FROM TABLE2
WHERE STATUS = 'DONE';"

>
I didn't work in the user notification piece.
>>
>
>