how to migrate 2 access databases to ONE sql database

[Ray_s]

hi,

I have 2 access databases (same structure but different data: one for 2007
data the other for 2008 data), and want to upgrade / upsize / migrate
(whatever) both access databases to ONE sql 2005 database.
If I use the Upsize tool on the first access db (which will create the sql
db on sql server and migrate data from the first access db to sql) - this
would be step1- ; how would I then migrate the data from the second access
db to the same sql db - this would be step2 - ?
or, what is the best way of doing what I want to do?
thanks

[John W. Vinson]

Post by Ray_s
hi,
I have 2 access databases (same structure but different data: one for 2007
data the other for 2008 data), and want to upgrade / upsize / migrate
(whatever) both access databases to ONE sql 2005 database.
If I use the Upsize tool on the first access db (which will create the sql
db on sql server and migrate data from the first access db to sql) - this
would be step1- ; how would I then migrate the data from the second access
db to the same sql db - this would be step2 - ?
or, what is the best way of doing what I want to do?
thanks

If you get the first database correctly upsized, then I'd suggest using a
frontend linked both to the SQL tables and to the 2008 Access tables. Run
Append queries to append the 2008 data to the SQL tables.

If the tables have Autonumber ID's, this may get complicated, since you'll
almost surely have duplicate ID's. This can be fairly easy or a real mess
depending on how many related tables you have, and how far the foreign key
values have spread.

[Ray_s]

I know about sql server, but I kow very little about Access. I am not sure
I understand what you meant by:
[...] then I'd suggest using a > frontend linked both to the SQL tables and
to the 2008 Access tables. Run
Append queries to append the 2008 data to the SQL tables.
could you please explain or is there a link to further details.

another question:
we have another split access database (reports, data) that need be upsized,
what are the steps to upsize such
pair of databases? upsizing only the access "data" database, but then what
to do with the other "reports" access database.

Thank you for your help.

Post by John W. Vinson

Post by Ray_s
hi,
I have 2 access databases (same structure but different data: one for 2007
data the other for 2008 data), and want to upgrade / upsize / migrate
(whatever) both access databases to ONE sql 2005 database.
If I use the Upsize tool on the first access db (which will create the sql
db on sql server and migrate data from the first access db to sql) - this
would be step1- ; how would I then migrate the data from the second access
db to the same sql db - this would be step2 - ?
or, what is the best way of doing what I want to do?
thanks

If you get the first database correctly upsized, then I'd suggest using a
frontend linked both to the SQL tables and to the 2008 Access tables. Run
Append queries to append the 2008 data to the SQL tables.
If the tables have Autonumber ID's, this may get complicated, since you'll
almost surely have duplicate ID's. This can be fairly easy or a real mess
depending on how many related tables you have, and how far the foreign key
values have spread.
--
John W. Vinson [MVP]