[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

copying from one table to another in ms access 2007

Hi.
If I have a database table with 20 records and want to copy a few records that have been checked off to second existing table (using a check field in the first table) how is this easily accomplished. I assume you have to do it in VBA as I am using access 2007.
e g
TABLE 1
record 1
record 2 (checked)
record 3
record 4
record 5 (checked)

TABLE 2
(contains 50 records)

simply take record 2 and 5 and copy them into TABLE 2. Then uncheck the two records.

I am not sure if I have to reopen the tables in VBA? I have a check field in TABLE 1
0
PawloA
Asked:
PawloA
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can do this:

Currentdb.Execute "INSERT INTO [Table 2] (Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM [Table 1] WHERE MyCheckBoxField=True"

Currentdb.Execute "UPDATE [Table 1] SET MyCheckBoxField=False"

Obviously you'd have to change the columns and field names to match your own.

You don't have to open the tables.
0
 
Helen FeddemaCommented:
You could make a select query containing the checked items from Table 1, and then make an append query to append all but the key field for these recods to Table 2.  Then an update query to set the Yes/No field in Table 1 to False (unchecking these records).  After testing these queries, you could run the append and update query from VBA code, using OpenQuery.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
That will be two queries:  INSERT and UPDATE

INSERT INTO Table2 (column1, column2, the_check)
SELECT column1, column2, the_check
FROM Table1
WHERE Table1.the_check = True

... then ...

UPDATE Table1
SET the_check = False
WHERE the_check = True
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
SteveCommented:
You could do it with an append query. No vba needed.
0
 
PawloAAuthor Commented:
actually it would be append instead of insert I believe
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade, although I believe LSMConsulting had the same answer, and posted it before I did, so really he should be awarded at minimum half the points, probably more.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now