What Are The Issues With MS Access Client to SQL Server DB

[Smithers]

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!

 

[Roger Carlson]

Simply moving a poorly designed application to SQL Server WILL NOT improve
performance.

One example is an Access form that has a table as it's record source and is
then FILTERED to see only one record. Whether that table is in Access or
SQL Server, the whole table is going to have to be dragged across the
network. No performance gain. Many poorly designed applications use
exactly this process. There are many, similar issues.

I would direct your attention to the following book: Microsoft Access
Developer's Guide to SQL Server by Andy Baron, Mary Chipman . It talks
specifically about these issues and solutions for them.

When I looked this up on Amazon (to make sure it was still in print) I also
found this:Client/Server Programming with Access & SQL Server: The
Integrated Guide for Programmers & Developers by Leo Sanin, Renzhong Chen.
I cannot vouch for it, but the description looks good.

Note: pointing to a published book might have more weight than your opinions
(or opinions from a newsgroup) alone.

Whether you decide to accept this job or not is up to you. On the one hand,
you will gain an awful lot of experience upgrading to SQL Server. This is
especially true for a poorly designed database. You always learn far more
when there are a lot of problems than when everything goes smoothly.

However, if the client refuses to even consider a database design change,
I'd hesitate. Even if I you were not moving to SQL Server, chances are that
SOME aspect of their business model has changed over the years. This is a
great time to bring it all up to speed.

Bottom line? I see FAILURE written all over this project, but you might
learn a great deal by it, which won't be a failure for you personally. Nor
should it necessarily reflect badly on you if it does fail, especially if
they ignore your specific advice.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

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!

 

[Ron Hinds]

I did almost the exact same type of project - i.e., migrating an Access-only
app to and Access-SQL Server app (Access 97). I can tell you for sure that
you won't get a performance improvement simply by migrating the back-end to
SQL Server. In fact, performance will probably be considerably worse without
a lot of work to the front-end. However, once that work is complete, you can
expect tremendous gains in performance. It took me 6 months to accomlish
this (the app in question is quite large). The bottom line is Access is
designed such that the processing all occurs on the client side. In order to
gain performance by going to SQL Server, you will need most of the
processing to go on on the server side. This is possible - but as I said it
will require considerable work on the client code. Let me try to answer your
qustions inline.

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?

Not necessarily. As I said above, Access apps can be built that utilize the
strengths of SQL Server. The biggest technical hurdle will be converting the
bound forms to use local temp tables, which you populate via SQL
Pass-Through queries. Then when data needs to be updated, etc., again you
will use SQL Pass-Through queries.

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)?

Yes, if you make no changes to the front-end code.

What else?

The forms bound to server tables can cause locking issues with SQL Server.

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

You are correct here. You need to change the forms to be bound to local temp
tables.

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?

Yes, they can be bound to views. The gotcha here is that the views must have
unique indexes or Access will consider them not updatable. Views are treated
just like other linked tables in Access.

How about bound directly to SQL Server tables?
Yes.

What can
I tell them specifically is wrong, in technical terms, with binding an MS
Access form directly to a SQL Server table?

Locking issues are what I ran into with this. Access (97 anyway) tends to
lock entire pages of data when forms are bound directly to the server
tables.

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 think we've already touched on the main ones above.

In my situation, I also was prevented from making changes to the database
(i.e. backend) design (although it wasn't nearly as problematic as yours
sounds). But by reworking the front-end I was able to make massive
performance gains. Another poster recommended the book Microsoft Access
Developer's Guide to SQL Server by Chipman and Baron. I relied heavily on
that book to do my conversion - you NEED this book if you are going to take
on this project.

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]

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

 

[David Portas]

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.

 

[Smithers]

RE:
<< Looks to me like you are looking for a argument to shoot down the fact of
no middle tier. >>
<< Ah..the Darth Vader comes out now!!! >>
<< Lets keep the ms-access haters out of this argument>>

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.

Re:
<< Again, don't confuse the issue of duplicate data, and that of a middle
tier>>

What lead you to believe I was doing that? I raised a bunch of concerns.
Looks like you're just looking for a springboard to vent godonlyknowswhat.
Glad I could provide that for you (I guess).

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.


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?!?!?

Re:
<< If you make NO changes to the front end, the yes, for the most part some
things will most certainly run slower >>
<< Just moving the application will not fix performance, nor improve it >>

So, you agree with the concerns raised in my OP then. Geeze!

Relax man - go get laid or something; have a beer; go for a walk; take ten
deep breaths; something - ANYTHNIG! : )

Access MVP! Wow! You're rantings don't do much to further the cause of MS
Access, nor your own opinions. You're style gives MS Access MVPs a bad name.

I would strongly suggest that you study Celko's postings in the SQL Server
group. He does a great job at offending people - but at least *most* of what
he says is factually based, accurate, and can be backed up; not every time,
but most of it. So while we dislike his style we listen (or at least some of
us do who can get past the offensive style) and learn a bunch from the guy.
Maybe you could pick it up on the *rational* side if you want to have some
credibility while remaining so apparently hostile or defensive or whatever
other description can be reasonably attached to your emotional writings.

-Have a good weekend (if you can)

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
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Sylvain Lafontaine]

Yeah, I've seen this kind of clients in the past: they don't mind spending
25000$ on new hardware with SQL-Server or 75000$ a year for an in-house
programmer but they do mind spending 5000$ for an external programmer.

For them, buying a new server is like bying a new car for 25000$: it will
work because it's new and it costs 25000$; hence the concept that bringing
SQL Server without making anything else will improve performance
significantly by its sole presence.

With 120 users, a company of this size should have a pocket of sufficient
depth to pay for the redesign of this stuff; even if they need to apply for
a loan. If they wanted to buy a new card, they wouldn't ask the seller to
give it for almost free because they don't want to pay for it; why should it
be different with their core business situation?

They don't have the budget to fix it? Common! Stop whining and go make a
loan!

 

[Albert D.Kallal]

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

 

[Smithers]

Re:
<< we just given some suggestions here...nothing more...nothing less >>

Actually, you gave a lot more and a lot less.

<< I just wanted to break the ice on this issue >>
With your Darth Vader comments and talk about "Access haters".... you
accomplished in creating a whole lot of new ice - you didn't breaking any.

Most MVPs I've ever seen help to promote peace and harmony in the NGs. You
are the first I've seen in many many years (and many many NGs) who is
actually offensive.

Again, I can't believe you are an MVP. If you really are, then you should
not be. You are certainly tarnishing the reputation of MVPs and I expect
many would distance themselves from you and your outbursts and name calling.

Have a good weekend (if you can)

 

[Albert D.Kallal]

Re:
<< we just given some suggestions here...nothing more...nothing less >>

Actually, you gave a lot more and a lot less.

<< I just wanted to break the ice on this issue >>
With your Darth Vader comments and talk about "Access haters".... you
accomplished in creating a whole lot of new ice - you didn't breaking any.

Well, I really thought nothing much of the issue. And certainly the use of
access in the corporate environment has often been a issue here.

I see little harm, or issue of stating the "dark force". Perhaps I should
had said that:

Often, some IT departments really have it out for ms-access, and are
trying to get rid of ms-access.

I just used the term Darth Vader....really, not a big deal...and kind of
like a crumb falling off of a cake.

If you found that term off base, or something I should not have used, then I
really do apologize. I certainly meant noting personal, or not to offend
anyone
here.

I grew up watching and loving star wars..and I did not really think the term
was out of line. I don't believe I made any kind of personal attack here,
and
I can certainly say I had no special motives here.

However, obviously you do feel offended, and thus I do apologize. I meant no
harm here.

Simply put, we often see people bashing the product ms-access. Many
companies want to get rid of ms-access, and I meant nothing more then
pointing out this fact. It has become a really sad day if this kind of
attitude can't be point out. It does remain that some IT people and
consultants really have it out for ms-access. I wish this was not so. You
cleared up this was not the issue in your case, so then why not move on?
(or,
perhaps it still is a issue with you - you do seem to take offense to
anything that supports the use of ms-access in this project - you seem
HYPER sensitive on this issue..and one could ask why??).

Anyway, Once again, if you found my use of the term "Darth Vader" like
offensive, then I do apologize. I can assure you I meant nothing personal,
or
in any want to offend you, or anyone reading here.

many would distance themselves from you and your outbursts and name
calling.

We need to keep open minds here. And, as for name calling, it seems you
outright complete miss judged, or miss understood my comments about 97% of
the code. Talk about a outburst here!!

Anyway, I still stand on the suggestions and issues I have pointed out to
you.

You may be very well correct that ms-access is not the solution here
anymore..

On the other hand, you should be open minded to the issue that ms-access
might very well continue to be a excellent solution here..and it is this
point of view that you seem unwilling to entertain in any way, or shape.

For better, or worse, a lot of people are going to tell you that
ms-acces is a great solution for you current system.

As yoda would say...

good perhaps access is....hum....?

 

[Smithers]

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.

Thank you David and others for your thoughtful responses.

 

[Roger Carlson]

In addition to the Baron/Chipman book, the Access Developer's Handbook has
helpful information about making your application run faster. In fact,
here's a free exerpt:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

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!