Trying to migrate Access to SQL Server 2005 Express

[3587ch]

I'm trying to migrate Access to SQL Server 2005 Express. I installed the SSMA but it will not connect ot my Express server (don't know why) I have Management Studio Express, but it does not include the Import options.

Does anyone know how I can mirgate?

Thank you,

 

[lameid]

Within Access you can use the upsizing wizard.

In 2003 it is under Tools, Database Utilities

 

[3587ch]

It does a really poor job of upsizing the queries and code when you wan to create an adp file. I was hoping that someone might know of a better option.

Thank you,

 

[lameid]

I think Access is going to be the best bet aside from a commercial tool of which I don't know any.

As for ADP's in general...

thread958-1528961

 

[JoeAtWork]

I've upgraded at least 10 databases using the Wizard, and found it does about 95% of the work, but you need to do a little tweaking at the end. In what ways have you found that it does a poor job?

I've only once done an ADP, I prefer linked tables instead. ADP's tend to require more post-upgrade work, and I find them too inflexible. I've found that when upgrading to linked tables, there's far less work that needs to be done, especially if you started off with a properly normalized database.

 

[lameid]

Personally, I found SQL server hates the Jet IIF function. That was my personal corner of hell when I converted queries. I had nested queries with sums for reporting which utilized NESTED IIF funtions. You have to use the T-SQL Case When Statment instead. I don't have the code from that job but I ended up writing a function to parse the JET SQL and convert the IIF's to Case When for me. That was obnoxious with all the syntax considerations.

 

[MichaelRed]

" ... function to parse the JET SQL and convert the IIF's to Case When ... "

sounds quite useful, like a faq?

MichaelRed

 

[lameid]

Unfortunately I never got around to making a FAQ...

I don't have the code from that job