access to sql

[DevAccess]

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

 

[Minty]

That sounds as if you have some foreign keys set up as integer values in (sometimes known Short numbers) that are Long Integer types in the primary Autonumber field.

You will need to change that before anything works, no matter what system you try and use.

 

[DevAccess]

That sounds as if you have some foreign keys set up as integer values in (sometimes known Short numbers) that are Long Integer types in the primary Autonumber field.

You will need to change that before anything works, no matter what system you try and use.

Do you mean I should change this datatype change in ms access before migrating to sql ? and this is just sample error there are more then 100 errors which appears while upsizing functionality

 

[Minty]

I think you will have to make the datatypes match exactly.

SQL Server will allow joins on differing datatypes in Views and Queries, but won't allow it for enforced referential purposes as far as I know.