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

JR1412

Board Regular
Joined
Jan 29, 2005
Messages
62
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top