Convert from Access 2003 to SQL

[ibeetb]

We want to expand our DB from Access to SQL Server.
1)Is SQL 2005 stable enough to use yet?
2) Does anyone know of any documentation I can use to research this
conversion (i.e setup, architecture, risks, benefits, disadvantages...)

Thank you

 

[Sylvain Lafontaine]

To my knowledge, the upsizing wizard of Access 2003 may not work SQL-2005;
so if you want to use this wizard, you should start with SQL-Server 2000 or
MSDE 2000 for the upsizing step.

However, many people make their own upsizing without using any wizard; so
the incompatibility problem won't make any difference in that case.

 

[Gabriele Bertolucci]

To my knowledge, the upsizing wizard of Access 2003 may not work

SQL-2005; so if you want to use this wizard, you should start with
SQL-Server 2000 or MSDE 2000 for the upsizing step.

Why not?

I've used the upsizing wizard in Access XP to create two databases in SQL
Express and got not problem (and I don't understand why I sould get some).

Obviously, you cannot upsize queries, only tables and relations.

However, many people make their own upsizing without using any wizard;
so the incompatibility problem won't make any difference in that case.

Nice, but I think this sould be a big waste of time, provided that there
exists a functional way

 

[Sylvain Lafontaine]

Oups! Sorry, my mistake. I think that you're right about upsizing tables
and relations.

The fact that ADP 2003 won't work in design mode with SQL-Server 2005 and
also that most queries (excerpt for the basic ones) in an Access database
won't be upsized correctly by the wizard made me forgot that it will have no
trouble for upsizing only the tables and relations on SQl-2005.

Did you have to put the database in the compatibility mode fro SQL-2000
before making the upzising?

 

[Gabriele Bertolucci]

Did you have to put the database in the compatibility mode fro

SQL-2000 before making the upzising?

Before?!?

I had to do it AFTER the upsizing process.

 

[Sylvain Lafontaine]

Just out of curiosity, you had to do it after the upsizing process for an
ADP project or for a MDB file with linked tables?

How did you know that you have to change the setting? Was there any
particular error message?

 

[Gabriele Bertolucci]

Just out of curiosity, you had to do it after the upsizing process for

an ADP project or for a MDB file with linked tables?

I never worked with ODBC and linked tables, so I can speak only for ADPs.

How did you know that you have to change the setting?

I frequent some italian newsgroup and should have read it somewhere in a
post there.

Was there any particular error message?

Sincerely no, but I don't know real problems occurring in not applying that
setting.
I've read in the BOL some annotations regarding compatibility level.
It implies some database behaviours, almost all regarding Transact-SQL.