Move record from one table to another

[Joshua.Buss]

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?

[ManningFan]

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.

Jo*********@gmail.com 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]

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:

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.

[ManningFan]

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.
[e-mail] wrote:

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:

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.

[Joshua.Buss]

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:

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.

[pietlinden]

This is a response to a very similar question...

http://groups.google.com/group/comp....cf92573e6b84f7

watch the wrap... and that should work for you.

You just specify the database you want to append to in the SQL
statement.

[ManningFan]

You would specify that in a WHERE clause. So the code would be
something like:
set rec = db.openrecordset ("Select * from Table1 where ID = " &
me.TableID & "")

Whatever field you're keying off of would be in the WHERE clause.

Jo*********@gmail.com wrote:

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:

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.

[Smartin]

Jo*********@gmail.com 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).

You can do this.

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)

This is all untested. In the button click event try the following:

CurrentDb.Execute "UPDATE TABLE1 SET PUSHFLAG = 1 WHERE ID = " & Me.ID & ";"

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

Table2 will need the PUSHFLAG column.

CurrentDb.Execute "INSERT INTO TABLE2 IN '<PATH>' FROM TABLE1 WHERE
PUSHFLAG = 1;"

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

CurrentDb.Execute "UPDATE TABLE2 SET STATUS = 'DONE' WHERE PUSHFLAG = 1;"

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.

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.

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

--
Smartin

[Smartin]

Smartin wrote:

Jo*********@gmail.com 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).

You can do this.

>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)

This is all untested.

....as evidenced by the fact that I committed a glaring error or two (^:
(see #4)

In the button click event try the following:

>
CurrentDb.Execute "UPDATE TABLE1 SET PUSHFLAG = 1 WHERE ID = " & Me.ID &
";"

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

Table2 will need the PUSHFLAG column.

CurrentDb.Execute "INSERT INTO TABLE2 IN '<PATH>' FROM TABLE1 WHERE
PUSHFLAG = 1;"

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

CurrentDb.Execute "UPDATE TABLE2 SET STATUS = 'DONE' WHERE PUSHFLAG = 1;"

>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.

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';"

Should be

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

[<PATH>].TABLE2 WHERE STATUS = 'DONE';"

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

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

--
Smartin

[Smartin]

Smartin wrote:

Jo*********@gmail.com 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).

You can do this.

>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)

This is all untested.

Sorry, some stoopid things I missed...

In the button click event try the following:

>
CurrentDb.Execute "UPDATE TABLE1 SET PUSHFLAG = 1 WHERE ID = " & Me.ID &
";"

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

Table2 will need the PUSHFLAG column.

CurrentDb.Execute "INSERT INTO TABLE2 IN '<PATH>' FROM TABLE1 WHERE
PUSHFLAG = 1;"

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

CurrentDb.Execute "UPDATE TABLE2 SET STATUS = 'DONE' WHERE PUSHFLAG = 1;"

Should be

CurrentDb.Execute "UPDATE TABLE2 IN '<PATH>' SET STATUS = 'DONE' WHERE
PUSHFLAG = 1;"

>

>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.

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';"

Should be

CurrentDb.Execute "DELETE FROM TABLE1 WHERE ID IN (SELECT ID FROM
[<PATH>].TABLE2 WHERE STATUS = 'DONE';"

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

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

--
Smartin