Upgrading from Access to SQL

[TanyaSealey]

We currently use a large Access 2000 database but we're planning to upgrade to a SQL Server back-end and Access front-end. The database is supported in-house and there is a lot of Access expertise (VBA, SQL etc) but no SQL Server experience. Could anyone give me tips on the best way to go about upgrading the database, and what exactly is involved? How effective is the upgrade wizard in Access? How transferrable are our Access skills to SQL Server? And how long do upgrades of this kind normally take?

Any advice would be very much appreciated.

Thanks.

 

[grnzbra]

Perhaps it was the design of the Access database, but when we tried that at my last job, most of the queries came back unconvertable. Tables went ok, but there were so many queries that couldn't be converted that we went back to the people who designed the system to have them rebuild it for SQL. Of course, the magnitude of this problem might be a matter of perception because we were told that all we had to do was run the system through the upsizing wizard and, "Poof!. Instant SQL Server database!".

 

[grnzbra]

Oh, I should have mentioned that I was the only one there so I had to do everything with the conversion whenever I could get a few moments away from my regular duties. My fluency with SQL wasn't that good. If you've got the SQL expertese, rewriting queries that don't convert shouldn't be too much of a problem.

 

[abig99]

ok, we have done something very similar in the last year, its well worth it after all the problems.
SQL server uses SQL simlar to that used in making queries in access, so start leaving the query builder behind and practice while still in access.
SQL server front ends should be created and tested well in advance of deadlines, they tend to have many bugs, especially in recordsets and queries.
Dates are treated as american in SQl server usually.
When you transfer your tables you will lose relationships and keys set in access. All SQL tables require a kep field in order to be updatable in my experience, often autonumbers can be used.
now we have it up and running i have forgotten alot of the problems we had, will keep ya posted if my mind begins to work again.