Results 1 to 5 of 5

newbie - how to move data between Access tables with macro?

This is a discussion on newbie - how to move data between Access tables with macro? within the Microsoft Access forums, part of the Question Forums category; This would be no problem if the files where in Excel, but I have not been able to make this ...

  1. #1
    Board Regular
    Join Date
    Jan 2005
    Posts
    62

    Default newbie - how to move data between Access tables with macro?

    This would be no problem if the files where in Excel, but I have not been able to make this work with an Access file.

    I have a macro for csv files that does almost exactly what I need. With a few modifications, I was able to open the tables in Excel, but could not figure out how to get the data back into the original file.

    Here is what I am trying to get the macro to do:
    1. Open each mdb file in a directory. (Each file contains several tables).
    2. Copy the data from column 3 in table "RQ", paste that data to column 26 in table "ID".
    3. Then copy the data from column 15 on table "ID" back to column 3 in table "RQ".
    4. Then save the modified mdb file to a new location and delete the old mdb file.

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,175

    Default

    Does the data really need to be moved between 2 different tables, or do you need to re-think your structure?

    A bit more info on what you have and what you need, would help. Otherwise, the way to do this in Access is via queries.

    Note, the examples below are just one way to proceed. There will doubtless be better ones when we get more information. Also, any time you rewrite heaps of records, do it on a copy of the database and keep a backup of the original.

    Examples:
    1. To do what you describe -- moving one field between 2 tables.
    Build a query with both tables, linking them on a common ID field (this field must be unique. If you don't have a unique field, maybe a combination of 2 fields will be unique: join the tables on both fields).
    With Table A, place all fields except for the excluded one into the grid. Add the new field from Table B. Check the data, return to Design view.
    Query > Make-table query. Provide a name for the new table, OK. Click the Run button (!) to create the new table.
    Repeat for the reverse operation.

    2. Another option -- appending the extra field from one table to another.
    Same query as above, but you only create one table. The difference is that you add all fields from A, and the extra from B.

    3. Third option -- you just want to replace data in table A with corresponding data in B.
    Same starting query. This time, Query > Update query. Say you want Field4 of Table A to be replaced with data from Field8 of Table B. In the Update row of Field4, type [Table B].[Field8] (adjust to suit).
    Run the query, and matching data will be written across to Table A.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  3. #3
    Board Regular
    Join Date
    Jan 2005
    Posts
    62

    Default

    I can not change the file structure. The mdb file is the output of one program which is being imported into another program. The two programs have a problem in that one program is loading the data into the wrong table for the other program. I can change the order of the columns in a given table, but I can't tell the software to swap the data from one table to the other. It is very fustrating.

    So software A, places a data into column 3 in table "RQ". But software B needs the data in table ID.
    The data placed by software A in column 15 on table "ID", must be in column 3 in table "RQ" for software B to function correctly.

    There is no relationship of the actual data or than they are residing in each other's space.

    The file is created, imported by the other software and then deleted. This is why I was looking at a macro. So that it can be run each time a new file is created.

    I have setup something very similar to this once before, but the files were csv. The macro started automatically (self cert.), opened the file, copied the data from the appropiate columns to a temporary location, then pasted them to their correct home, saved the file to a new location, sent the original file to the recycling bin and then checked for more files to convert. If no more files, it simply closed itself. Problem is trying to do this in Access instead of Excel/csv files.

    If I can't figure out a better way, I may read all the tables into Excel and kick out a single csv file which software B will accept and give up on the mdb file.

  4. #4
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,175

    Default

    If there is no relationship between the tables, you will find the manipulation very hard to do in Access.

    I'd recommend pulling them into Excel and doing the swap there, as long as you are sure that the data will be OK. As for getting the data back out to csv, push each sheet to a new file and save as csv.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  5. #5
    Board Regular
    Join Date
    Jan 2005
    Posts
    62

    Default

    Thanks, I think that is the path I will go.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com