access to sql (1 Viewer)

[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.

 

[Pat Hartman]

Access is much more forgiving of sloppy coding than SQL Server is. Delete the generated SQL Server database. Fix the Access database and try again. Remember that Access generates hidden indexes for all foreign keys so while you're at it, look at all the indexes and get rid of any that you generated for the same purpose. If you made the mistake of leaving the property that generates indexes for all fields suffixed with some terms like "ID", then fix that property now and get rid of all the unnecessary indexes.

Keep fixing the Access app until you get a clean conversion. Think of it this way, you are learning how not to define tables and your next application will convert ever so much more smoothly. I've gotten to the point where it rarely takes me more than two tries and less than an hour to do the entire conversion.

Once the conversion is done, you may still have a lot of work to do. Any DAO code you have written, might need to be changed to add the argument - dbSeeChanges when you open a recordset against a table with an autonumber/identity column primary key.

Then you'll get to your queries where you may have trougle if you've used UDF or VBA functions.