Hi
If you are going to remove access completely your access queries will need
to be re-written as stored procedures or views and the reports rewritten in
(say) VB. Alternatively you can use an Access Data Project where most of the
code can remain the same.
I have to say it - bah! There - I said it.
Every single solitary time I've seen someone post on Usenet asking about
upsizing an Access database to SQL Server, the first response says something
about having to convert all your queries to views and stored procedures.
Frankly, that's not at all necessary, and a serious drain on time and money.
Access/DAO mostly does a fine job of translating queries of linked tables into
reasonable server-side SQL for execution, and it creates prepared statements
for parameter queries, so SQL Server won't have to keep recompiling each query
each time it sees it. Furthermore, Access is quite happy doing things like
using a named query parameter to look up a value from a form control and
allowing editing of values from multiple tables in a query with a join.
If you convert all these things to views and stored procedures, you have to
figure out how to do things like bind forms to parameterized stored
procedures, and implement extra code and forms for editing that were never
needed before, etc. It's a huge, complex, error prone exercise that forces
you to add complexity and remove convinience in the UI, all for basically no
return on investment.
Now, you're going to say that sometimes Access does not do a good job of
producing the server-side SQL. Yup, that's true, so in -those- cases, using a
view and/or a stored procedure can be very beneficial. You're also going to
say that if you have multiple systems accessing the same back-end, you want to
make the database interactions consistent and centralize business logic. I
say, yes, but again, do that incrementally as needed for specific cases. If
you really need to centralize a -lot- of bunsiness logic, T-SQL is not a great
language for it anyway, and you probably should think about a 3-tier system
and dump the Access UI altogether.
The access upgrade wizard will migrate most things, although it can run into
problems if you are using something that is more complex.
Here's a case where I do recommend doing it the "hard way. I've never seen
the upsizing wizard do a particularly good job. For each table, you should be
deciding what should be your clustered index, whether you need a TIMESTAMP
field, checking for NULL in Boolean fields, etc.
I suggest that you create a test system and copy your database and
application onto it. Then you try the upgrade, and you will get a feel for
what is involved and how much can be easily migrated.
An excellent suggestion - I agree.
