"Smithers" <A@B.COM> wrote in message
news:%23hS%231juEGHA.648@TK2MSFTNGP14.phx.gbl...
> I have a *potential* client that has an MS Access-based OLTP application.
> They currently have two .mdb files - one for the data that lives on a file
> server, and another .mdb file on each client workstation that contains the
> forms, reports, queries, and modules. They are planning to migrate the
data
> to SQL Server but keep forms etc as a MS Access client application.
>
> The reason they want to migrate the data to SQL Server is specifically to
> enhanse performance.
>
> A few years ago this application had supported about 10 users and now it's
> close to 120 users. IMO, the performance issues aren't necessarily due to
> the size of the database (under 1GB) or number of concurrent users -
> although that's obviously a bit much for MS Access. The real problem is
the
> fundamental design of the database - which contains many tables that are
> more or less denormalized. The in-house developer apparently believed and
> continues to believe that tables *must* (and can only...) directly reflect
> the UI they are supporting. There is really no concept of separating the
> data structures (DDL) from the presentation (i.e., no DAL in the middle).
> Duplicate data and similarly expected problems are prevalent.
>
> The in-house developer believes, and has told his management, that simply
> moving the data from Access to SQL Server will improve performance
> significantly. They do not have the budget or in-house technical resources
> to completely rewrite the system which, IMO, is going to be necessary
sooner
> or later. For now they are hoping that moving the data to SQL Server and
> "throwing hardware at it" will be good enough to result in acceptable
> performance - without any attempt to get rid of the MS Access front end or
> any significant redesign of the database. And yes, they already have
> reasonably good indexes in their tables - meaning that their current
> performance is about the best they can possibly have unless they do a
major
> redesign and rewrite.
>
> I need to be able to talk in specific terms with the management and
in-house
> technical staff about the pros and cons of their plans. While I can talk
> about the db design issues and application architecture, I've never dealt
> with an Access client to a SQL Server db because I always thought it was
> simply a bad practice from the beginning and never really thought through
it
> much less actually worked with that scenario...
>
> thus I have a few questions:
>
> 1. I am wondering what the biggest technical issues are (i.e. reasons
doing
> this is a bad idea). Does this arrangement (Access client to SQL Server
db)
> fail to make use of SQL Server's available locking and other concurrency
> mechanisms? Does this scenario basically result in a [file server
> architecture] with queries processed on the client and not on the server
> (because MS Access is at the front end)? What else?
>
> 2. What can we do to move processing to the server and out of the MS
Access
> client? Stored procedures are not an option, I believe, if/when an MS
Access
> form is bound to a query or table (current prevalent scenario in their
db).
> I'm sure reports can be generated from stored procedures just fine and
> therefore processing for reports moved to the server; but what about the
> data entry forms which are bound to tables and/or queries? Stored
procedures
> are not an option there, so what about views? Can MS Access forms be bound
> to SQL Server views? How about bound directly to SQL Server tables? What
can
> I tell them specifically is wrong, in technical terms, with binding an MS
> Access form directly to a SQL Server table?
>
> 3. What other "gotchas" are lurking that I might not be aware of? I would
> think there would be many - but I'd appreciate a few of the important
ones.
>
> I'd like to dodge this bullet altogether but I'm considering getting
> involved because I need the money. Even so... I will refuse get involved
in
> a situation where my reputation is likely to get hurt if it's a totally
> impossible situation from the start (i.e. we could get the back end
> converted and still realize little or no performance improvement). Is this
> scenario a dead-end to begin with given their desire to *not* address the
> fundamental design flaws and rewrite the application and redesign the
> database?
>
> Thanks!
>
>

"Smithers" <A@B.COM> wrote in message
news:%23hS%231juEGHA.648@TK2MSFTNGP14.phx.gbl...
> I have a *potential* client that has an MS Access-based OLTP application.
> They currently have two .mdb files - one for the data that lives on a file
> server, and another .mdb file on each client workstation that contains the
> forms, reports, queries, and modules. They are planning to migrate the
data
> to SQL Server but keep forms etc as a MS Access client application.
>
> The reason they want to migrate the data to SQL Server is specifically to
> enhanse performance.
>
> A few years ago this application had supported about 10 users and now it's
> close to 120 users. IMO, the performance issues aren't necessarily due to
> the size of the database (under 1GB) or number of concurrent users -
> although that's obviously a bit much for MS Access. The real problem is
the
> fundamental design of the database - which contains many tables that are
> more or less denormalized. The in-house developer apparently believed and
> continues to believe that tables *must* (and can only...) directly reflect
> the UI they are supporting. There is really no concept of separating the
> data structures (DDL) from the presentation (i.e., no DAL in the middle).
> Duplicate data and similarly expected problems are prevalent.
>
> The in-house developer believes, and has told his management, that simply
> moving the data from Access to SQL Server will improve performance
> significantly. They do not have the budget or in-house technical resources
> to completely rewrite the system which, IMO, is going to be necessary
sooner
> or later. For now they are hoping that moving the data to SQL Server and
> "throwing hardware at it" will be good enough to result in acceptable
> performance - without any attempt to get rid of the MS Access front end or
> any significant redesign of the database. And yes, they already have
> reasonably good indexes in their tables - meaning that their current
> performance is about the best they can possibly have unless they do a
major
> redesign and rewrite.
>
> I need to be able to talk in specific terms with the management and
in-house
> technical staff about the pros and cons of their plans. While I can talk
> about the db design issues and application architecture, I've never dealt
> with an Access client to a SQL Server db because I always thought it was
> simply a bad practice from the beginning and never really thought through
it
> much less actually worked with that scenario...
>
> thus I have a few questions:
>
> 1. I am wondering what the biggest technical issues are (i.e. reasons
doing
> this is a bad idea). Does this arrangement (Access client to SQL Server
db)
> fail to make use of SQL Server's available locking and other concurrency
> mechanisms? Does this scenario basically result in a [file server
> architecture] with queries processed on the client and not on the server
> (because MS Access is at the front end)? What else?
>
> 2. What can we do to move processing to the server and out of the MS
Access
> client? Stored procedures are not an option, I believe, if/when an MS
Access
> form is bound to a query or table (current prevalent scenario in their
db).
> I'm sure reports can be generated from stored procedures just fine and
> therefore processing for reports moved to the server; but what about the
> data entry forms which are bound to tables and/or queries? Stored
procedures
> are not an option there, so what about views? Can MS Access forms be bound
> to SQL Server views? How about bound directly to SQL Server tables? What
can
> I tell them specifically is wrong, in technical terms, with binding an MS
> Access form directly to a SQL Server table?
>
> 3. What other "gotchas" are lurking that I might not be aware of? I would
> think there would be many - but I'd appreciate a few of the important
ones.
>
> I'd like to dodge this bullet altogether but I'm considering getting
> involved because I need the money. Even so... I will refuse get involved
in
> a situation where my reputation is likely to get hurt if it's a totally
> impossible situation from the start (i.e. we could get the back end
> converted and still realize little or no performance improvement). Is this
> scenario a dead-end to begin with given their desire to *not* address the
> fundamental design flaws and rewrite the application and redesign the
> database?
>
> Thanks!
>
>

"Albert D.Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in message
news:%23W3mVMwEGHA.3856@TK2MSFTNGP12.phx.gbl...
>> The in-house developer apparently believed and continues to believe that
>> tables *must* (and can only...) directly reflect the UI they are
>> supporting. There is really no concept of separating the data structures
>> (DDL) from the presentation (i.e., no DAL in the middle). Duplicate data
>> and similarly expected problems are prevalent.
>
> Don't confuse the issue of binding forms to a table and that of building
> a middle tier of business rules and functions. They are SEPARATE
> issue. Don't try and assassinate the developer because they used a 2
> tier approach, and don't have a middle tier of business rules. For the
> most
> part, and applications that only have 150 users, a two tier design will
> perform very fine, and the fact of NOT having a middle tier is NOT
> a big deal. sql server will not even break out a sweat with 150 users
> in two tier anyway.
>
> The fact of duplicate data is complete separate from the
> fact that no middle tier exists. In fact, one of the "main" reasons
> why often a middle tier is built is because programming languages
> like t-sql are not great for developing complex code.
>
> You can't possibility expect to build
> and debug a bunch of payroll code in t-sql. Hence, a middle
> tier is built with a decent language, and a deceit programming
> environment. However, since ms-access IS A decent
> development platform, then the need to use a middle layer
> does not arise nearly as often as compared to using a
> browser based application (you have difficult to use
> t-sql, and then you have nothing available the client side
> except a browser. In these cases, you really were forced to use a middle
> layer with a nice code debugger etc.).
>
> I hate to bring this up, but now that sql server will consume .net code, I
> am going to be one happy guy. Remember, I don't have 300 users, and
> I can afforded the luxury of using server side code now (but, this is a
> issue for another day).
>
> ).
>
> Anyway, I just want you to be clear that the fact of duplicate
> data, and using bound forms is a separate issue from the
> fact that the developer did not use a middle tier. Looks to
> me like you are looking for a argument to shoot down
> the fact of no middle tier. Sure, there many reasons to
> have a middle tier, but the fact of duplicate data in the
> current application has NOTHING to do with this issue
> except that you have duplicate data!!! Using a middle
> tier will NOT fix this problem!
>
> Anyway, since user counts of < 150 users represents likely
> 99.9% of applications, then a two tired design approach
> of using ms-access  covers most of the marketplace and
> there is nothing wrong with using a 2 tier design for most
> application. Again, don't confuse the issue of duplicate
> data, and that of a middle tier.
>
>>
>> The in-house developer believes, and has told his management, that simply
>> moving the data from Access to SQL Server will improve performance
>> significantly.
>
> Moving a VB, or c++, or a ms-access (they are all simply development
> tools)
> will not of its self improvement performance.
>
> While a few posters here has said that moving a ms-access application to
> sql
> server is a big job, I tend to disagree. About 97%, or even higher of the
> existing
> code will work. Further, if the application is designed reasonably well
> now,
> then
> the amount of work to move the back end to sql server is not a lot. So,
> much
> of the difficulty of moving the back end data to sql server really depends
> on
> how server friendly the existing design is.
>
>> For now they are hoping that moving the data to SQL Server and "throwing
>> hardware at it" will be good enough to result in acceptable performance
>
> Just moving the application will not fix performance, nor improve it.
>
>
>>- without any attempt to get rid of the MS Access front end
>
> Ah..the Darth Vader comes out now!!! As mentioned, the performance
> difference
> between c++, or VB, or ms-access IS NOT GOING TO BE ANY DIFFERENT HERE!!!
> Lets keep the ms-access haters out of this argument. As I mentioned, a 150
> users with MS-access is not even going to cause sql server to break out
> into
> a sweat. There are companies RIGHT NOW that have 1000 user seat counts
> using
> ms-access to sql server.
>
> So, don't necessary assume that you have to throw out ms-access here.
> Ms-access make a fine client to sql sever, or oracle for that matter.
>
>>or any significant redesign of the database. And yes, they already have
>>reasonably good indexes in their tables - meaning that their current
>>performance is about the best they can possibly have unless they do a
>>major
>>redesign and rewrite.
>
> Ok, now the above makes reasonable sense. The one issue here is that as a
> stop gap, the data could be moved to sql server, and the current front end
> continues to be used. Performance bottle necks can be identified over
> time, and
> they can be converted to stored (t-sql) procedures on the server side. As
> I
> mentioned before, there is going to be no performance difference if you
> write this
> application in VB, c++, or keep it in ms-access.
>
> Bound forms in ms-access perform just fine with sql server. This is a long
> as the current design was built with limiting the amount of data that is
> transferred to a form. Remember, even in your current file share system,
> ms-access does NOT transfer a whole table into form to load one record.
> There is a common myth that ms-access and a JET file share pulls the whole
> table. I think you can rapidly see that the current application would not
> function as well as it does if that was the case.
>
> So, the goal, and idea of moving the data to sql server is a good one. As
> for keeping the front end in ms-access, this can be a fine solution also.
>
> Also, ms-access now has some neat share point features, and is
> going to have even more support in the next version. So, as a
> business tool, don't be so hasty to throw out access here.
>
>>
>> I need to be able to talk in specific terms with the management and
>> in-house technical staff about the pros and cons of their plans. While I
>> can talk about the db design issues and application architecture, I've
>> never dealt with an Access client to a SQL Server db because I always
>> thought it was simply a bad practice from the beginning and never really
>> thought through it much less actually worked with that scenario...
>
> Yes, as mentioned, ms-access make a fine client to sql sever. Why would it
> be worse then c++, or vb? You have this nice data engine at your
> disposal..and it does not care if the client is access, or c++. (how does
> sql server even know!!!).
>
>> 1. I am wondering what the biggest technical issues are (i.e. reasons
>> doing this is a bad idea). Does this arrangement (Access client to SQL
>> Server db) fail to make use of SQL Server's available locking and other
>> concurrency mechanisms? Does this scenario basically result in a [file
>> server architecture] with queries processed on the client and not on the
>> server (because MS Access is at the front end)? What else?
>
> Sure, some sql is processed local side. But, so what? I mean, if you have
> a
> table with 100,000 records in it, if you ask for one record, both the file
> share, and sql server will only return one record (so, I leave it to you
> as
> to "why" sql server scales better then a JET file share..but not that both
> are only grabbing one record across the network in this case!!). . There
> is
> *certainly* some cases when JET does a poor job, and the requests to sql
> server are not the best. However, in those cases, the solution is to
> simply
> use a pass-through query (that means the query in ms-access is sent
> un-touched by jet to sql server). In other cases, simple binding a view to
> a
> linked table does the trick (so, the join of data occurs server side....).
> So, for the most part, the fact that some sql processing, and syntax
> checking
> occurs local side is a good thing. As for the locking, and concurrency
> issues..why would using a asp web page, or VB, or ms-access behave any
> different in this regards? The locking and concurrency issues become that
> of
> the database system you choose...not the client. SQL server does not know,
> or care you are grabbing data with ms-access, or a ASP web page. The
> performance, and process is essentially the same.
>
>>
>> 2. What can we do to move processing to the server and out of the MS
>> Access client? Stored procedures are not an option
>
> Why are not stored procedures an option? How do you expect to gain any
> server side processing of data if you don't use stored proceeds?
> (you again seem to be forming these arguments to give yourself a win in
> building that middle tier).
>
>>, I believe, if/when an MS Access form is bound to a query or table
>>(current prevalent scenario in their db). I'm sure reports can be
>>generated
>>from stored procedures just fine and therefore processing for reports
>>moved
>>to the server; but what about the data entry forms which are bound to
>>tables and/or queries?
>
> You have not yet explained why the above is a problem? Why is the above
> a problem issue?
>
>> Stored procedures are not an option there
>
> As mentioned, you are giving up one ability of sql server  to do server
> side processing by elimination this from the mix.
>
>>, so what about views? Can MS Access forms be bound to SQL Server views?
>
> Yes, I mentioned this previous. If the form is bound to simple
> table, then little gains are to be had. However, for combo boxes
> and forms that are the result of a join, then view can make a
> real difference. However, a simple form bound to a table gains
> absolute nothing by using a view in place of that. (why would
> it benefit...for what reason?).
>
> Without question, a form bound to a table, or bound to a view
> need to be *restricted* to one record. So, if forms typically
> now use the where clause to "load to" one record, then
> this approach also works well with sql server. 99% of my
> forms (for JET based, or sql server based) applications
> have a where clause. This simply means that the form
> opens to ONE record..You edit...and then close the form
> to save the data. This also means that very little data is
> transferred to the form.  For example, how is a search
> done now.  I talk about a approach and solution that
> works well in JET or sql server using ms-access here:
>
> http://www.members.shaw.ca/AlbertKallal/Search/index.html
>
>
>>How about bound directly to SQL Server tables? What can I tell them
>>specifically is wrong, in technical terms, with binding an MS Access form
>>directly to a SQL Server table?
>
> There is nothing wrong with the above. As long as the form is opened with
> a where clause to respect records loaded, for the most part the above is
> a happy, and cost effective solution (those bound forms save HUGE amounts
> of developer time).
>
>> impossible situation from the start (i.e. we could get the back end
>> converted and still realize little or no performance improvement).
>
> If you make NO changes to the front end, the yes, for the most part
> some things will most certainly run slower (however, a VB developer
> would experience the same thing too!!). However, it is a put out the
> fire approach. In other words, you simply "fix" the parts of the
> application
> that perform too slow (such as forms that drag too much data across
> the network). You don't have to re-write the application, and the
> benefits of performance for only 100 users is not going to make, or break
> this (there is other issues of support, and distribution of the software
> here, but performance is NOT going to be the decision that make you drop
> ms-access as the developers tool here).
>
>
> --
> Albert D. Kallal   (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
> http://www.members.shaw.ca/AlbertKallal
>
>
>

"Albert D.Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in message
news:OjKxNSxEGHA.740@TK2MSFTNGP12.phx.gbl...
> >
>> Looking for an argument? Darth Vader? Access haters?? What are you
>> talking about. Relax man! So dramatic! and wrong. I love MS Access! It's
>> just so limited compared to other products and application architectures.
>> Sometimes it's the perfect tool for the job and sometimes it's not.
>
> No, not a big deal. You been very gentleman like and clear. I just wanted
> to break the ice on this issue. If you done work for any amount of
> companies, you will often experience a very hard line against ms-access.
> If this is not the case..then so be it. It is not a "big" deal one way, or
> the other, and I not looking to start a argument here. Just be aware, that
> often there are motives base on emotions...not what is the best approach.
>
>>
>> RE:
>> << About 97%, or even higher of the existing code will work>>
>>
>> Okay, with that statement you lost the remainder of any credibility you
>> may have had. You have never seen the code in question - so you are
>> simply not in a position to know how much of it will work, much less that
>> 97% of it is fine.
>
>
> All I said was based on experience, that about 97% of your code will work
> un-changed if you move the back end to sql server.
>
> What is your experience on this issue?
>
> I don't know, nor have any idea how bad the code is. However, even bad
> code will mostly work with sql server. What exactly was your point here?
> My point is that most of the code does not need to be changed. If you are
> saying the code needs to be changed because it is bad, then that is the
> usual opinion of every developer looking at a new project!! . However, I
> am saying that as a general rule, about 97% of the code will run
> un-changed when you move to sql server. ( I not saying that 97% of the
> code is good, or bad. In fact, it is rather funny that you would not grasp
> my point, and actually think I am so stupid to as be able to read the
> quality of the code!!. Don't be so closed minded here. I am not saying
> that 97% of your code is fine, but I most certainly saying that about 97%
> of the code will run as is, and unchanged. This certainly does not imply
> that the code does (or does not) need to be changed.
>
> What exactly did you think my point was? I would still bet that if you
> moved the linked tables to sql server, about 97% of the existing code
> would work un changed.
>>
>>
>> Re:
>> << The one issue here is that as a stop gap, the data could be moved to
>> sql server >>
>>
>> Your're kidding - right?!?!? (unfortunately I suppose you aren't
>> kidding).
>> Why would I go in as a consultant and recommend a stop gap?!?!?
>
> Because maybe the company can't afford a re-write. Perhaps your clients
> are so perfect, and have unlimited budgets. Unfortunately, the rest of the
> real world is not so lucky. Often, you have to make a compromise. So, in
> place of re-writing the whole application, you as a stop gap move the data
> to sql server, and fix the performance points in the application? I can't
> imagine a consultant would not consider this approach/ Perhaps you are
> dishonest, and simply want to re-write the whole thing. Can you really
> make a case that re-writing the whole thing in place of fixing the
> performance problems with the current application is not a viable
> solution? Perhaps you can make the case already. However, if you can, then
> why are you bothering to ask for advice here? You can't have this argument
> both ways.
>
> So,I am simply suggestions due to budgets and time issues, you might as a
> stop gab move the data to sql sever and continue to run the front end.
> Obviously , this is NOT an ideal situation. Since it is not ideal..then
> what must it be?...well, it must be a stop gap then...right? This solution
> might serve them for a few more years until such time as a "more ideal"
> solution can be built. I see absolute no reason here to get your feathers
> all ruffed up...we just given some suggestions here...nothing
> more...nothing less....
>
>
> Re-read what I suggested....
>
> --
> Albert D. Kallal   (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
> http://www.members.shaw.ca/AlbertKallal
>

> I have a *potential* client that has an MS Access-based OLTP application.
> They currently have two .mdb files - one for the data that lives on a file
> server, and another .mdb file on each client workstation that contains the
> forms, reports, queries, and modules. They are planning to migrate the data
> to SQL Server but keep forms etc as a MS Access client application.
>
> The reason they want to migrate the data to SQL Server is specifically to
> enhanse performance.
>
> A few years ago this application had supported about 10 users and now it's
> close to 120 users. IMO, the performance issues aren't necessarily due to
> the size of the database (under 1GB) or number of concurrent users -
> although that's obviously a bit much for MS Access. The real problem is the
> fundamental design of the database - which contains many tables that are
> more or less denormalized. The in-house developer apparently believed and
> continues to believe that tables *must* (and can only...) directly reflect
> the UI they are supporting. There is really no concept of separating the
> data structures (DDL) from the presentation (i.e., no DAL in the middle).
> Duplicate data and similarly expected problems are prevalent.
>
> The in-house developer believes, and has told his management, that simply
> moving the data from Access to SQL Server will improve performance
> significantly. They do not have the budget or in-house technical resources
> to completely rewrite the system which, IMO, is going to be necessary sooner
> or later. For now they are hoping that moving the data to SQL Server and
> "throwing hardware at it" will be good enough to result in acceptable
> performance - without any attempt to get rid of the MS Access front end or
> any significant redesign of the database. And yes, they already have
> reasonably good indexes in their tables - meaning that their current
> performance is about the best they can possibly have unless they do a major
> redesign and rewrite.
>
> I need to be able to talk in specific terms with the management and in-house
> technical staff about the pros and cons of their plans. While I can talk
> about the db design issues and application architecture, I've never dealt
> with an Access client to a SQL Server db because I always thought it was
> simply a bad practice from the beginning and never really thought through it
> much less actually worked with that scenario...
>
> thus I have a few questions:
>
> 1. I am wondering what the biggest technical issues are (i.e. reasons doing
> this is a bad idea). Does this arrangement (Access client to SQL Server db)
> fail to make use of SQL Server's available locking and other concurrency
> mechanisms? Does this scenario basically result in a [file server
> architecture] with queries processed on the client and not on the server
> (because MS Access is at the front end)? What else?
>
> 2. What can we do to move processing to the server and out of the MS Access
> client? Stored procedures are not an option, I believe, if/when an MS Access
> form is bound to a query or table (current prevalent scenario in their db).
> I'm sure reports can be generated from stored procedures just fine and
> therefore processing for reports moved to the server; but what about the
> data entry forms which are bound to tables and/or queries? Stored procedures
> are not an option there, so what about views? Can MS Access forms be bound
> to SQL Server views? How about bound directly to SQL Server tables? What can
> I tell them specifically is wrong, in technical terms, with binding an MS
> Access form directly to a SQL Server table?
>
> 3. What other "gotchas" are lurking that I might not be aware of? I would
> think there would be many - but I'd appreciate a few of the important ones.
>
> I'd like to dodge this bullet altogether but I'm considering getting
> involved because I need the money. Even so... I will refuse get involved in
> a situation where my reputation is likely to get hurt if it's a totally
> impossible situation from the start (i.e. we could get the back end
> converted and still realize little or no performance improvement). Is this
> scenario a dead-end to begin with given their desire to *not* address the
> fundamental design flaws and rewrite the application and redesign the
> database?
>
> Thanks!

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1136582773.078813.132240@z14g2000cwz.googlegroups.com...
> Smithers wrote:
>> I have a *potential* client that has an MS Access-based OLTP application.
>> They currently have two .mdb files - one for the data that lives on a
>> file
>> server, and another .mdb file on each client workstation that contains
>> the
>> forms, reports, queries, and modules. They are planning to migrate the
>> data
>> to SQL Server but keep forms etc as a MS Access client application.
>>
>> The reason they want to migrate the data to SQL Server is specifically to
>> enhanse performance.
>>
>> A few years ago this application had supported about 10 users and now
>> it's
>> close to 120 users. IMO, the performance issues aren't necessarily due to
>> the size of the database (under 1GB) or number of concurrent users -
>> although that's obviously a bit much for MS Access. The real problem is
>> the
>> fundamental design of the database - which contains many tables that are
>> more or less denormalized. The in-house developer apparently believed and
>> continues to believe that tables *must* (and can only...) directly
>> reflect
>> the UI they are supporting. There is really no concept of separating the
>> data structures (DDL) from the presentation (i.e., no DAL in the middle).
>> Duplicate data and similarly expected problems are prevalent.
>>
>> The in-house developer believes, and has told his management, that simply
>> moving the data from Access to SQL Server will improve performance
>> significantly. They do not have the budget or in-house technical
>> resources
>> to completely rewrite the system which, IMO, is going to be necessary
>> sooner
>> or later. For now they are hoping that moving the data to SQL Server and
>> "throwing hardware at it" will be good enough to result in acceptable
>> performance - without any attempt to get rid of the MS Access front end
>> or
>> any significant redesign of the database. And yes, they already have
>> reasonably good indexes in their tables - meaning that their current
>> performance is about the best they can possibly have unless they do a
>> major
>> redesign and rewrite.
>>
>> I need to be able to talk in specific terms with the management and
>> in-house
>> technical staff about the pros and cons of their plans. While I can talk
>> about the db design issues and application architecture, I've never dealt
>> with an Access client to a SQL Server db because I always thought it was
>> simply a bad practice from the beginning and never really thought through
>> it
>> much less actually worked with that scenario...
>>
>> thus I have a few questions:
>>
>> 1. I am wondering what the biggest technical issues are (i.e. reasons
>> doing
>> this is a bad idea). Does this arrangement (Access client to SQL Server
>> db)
>> fail to make use of SQL Server's available locking and other concurrency
>> mechanisms? Does this scenario basically result in a [file server
>> architecture] with queries processed on the client and not on the server
>> (because MS Access is at the front end)? What else?
>>
>> 2. What can we do to move processing to the server and out of the MS
>> Access
>> client? Stored procedures are not an option, I believe, if/when an MS
>> Access
>> form is bound to a query or table (current prevalent scenario in their
>> db).
>> I'm sure reports can be generated from stored procedures just fine and
>> therefore processing for reports moved to the server; but what about the
>> data entry forms which are bound to tables and/or queries? Stored
>> procedures
>> are not an option there, so what about views? Can MS Access forms be
>> bound
>> to SQL Server views? How about bound directly to SQL Server tables? What
>> can
>> I tell them specifically is wrong, in technical terms, with binding an MS
>> Access form directly to a SQL Server table?
>>
>> 3. What other "gotchas" are lurking that I might not be aware of? I would
>> think there would be many - but I'd appreciate a few of the important
>> ones.
>>
>> I'd like to dodge this bullet altogether but I'm considering getting
>> involved because I need the money. Even so... I will refuse get involved
>> in
>> a situation where my reputation is likely to get hurt if it's a totally
>> impossible situation from the start (i.e. we could get the back end
>> converted and still realize little or no performance improvement). Is
>> this
>> scenario a dead-end to begin with given their desire to *not* address the
>> fundamental design flaws and rewrite the application and redesign the
>> database?
>>
>> Thanks!
>
> I agree with most of what's been said. Moving to SQL Server without a
> redesign is a recipe for failure. Walk away if they don't listen.
>
> One "positive" aspect for you may be that their logical data model is
> lousy (lamentably true of pretty much every Access database I've ever
> seen). Given their rate of expansion they may sooner rather than later
> discover that data integrity has suffered and that the system can't
> support new demands for management information. At that point there
> will be a business case for a clean sweep.
>
> --
> David Portas
> SQL Server MVP
> --
>

"Smithers" <A@B.COM> wrote in message
news:%23hS%231juEGHA.648@TK2MSFTNGP14.phx.gbl...
> I have a *potential* client that has an MS Access-based OLTP application.

> A few years ago this application had supported about 10 users and now it's
> close to 120 users. IMO, the performance issues aren't necessarily due to
> the size of the database (under 1GB) or number of concurrent users -
> although that's obviously a bit much for MS Access. The real problem is
the
> fundamental design of the database - which contains many tables that are
> more or less denormalized.

> The in-house developer believes, and has told his management, that simply
> moving the data from Access to SQL Server will improve performance
> significantly. They do not have the budget or in-house technical resources
> to completely rewrite the system which, IMO, is going to be necessary
sooner
> or later. For now they are hoping that moving the data to SQL Server and
> "throwing hardware at it" will be good enough to result in acceptable
> performance - without any attempt to get rid of the MS Access front end or
> any significant redesign of the database.

"wolfman" <wolfman9***@hotmail.com> wrote in message
news:LVBvf.12860$ka.6753@tornado.socal.rr.com...
>
> "Smithers" <A@B.COM> wrote in message
> news:%23hS%231juEGHA.648@TK2MSFTNGP14.phx.gbl...
>> I have a *potential* client that has an MS Access-based OLTP application.
>
>> A few years ago this application had supported about 10 users and now
>> it's
>> close to 120 users. IMO, the performance issues aren't necessarily due to
>> the size of the database (under 1GB) or number of concurrent users -
>> although that's obviously a bit much for MS Access. The real problem is
> the
>> fundamental design of the database - which contains many tables that are
>> more or less denormalized.
>
>> The in-house developer believes, and has told his management, that simply
>> moving the data from Access to SQL Server will improve performance
>> significantly. They do not have the budget or in-house technical
>> resources
>> to completely rewrite the system which, IMO, is going to be necessary
> sooner
>> or later. For now they are hoping that moving the data to SQL Server and
>> "throwing hardware at it" will be good enough to result in acceptable
>> performance - without any attempt to get rid of the MS Access front end
>> or
>> any significant redesign of the database.
>
> Others have addressed the technical issues, but you are looking at
> business
> issues here. The system has gone from 10 to 120 users, the design is
> crappy,
> and they do not have the budget to really fix it right. Meaning, they
> aren't
> motivated to fix it right. They want cheap rather than good, correct, and
> workable in the long run -- which is really where "cheap" is in business
> systems. They have their priorities upside down and they want you to work
> a
> miracle for nothing.
>
> Let me tell you who this potential client really is -- Migraine Headache
> Industries.
>
>

"Smithers" <A@B.COM> wrote in message
news:%23hS%231juEGHA.648@TK2MSFTNGP14.phx.gbl...
> I have a *potential* client that has an MS Access-based OLTP application.
> They currently have two .mdb files - one for the data that lives on a file
> server, and another .mdb file on each client workstation that contains the
> forms, reports, queries, and modules. They are planning to migrate the
data
> to SQL Server but keep forms etc as a MS Access client application.
>
> The reason they want to migrate the data to SQL Server is specifically to
> enhanse performance.
>
> A few years ago this application had supported about 10 users and now it's
> close to 120 users. IMO, the performance issues aren't necessarily due to
> the size of the database (under 1GB) or number of concurrent users -
> although that's obviously a bit much for MS Access. The real problem is
the
> fundamental design of the database - which contains many tables that are
> more or less denormalized. The in-house developer apparently believed and
> continues to believe that tables *must* (and can only...) directly reflect
> the UI they are supporting. There is really no concept of separating the
> data structures (DDL) from the presentation (i.e., no DAL in the middle).
> Duplicate data and similarly expected problems are prevalent.
>
> The in-house developer believes, and has told his management, that simply
> moving the data from Access to SQL Server will improve performance
> significantly. They do not have the budget or in-house technical resources
> to completely rewrite the system which, IMO, is going to be necessary
sooner
> or later. For now they are hoping that moving the data to SQL Server and
> "throwing hardware at it" will be good enough to result in acceptable
> performance - without any attempt to get rid of the MS Access front end or
> any significant redesign of the database. And yes, they already have
> reasonably good indexes in their tables - meaning that their current
> performance is about the best they can possibly have unless they do a
major
> redesign and rewrite.
>
> I need to be able to talk in specific terms with the management and
in-house
> technical staff about the pros and cons of their plans. While I can talk
> about the db design issues and application architecture, I've never dealt
> with an Access client to a SQL Server db because I always thought it was
> simply a bad practice from the beginning and never really thought through
it
> much less actually worked with that scenario...
>
> thus I have a few questions:
>
> 1. I am wondering what the biggest technical issues are (i.e. reasons
doing
> this is a bad idea). Does this arrangement (Access client to SQL Server
db)
> fail to make use of SQL Server's available locking and other concurrency
> mechanisms? Does this scenario basically result in a [file server
> architecture] with queries processed on the client and not on the server
> (because MS Access is at the front end)? What else?
>
> 2. What can we do to move processing to the server and out of the MS
Access
> client? Stored procedures are not an option, I believe, if/when an MS
Access
> form is bound to a query or table (current prevalent scenario in their
db).
> I'm sure reports can be generated from stored procedures just fine and
> therefore processing for reports moved to the server; but what about the
> data entry forms which are bound to tables and/or queries? Stored
procedures
> are not an option there, so what about views? Can MS Access forms be bound
> to SQL Server views? How about bound directly to SQL Server tables? What
can
> I tell them specifically is wrong, in technical terms, with binding an MS
> Access form directly to a SQL Server table?
>
> 3. What other "gotchas" are lurking that I might not be aware of? I would
> think there would be many - but I'd appreciate a few of the important
ones.
>
> I'd like to dodge this bullet altogether but I'm considering getting
> involved because I need the money. Even so... I will refuse get involved
in
> a situation where my reputation is likely to get hurt if it's a totally
> impossible situation from the start (i.e. we could get the back end
> converted and still realize little or no performance improvement). Is this
> scenario a dead-end to begin with given their desire to *not* address the
> fundamental design flaws and rewrite the application and redesign the
> database?
>
> Thanks!
>
>