Ms access to sql

SQL Server Administration
1

Hello

I have ms access db which has complex relationship between tables, and there are above 100 tables in the database, the relation between tables are very much complex ( I mean in terms of referential integrity ).
I would like to migrate ms access tables to sql server with referential integrity intact.

For this I tried MS access 2010 upsizing functionality which fails with below errrors

Relationship or Constraint Failed to Upsize: Server Error 1753: Column 'tblFinInstructions.FinInsId' is not the same length or scale as referencing column 'tblAddFinInst.FinInstId' in foreign key 'tblAddFinInst_FK00'. Columns participating in a foreign key relationship must be defined with the same length and scale. Server Error 1750: Could not create constraint or index. See previous errors.

I have above errors at lots of places in the above activity which is being generated as report at the end of upsizing fuctionality.

I tried Sql server migration assistant it ends up with lots of warning and one error message but again it does only partially migration of referential integrity to sql server tables.

I also tried SQL server import and export functionality which also has done partial moving referntial integrity of ms access tables to sql server.

Above stuff do not seems to be working for my database atleast. and I am middle of sea now, if someone can please help if one has done similar activity in past with success would be very much helpful.

Thanks
Man

2

have you tried migrating using ssis

  1. create the table schemas in sql
  2. import data to a staging database using ssis
  3. then using tsql migrate data from staging tables to real tables
3

The problem is in your access db - you need to fix it there before you can migrate. Access has allowed a relationship to be defined between 2 different data types - in one table the column could be defined as int and the other table defined as numeric.

In order to be able to create the relationships - the data types must be the same. If the column in table A is defined as numeric(18,4) - then it must be defined as numeric(18,4) in table B.

If you cannot make those changes in the Access database - then you need to create the tables and relationships manually in SQL Server and then use SSIS to migrate the data.