MDB to .SQL

Question

MDB to .SQL

William Schlichtman

Valued Member

Points: 61
More actions
September 5, 2005 at 11:26 pm

#69634

Are there any utilities out there that, instead of upsizing from Access to SQL, upsize from MDB to .SQL script files containing all the SQL statements that it would take to recreate the database?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

I use Visio for Enterprise Architects that comes with Visual Studio for Enterprise Architects (2002 or 2003). This allows you to "Reverse Engineer" the MDB, then by changing the database default from Access to SQL Server, it can generate the SQL Server database or script to create the database.

I always use the script option so I can control the database creation, split the generated script into 2 parts: create the tables and PK constraint, and create the indices and FK constraints. That way I can run a database population script or DTS Import between the two parts and not have to worry about the order of population to comply with FK constraints.

It also allows you to tweak the data types like changing smalldatetime to datetime, nvarchar to varchar, etc. Not to mention a great printed copy of the data model (ER diagram).

Andy

Parker Smith SSChasing Mays Points: 619 More actions · Answer 2

Alternatively, you could run the upsizing routine, and create the scripts from the SQL Server database itself using Enterprise Manager.

Then you could drop or keep the database itself, depending on your needs.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Having performed this type of task with various Access versions from 95 to 2000 I'd opt for Parker's method at a minimum. At times I've had access to software like ErWin and PowerDesigner for documentation purposes (they actually do a good job of schema translation as well !). But the proof is in the pudding so to speak. You've got to test it at least once to find out just what is not totally compatible. Besides, I've never really trusted a 'wizard' ! SO do it, test it, modify it and script it.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 4

I tried the Upsizing tool and found it troublesome.

I use DTS Import Wizard to get my databases into SQL from Access and then use EM to develop the scripts as above.

Richard