How to move data from 1 table to another?

[AbraAbraCadabra]

I want to move data from 1 table into another, but I first want to "process"
it with my code. Some of the data will be replaced by others and some
of the data from various cells may be combined, etc.

It seems to me that I should use a subroutine to accomplish this (it's just
a one-time thing). Could someone show me some sample code of how
to pull data from one table, put into into a variable, and then stick it
into
another table?

Thank you!

 

[Pat Hartman\(MVP\)]

Much of the time you don't actually need to process a recordset, you can do
everything with an update or append query. Either of these queries will be
faster than a VBA code loop if you have a lot of records to process.

If you decide you need code, you can find it by searching help for MoveNext,
AddNew and Update methods. You can use either DAO or ADO. I prefer DAO
when the tables are native Access.

If you have trouble with searching help, go to the table of contents and
drill down. You will see DAO near the bottom of the list.

 

[AbraAbraCadabra]

Much of the time you don't actually need to process a recordset, you can

do everything with an update or append query. Either of these queries
will be faster than a VBA code loop if you have a lot of records to
process.

Here are some examples of things I am trying to accomplish...

In Table1 there area 3 columns called "mo" "date" and "year".
I need to take these values and put them into a single column
called "date" which resides in another table.

For something like that would I still want Update/Append Query?

 

[manningfan]

I would. Sounds like an easy job for an Update query.

 

[Guest]

I agree. Here is an example SQL statement for a table named "Table1" and a
newly added date/time datatype named "NewDate":

UPDATE Table1
SET Table1.NewDate = CDate([mo] & "/" & [date] & "/" & [year]);

Furthermore, I recommend that you not name your new field "date", or any
other reserved word in Access. See Allen Browne's super list of names that
you should avoid using:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________