Access2003: Copying table data between two MDB files?

[ChrisGreaves]

I used to maintain a database “OLD” which has useful forms, queries etc, and was somewhat useful for sales – chasing up reads, stale contacts etc.,

I have inherited a similar database “NEW” which contains a striking similarity to my old Contacts database.

I have deleted all the records from the OLD database; it is a structure with queries and forms and, right now, an empty table.
I would like to know if there is a relatively easy way to copy/move the data from the much simpler table in the inherited NEW database into my more complicated table in my OLD database.

And if so, the general direction I should take.
(1) Write a simple program in WordVBA to copy .Fields of an rst across, on a record-by-record basis (I know I can do this)
(2) In Access2003 use File, Import (I have seen but not used this)
(3) In Access2003 use File, Link (I have seen but not used this)
(4) some other method

The OLD database table is empty, contains zero records.
The NEW inherited table holds 210 records.
The screen snapshots show the similarity between the two tables.
I can drop some of the unmatched fields such as SIC (“Industry Code”), Fax number, Web Page and so on.
Thanks
Chris

[HansV]

I'd do the following:

- Open the 'old' database.
- Select File > Get External Data > Link Tables...
- Navigate to the 'new' database, select it and click Link.
- Select the contacts table, then click OK.
- You should now see the linked table in the Tables tab of the Database window. It has an arrow next to the table icon to indicate that it's a link.
- Activate the Queries tab of the Database window.
- Double-click 'Create query in Design view'.
- Select the linked table, click Add, then click Close.
- Click the Query Type dropdown on the toolbar and select Append Query...
- Select the 'old' contacts table as target and click OK.
- Add all the fields that you want to transfer from the field list to the query grid.
- For field names matching those of the target table (such as TITLE and GIVEN), Access will automatically populate the 'Append to' row.
- For the rest, you'll have to specify the 'Append to' field yourself by selecting it from the dropdown in the 'Append to' row.
- Finally, click the Run button and confirm that you want to append ... rows.

[ChrisGreaves]

I'd do the following:...

HV> - Add all the fields that you want to transfer from the field list to the query grid. - For field names matching those of the target table (such as TITLE and GIVEN), Access will automatically populate the 'Append to' row. - For the rest, you'll have to specify the 'Append to' field yourself by selecting it from the dropdown in the 'Append to' row. - Finally, click the Run button and confirm that you want to append ... rows.


Hans, thanks for this detailed reply. I gave it a shot two days ago, but found that I had to drag all the fields across manually. I had previously standardized most of the field names in the hopes that “automatic” would kick ion. I preserve red and did not see any “RUN button”.

I will try again this weekend.

In the meantime I bit the bullet and exported both tables via Excel, did a quick column-drag in Excel, and then imported the data back into a voided table, so I am on my way.
However I will go back and try again, because it seems to me that it ought to be possible for a user to “drag a table from one database table to a separate database table”, to put it crudely.

Thanks
Chris

[ChrisGreaves]

I'd do the following:..

SUCCESS!


TRAPS FOR YOUNG PLAYERS (1) I misunderstood the “automatically populate” instruction; I expected Access2003 to save me the effort of dragging fields that were well named. Of course, I may not want all the fields dragged across, so auto-drag is noit necessarily a good action
(2) I was expecting to see data in a query table instead of a data table. This shows how badly-wired my brain can be at times!


Cheers
Chris

[HansV]

How early does the café open?

[ChrisGreaves]

How early does the café open?

Normally 7am-11pm.
I was up at 4 this morning because I couldn't get to Geoffrey's memorial do in Melbourne, so I downed my first coffee and said "Cheers Geoff" while everyone was swigging
Then I bounced in here at 7am and got an email saying that the "do" was cancelled, so I am now on my fifth coffee by 8:48 and bouncing off the walls.

The cafe may close because the gambling commission has turned off the slot machines canada-wide, and that, apparently, is the cafe/pubs primary source of income.

Cheers
Chris

[HansV]

All pubs, cafés and restaurants in The Netherlands closed down at 6PM on Sunday (they're now allowed to provide take-away though).