Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004

    Smile Unanswered: autmatically shifting from one table to other

    Hi there

    I am a new user to MS access 2002 .I just want to know how can i traqnsfer all the records from table A to table B with exactly same fields.
    The problem is I dont want my table A to grow more than 100 records so i am looking for a way so that it can automatically transfer all of its records to Table B when the Table A reaches 100 records..

    any help will be much appreciated


  2. #2
    Join Date
    Nov 2003


    (always test on a copy of your db first..., backups are important)

    First time, if Table B is empty, simply create it as a copy of table A:
    In the db Tables window, right-click on the table and choose save as... - give the copy of the table a new name...

    For subsequent transfers, make an append query like this:

    SELECT tbl_A.*
    FROM tbl_A;

    (as long as the two tables are indeed identical , table/field definition wise).

    You can open a new query in Design view, then choose View/SQL view from the menu and replace the suggested select text there with the syntax above here. Then click on the table view icon to preview and make sure the query returns (the desired) data. Then save the new query and run it by double clicking on it in the database window's queries list.

    And then, AFTER closing and backing up the db... delete the records from the original table by opening it and choose Edit/Select all records and then press the Delete key.

    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  3. #3
    Join Date
    Jun 2004
    Saratoga Springs
    Excellent walk through kedaniel.

    Do you need assistance writing the code to do the described process automatically (for example, when you click a button or when a form is opened)?
    Jon Jaussi, OCP DBA
    D4L Data Managent Solutions

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts