Top 10 Issues with mdb front and SQLServer back end

[John Welch]

I have a potential client who wants an application with about 20,000 records
and 5-8 users. I told him that Access would work fine, but he wants to go
with an mdb front end linked to SQL Server tables. My experience is solely
in Access (single user) and I'm trying to decide whether to take the job or
pass. I assume I'd just create the tables in Access and then upsize them
with the wizard to get started. After that I don't how much the development
style would be different from just a normal Access application. What I'm
looking for is something like "The top 10 (or 20) most important things to
do differently when developing a multiuser mdb with a SQL Server backend."
Can anyone point me to a relevant document or book or just give me some
ideas to think about? I have the Access 2000 developer's handbook, which I
love for single user stuff, but which I'm having a hard time following for
the client/server stuff.

[David W. Fenton]

"John Welch" <jw****@fred.com> wrote in
news:d9********@enews2.newsguy.com:

I have a potential client who wants an application with about
20,000 records and 5-8 users. I told him that Access would work
fine, but he wants to go with an mdb front end linked to SQL
Server tables. My experience is solely in Access (single user) and
I'm trying to decide whether to take the job or pass. I assume I'd
just create the tables in Access and then upsize them with the
wizard to get started. After that I don't how much the development
style would be different from just a normal Access application.
What I'm looking for is something like "The top 10 (or 20) most
important things to do differently when developing a multiuser mdb
with a SQL Server backend." Can anyone point me to a relevant
document or book or just give me some ideas to think about? I have
the Access 2000 developer's handbook, which I love for single user
stuff, but which I'm having a hard time following for the
client/server stuff.

Two things:

1. read the sections on optimizing multi-user apps in the
appropriate ADH version, and then the Enterprise sections, which
deal with using Access with SQL Server.

2. if you can get it (it's out of print) try to acquire a copy of
SQL Server for Access Developers. It's a great book.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Wed, 29 Jun 2005 15:10:03 -0700, "John Welch" <jw****@fred.com> wrote:

I have a potential client who wants an application with about 20,000 records
and 5-8 users. I told him that Access would work fine, but he wants to go
with an mdb front end linked to SQL Server tables. My experience is solely
in Access (single user) and I'm trying to decide whether to take the job or
pass. I assume I'd just create the tables in Access and then upsize them
with the wizard to get started. After that I don't how much the development
style would be different from just a normal Access application. What I'm
looking for is something like "The top 10 (or 20) most important things to
do differently when developing a multiuser mdb with a SQL Server backend."
Can anyone point me to a relevant document or book or just give me some
ideas to think about? I have the Access 2000 developer's handbook, which I
love for single user stuff, but which I'm having a hard time following for
the client/server stuff.

Here's my 2c in order of importance - most important first.

1. Keep result sets small either by filtering with where clauses or by
agregating with group by clauses, or both. Filtering forms, and limiting
subform results by master/detail relationships does -not- do this.

2. Unless your back-end will be shared by multiple applications, ignore any
advice you might find saying that all your queries should be implemented as
stored procedures. Doing that might gain you some performance here and there,
but the extra complexity will greatly slow, impair your user interface, and
lead to more costly bugs. It is also -not- tru that Access must read all the
records from the server to perfrom a filter or a join - JET passes these
things to the server unless you abuse function calls that can't be passed to
the server.

3. Except for simple lookup tables, all your tables should have IDENTITY
columns. This will improve update performance, prevent false update conflict
errors, and prevent users from inadvertently overwriting each other's long
text field updates with no warning or error messages.

4. Tables should have either no clustered index or use the primary key as a
clustered index. If you have good reason to use a clustered index that is
-not- the primary key, link to a view of that table instead of linking to the
table directly, and specify the primary key field when linking.

5. In the odd cases where you do need to move a query to the server for
performance reasons, use views when you can, and only use stored procedures
where views won't do the job.

6. Don't bother with any up-sizing wizard. To do it right, you'll have to do
it yourself. With scripts and code to do your upsizing steps, so you don't
have to re-do them manually if you have to start over. The steps are...

a. Create tables with no keys or indexes
b. Link tables form Access
c. Insert data into tables using INSERT queries from Access
d. Create indexes and primary keys on tables
e. Relink tables in Access
f. Create triggers and declared relationships

You can use scripts for a, d, and f. If you want, you can drive the whole
thing from Access using a series of pass-through queries in place of each
script.

[Trevor Best]

Steve Jorgensen wrote:

2. Unless your back-end will be shared by multiple applications, ignore any
advice you might find saying that all your queries should be implemented as
stored procedures. Doing that might gain you some performance here and there,
but the extra complexity will greatly slow, impair your user interface, and
lead to more costly bugs. It is also -not- tru that Access must read all the
records from the server to perfrom a filter or a join - JET passes these
things to the server unless you abuse function calls that can't be passed to
the server.
Abuse isn't an issue, you may simply have a function in a query like
Nz(), the equivelant in T-SQL is Isnull(), which is also the name of a
different function in Access. This makes translation to T-SQL very
difficult.
3. Except for simple lookup tables, all your tables should have IDENTITY
columns. This will improve update performance, prevent false update conflict
errors, and prevent users from inadvertently overwriting each other's long
text field updates with no warning or error messages.
Don't you mean timestamp columns?
6. Don't bother with any up-sizing wizard. To do it right, you'll have to do
it yourself. With scripts and code to do your upsizing steps, so you don't
have to re-do them manually if you have to start over. The steps are...

a. Create tables with no keys or indexes
b. Link tables form Access
c. Insert data into tables using INSERT queries from Access
d. Create indexes and primary keys on tables
e. Relink tables in Access
f. Create triggers and declared relationships

You can use scripts for a, d, and f. If you want, you can drive the whole
thing from Access using a series of pass-through queries in place of each
script.

You won't be able to do c without a PK or unique index on the table,
Access will treat it as read-only.

--
[OO=00=OO]

[Steve Jorgensen]

On Thu, 30 Jun 2005 08:31:27 +0100, Trevor Best <no****@besty.org.uk> wrote:

Steve Jorgensen wrote:

2. Unless your back-end will be shared by multiple applications, ignore any
advice you might find saying that all your queries should be implemented as
stored procedures. Doing that might gain you some performance here and there,
but the extra complexity will greatly slow development, impair your user
interface, and lead to more costly bugs. It is also -not- tru that Access
must read all the records from the server to perfrom a filter or a join - JET
passes these things to the server unless you abuse function calls that can't
be passed to the server.

Abuse isn't an issue, you may simply have a function in a query like
Nz(), the equivelant in T-SQL is Isnull(), which is also the name of a
different function in Access. This makes translation to T-SQL very
difficult.

"Abuse" means using it somewhere that must be processed prior to the output
stage. In other words, it's fine to say "Select nz(Sum([xyz],0) ...", but
it's not fine to say "Select Sum(nz([xyz],0)) ...". Likewise, even
"nz(Sum([xyz],0)" in a HAVING clause will make Access process extra rows,
though it might be fine where the unfiltered set will always be small anyway.

3. Except for simple lookup tables, all your tables should have IDENTITY
columns. This will improve update performance, prevent false update conflict
errors, and prevent users from inadvertently overwriting each other's long
text field updates with no warning or error messages.

Don't you mean timestamp columns?

Yes - thanks for the correction.

6. Don't bother with any up-sizing wizard. To do it right, you'll have to do
it yourself. Use scripts and code to do your upsizing steps, so you don't
have to re-do them manually if you have to start over. The steps are...

a. Create tables with no keys or indexes
b. Link tables form Access
c. Insert data into tables using INSERT queries from Access
d. Create indexes and primary keys on tables
e. Relink tables in Access
f. Create triggers and declared relationships

You can use scripts for a, d, and f. If you want, you can drive the whole
thing from Access using a series of pass-through queries in place of each
script.

You won't be able to do c without a PK or unique index on the table,
Access will treat it as read-only.

Not true - what you can't do is update data in the tables using a recordset.
INSERT queries work just fine, though. I assure you I have used this process
on several occasions, and it works perfectly (also note that "e" and "f" may
be reversed if desired).

[Bri]

John Welch wrote:

I have a potential client who wants an application with about 20,000 records
and 5-8 users. I told him that Access would work fine, but he wants to go
with an mdb front end linked to SQL Server tables. My experience is solely
in Access (single user) and I'm trying to decide whether to take the job or
pass. I assume I'd just create the tables in Access and then upsize them
with the wizard to get started. After that I don't how much the development
style would be different from just a normal Access application. What I'm
looking for is something like "The top 10 (or 20) most important things to
do differently when developing a multiuser mdb with a SQL Server backend."
Can anyone point me to a relevant document or book or just give me some
ideas to think about? I have the Access 2000 developer's handbook, which I
love for single user stuff, but which I'm having a hard time following for
the client/server stuff.

In addition to the very good points made by others:

- Timestamp Field in every table. OK, this point was already made, but
it is important enough to mention again.
- Getting Identity Field Value in Recordset Add. If you add a new record
to a recordset on an Access table, the Autonumber field value is
available before you Update (to save the record) so you can store it to
variable for later use. With SQL Server the Identity Field value is not
generated until you Update and so is not available until then.
- If opening a Recordset that doesn't need editing use dbOpenSnapshot
(good idea for Access backend too, but makes a bigger difference in SQL)
- If you use db.Execute use dbSeeChanges.
- Use Access queries for everything to begin with. Access will figure
out what requests need to be sent to the server and the processing will
happen on the server and the results will be returned to Access. If you
get a query that Access has decided it can't figure out what to send to
the server, then it will decide that it will perform the query locally
and will then pull all of the data to the client and then do the query.
If you come across one of these queries, then you have two choices: If
you want to retain the logic completely in the FE and do not need to
edit the query results then use a Passthrough Query, otherwise create a
View on the server or if a View is inadequate then a Stored Procedure. I
find that sometimes creating a View of just one part of the query is
enough to simplify the Access query so that it will be able to now pass
the request to the server. Remember that Passthrough queries, Views, and
SPs must be written in T-SQL not in Access's flavour of SQL. This is why
I suggest only worrying about them as a last resort.
- Visit Tony's page where he lists lots of tips and resources:
http://www.granite.ab.ca/access/sqlserverupsizing.htm
- For a good list of the differences between Access and SQL Server go
to: http://www.aspfaq.com/show.asp?id=2214
--
Bri

[Larry Linson]

"John Welch" wrote

I have a potential client who wants an
application with about 20,000 records
and 5-8 users. I told him that Access
would work fine, but he wants to go
with an mdb front end linked to SQL
Server tables.

Have you explained to the client that if he goes with SQL server tables,
they will need more Tender Loving Care than Access, on a continuing basis,
so he will have need of hiring or contracting at least a part-time Database
Administrator (DBA) with Microsoft SQL Server experience? (And, that is not
your line of work, and he needs to get the DBA first, now, to create the SQL
Server database to which you will line.)

That's worked for me... the client asks "How much do DBAs get per hour?" and
I respond, "Probably at least twice as much as I do." Then he says, "I want
you to do it." And I respond, "That's not my line of work. If it was, _I'd_
get twice as much per hour."

-- At which point the client says "And you say that it'll work just fine
with Access tables, no matter what Ol' Charlie over at Acme told me?" And I
assure him that "Access tables will be fine."

-- Or the client says, "Oh, I know a DBA I can contract." or "Do you know a
good DBA that I can contract?"

And in either case, we go forward from there. I've been politely told "We'll
think it over and call you." but I've never been physically thrown off the
premises.

I believe I am about, however, to delve a little more deeply into SQL
Server, more because I am intrigued with what is coming down the pike (SQL
Server 2005) than because I'm worried about doubling my rate. Not, of
course, that I've ever objected to making more per hour... <GRIN>

Larry Linson
Microsoft Access MVP