asked on

Move data between databases

Using the Create database nd Create tables, I create these along with fields to match an existing database with a different name. Is there an easy way to move data to the new database and wipe out the records from the existing one besides going through every field.
Thanks

ASKER CERTIFIED SOLUTION

waty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robhas

ASKER

Could you please be a little more specific about copying the records. The table I need to copy to is in an entirly different database.
Thanks

waty

You could use as following :

sSQL = "Select * from Table1"
set Record = DB1.OpenRecordset(sSQL, dbOpenDynaset)

if record.RecordCount > 0 then
record.Movelast
record.MoveFirst

' *** Ignore duplicates
On error Resume Next

For nI = 1 to record.RecordCount
' *** Construct here your insert
sSQL = "Insert Into ....." & record("YourField")...

DB.Execute SQL

Next

End If

Otherwise, your database are 2 Access database?

robhas

ASKER

I successfully coded the area to delete all records from a table but I am still having problems copying the data from a table to a different database. I am using this statement below but I keep receiving a syntax error. Can not figure out what is wrong.
Thanks

waty

Post your code, I will check

robhas

ASKER

Here is the code I forgot to add to the last comment.
dbCurrent.Execute "INSERT INTO CATEGORYGROUP IN PPRevenues & Year(Date) - 1 & .mdb select * from [categorygroup];"

Thanks

waty

You should create the query like that :

dbCurrent.Execute "INSERT INTO CATEGORYGROUP IN PPRevenues " & cstr(Year(Date) - 1) & ".mdb select * from [categorygroup];"

robhas

ASKER

It worked great. Thanks for all the help.