Hi Steve

I don't disagree with anything you said, but you seemed to have missed the
caveat in the first sentence, i.e. remove access all together and you don't
really have any choice.

John

"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
news:kd26s0pl1p1s0dr38ah8gftbqu762cvles@4ax.com...
>
> On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell"
> <jbellnewsposts@hotmail.com>
> wrote:
>
>>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.
>