How do I copy one table to another within a form

[Dennis]

Hi,

I'm getting ready to release my second update to my database. I've split
my original table tblMailingList into two tables; tblMailingList and
tblNewsLetter.

I've also changed the key on tblMailingList. I originally had the user
enter a key, but I've changed it to an Access Auto generated number. The key
on the tblNewsLetter file is a number and is equal to the autonumber on
tblMailingList. (I split the data for traffic reasons. One group maintains
the Mailing List and another group maintains the News Letter info.)

I've also rearrange the fields in tblMailingList.

So what I need to do is

1. Copy all field from old tblMailingList to new tblMailingList. The new
tblMailingList table will have the new auto generated key.

2. Copy the appropriate fields from the new tblMailingList (with the new
key) to the new tblNewsLetter table.

3. Remove the unwated fields from the table definition and remove all of the
data from those fields in the table.

I know I can do this manually, which I've done a couple of time when I was
testing. However, I want to expand my Access knowledge and learn how to do
this programmatically.

How would I go about doing this? Any help would be greatly appreciated.


Thanks,


Dennis

 

[John W. Vinson]

Hi,

I'm getting ready to release my second update to my database. I've split
my original table tblMailingList into two tables; tblMailingList and
tblNewsLetter.

If their structure is identical, this is almost surely a mistake. If the two
tables have the same fields, you would only need to add one more field to
indicate that the entry is a Mailing List or a Newsletter (or perhaps both).

I've also changed the key on tblMailingList. I originally had the user
enter a key, but I've changed it to an Access Auto generated number. The key
on the tblNewsLetter file is a number and is equal to the autonumber on
tblMailingList. (I split the data for traffic reasons. One group maintains
the Mailing List and another group maintains the News Letter info.)

Access is multiuser out of the box. There is nothing to prevent two users from
concurrently updating the mailing list table.

I've also rearrange the fields in tblMailingList.

The field order is completely irrelevant in a table. You're not doing data
entry directly in the tables, I hope!? You should be using a Form (or two
forms) to do so.

So what I need to do is

1. Copy all field from old tblMailingList to new tblMailingList. The new
tblMailingList table will have the new auto generated key.

2. Copy the appropriate fields from the new tblMailingList (with the new
key) to the new tblNewsLetter table.

3. Remove the unwated fields from the table definition and remove all of the
data from those fields in the table.

I know I can do this manually, which I've done a couple of time when I was
testing. However, I want to expand my Access knowledge and learn how to do
this programmatically.

How would I go about doing this? Any help would be greatly appreciated.

IF... and as noted above it's one big honking IF... you actually want to do
this, the simplest way would be to create a new tblMailingList (either with a
new name, or you could rename the existing one to oldMailingList) and a new
tblNewsLetter. You can do so by selecting oldMailingList in the database
window, type ctrl-C to copy, ctrl-V to paste, select "Design view only", and
give the new table name. Do this twice, once for each new table.

Then open each new table in design view and make the appropriate field
changes.

Finally, create two new Append queries to migrate the data.

Doing it programmatically would only be needed if you (for some reason) need
to do it repeatedly on a lot of databases (is this an app that you've sold?);
it would be far more work than the above. It's not impossible, post back with
an explanation if you need to do so.

 

[Dennis]

John,


Q. Is their structure is identical?
A. No - The structure is totally different. Basically, I'm moving 10
fields from tblMailingList to the tblNewsLetter table. I'll then delete the
10 fields from the tblMailingList table

Q. Access is multiuser out of the box. There is nothing to prevent two
users from concurrently updating the mailing list table.
A. I understand this. This decision was reached based upon some rather
unique business requirements. This was the simplest solutions.


Q. The field order is completely irrelevant in a table.
A. I understand this. However, I believe that a “pretty†table definition
is easier to work with than one where the field definitions are shot gunned
over the place.

Q. The simplest way would be to create a new tblMailingList …
A. I’ve already done this three or four time so I can refresh and test the
data. I will need to do it a few more times and especially when I put it
into production. I just hate having to data a data conversion by hand. It
is so easy to make a mistake.

Q. Create two new Append queries to migrate the data.
A. This is my main problem, I’m still learning SQL. Could you provide a
simple example of copying to data fields from one table to another. I think
I could take it from there.

Q. It would be far more work than the above. It's not impossible, post back
with
an explanation if you need to do so.
A. My main reason for this is I hate doing manual data conversion on live
data. I would have preferred to have it programmed and fully tested so I
know it worked. I don’t want to be called at 8:00AM with people telling me
that the system is not working.. I’m still learning Access and I though this
might be an interesting project to tackle. I figured it would help me
improve my knowledge of Access and SQL.

If it is almost “impossibleâ€, are you implying that Access is not a platform
for small commercial packages or are you saying that I have to learn to do
things the Access way?

Another approach is to have four tables:
1. tblMailingList - the original table.
2. tblMailingList_Auto – the original table with the Auto Generated number
key.
3. tblNewsLetter – This is my new table with the 10 fields split out. The
key to this file is the same as the key to the tblMailingList_Auto table.
4. tblMailingList_Final – this is the original table with the Auto
Generated number key and the 10 field names removed.

I could run one query where I copy the data from the tblMailingList to the
tblMailingList_Auto. Another query would copy the 10 fields from the
tblMailingList_Auto to the tblNewsLetter table. And a final query would copy
the fields from the tblMailingList_Auto to the tblMailingList_Final table. I
would rename the original tblMailingList to tblMailingList_1_7 (version
number). I would rename the tblMailingList_Final to tblMailingList.

Give your comments, I believe this approach will produce the same results
with much less work on my part. Is that a better approach?


Thanks for your assistance.




Dennis