How do you move or copy data into another database?

[jeffro]

Hi

I'm trying to move data from one database table to another database table. What I did was rebuild the old database to update some fields and now I would like to move the data into the database I rebuilt. Does anyone have any ideas?

Thanks aja

[Dr_Evil]

What type of Database are you using? If you are using SQL server you can use DTS to move your data. If it is an Access DB use the import/export data option. If you have any questions post your DB info along with it and I'll give you a more descriptive answer.

[jeffro]

I'm using ms access db. How exactly do you use the import/export method?

thanks aja

[Dr_Evil]

OK, for an access db you will need to export to a text file unless you want the definition (Column Names & Properties) to be exported also.

To do this:

1. Right click on the table that contains your data.
2. Click Save As/Export.
3. Select External File.
4. Change type to text file then click export. This will open up a wizard.
5. Here choose delimited and click next.
6. Select the delimiter you wish to use. You can set the "Text Qualifier" to none also.
7. Now click next and then Finish.
8. Now open the destination DB.
9. Click get external data in the file menu.
10.Click Import.
11.Change type to text file and click import.
12.Select delimited, then click next. Make sure you select the same delimiter here and set "Text Qualifier" to none. 13.Click next, now select your destination table. Click next and then Finish.

This should help you get your data moved. Good luck.

[jeffro]

I can get up to instruction number 3, then when I change the type to text and click export the wizard does not come up on the screen. Why is that?

aja

[Dr_Evil]

I'm not sure what would cause that. I just went through the process to make sure it was correct. Make sure you have a file name entered and also make sure "Save Formatted" isn't checked. Providing the above is done this should work fine for you.

[jeffro]

do you know the name of the wizard so I could do a search to see if I even have installed it?

thanks aja

[Dr_Evil]

Export Text Wizard

[paulw]

Joined this a bit late - there is a much easier way to do this.

Create a Make Table query and define the target table as the same table name but in a different database. Select 'Another Database' option in the dialog box asking for the target table name, and type in the target .mdb full filename (with path and .mdb extension).

Do a SELECT * and hey, presto! Table is copied over.

i.e. If you are trying to move table Test in MyDb.mdb

SELECT Test.* INTO Test IN 'C:\MyPath\MyOtherDb.mdb'
FROM Test;

Cheers,

Paul.