Re: Move record from one table to another

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@xxxxxxxxx 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?

.

Relevant Pages

  • Re: Tricky Visual Basic Code help...
    ... moves the current record into a new record in table2. ... this with a little VBA code and a couple custom queries. ... Set a "pushFlag" column in the record in table1 to a specific ... If a duplicate is not found, ...
    (microsoft.public.access.formscoding)
  • Re: Noob question on Access table merging
    ... database, some of the items I already have in my database. ... Now create a query with Table1 and Table2. ... Dbl-Click on it and select the option to select All records from Table1 and only those that match in Table2. ...
    (comp.databases.ms-access)
  • Re: counting in a query
    ... Say you've got two instances of 12345 in table1 ... and only one in table2. ... >>> How do I get it to give me a count of the zip5 field in each database ... >>> Joe Robison ...
    (microsoft.public.fox.programmer.exchange)
  • Re: MS SQL copy new and modified rows from TABLE1 to TABLE2
    ... Or ar the tables in different databases on different servers? ... were ever added to Table1. ... there in Table2. ... they are on the same server and in the same Database. ...
    (comp.databases.ms-sqlserver)
  • Re: real world singleton class example?
    ... I've used them for database classes before. ... What is the use of a singleton is when running PHP? ... table1 and table2 tables within the database. ...
    (comp.lang.php)