Link to home
Start Free TrialLog in
Avatar of cmdown
cmdown

asked on

Drop and Recreate Tables without losing data

We have an application that has previously used an access database.  Upgrading to multiuser allows us to host the db on MS sql server 2012  but the SSMS import routine has failed to create the primary keys and identity columns (process followed - Create new database in SSMS then rt clk on database and import from Access).  SSMS 2012 will not allow me save changes as it wants to drop and recreate the tables.

Is there a way to do this without losing the existing data that was imported at the same time ?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cmdown
cmdown

ASKER

Hi LSMC - yes you are correct.  I am already trying to use SSMA - latest v5.2 downloaded this morning.  I hit a few issues but having re-run it seems to have converted everything correctly.  Many thanks for the speedy reply.  Points awarded as answer was spot-on !
Avatar of cmdown

ASKER

Hi LSM

I'm reposting the question as although your answer was spot on (!) SSMA has introduced a problem in that it has changed the database schema and now the program can't write to the database so it looks as if I might well be back to dropping and recreating the tables.
Is your frontend written in Access? If so:

Did you add TimeStamps to the tables? Often Access needs to have those ...

Also be sure that the Primary Keys were setup properly, and that Access has the PK represented properly in the linked table. You can open the linked table in "design" view (Access will complain, but will let you open it anyway) and review the Indexes and such.