Yup - I'd say my knee was a bit quick on the jerk there <g>.

On Fri, 17 Dec 2004 17:05:26 -0000, "John Bell" <jbellnewsposts@hotmail.com>
wrote:

>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.
>>
>