Access 97 Capasity

[Paul]

I am in need of some advice please:

I have a client who wants to use Access 2k and 2k3 on their workstations.
The appication will be written using Access 2k.

My client has the following setup:

1. Possible 40 users in the database simultaneously (very light to moderate
use)
2. Each user will have Windows 2000 or XP Professional
3. Each user will have their own copy of Access 2k or 2k3
4. The application will be FE/BE with the Back End on a powerful server with
2 GB RAM and dual processors running 2k3 Server
5. Each Front End will be installed locally on each users workstation

The reason I am asking is because I don't know how to use SQL Server and
would like to stick with Access for FE/BE.

Thanks for any advice.

Paul

[Paul]

Re: Access 97 Capasity

I know my subject header is wrong 2k....not 97....sorry.

[color=blue]
>I am in need of some advice please:
>
> I have a client who wants to use Access 2k and 2k3 on their workstations.
> The appication will be written using Access 2k.
>
> My client has the following setup:
>
> 1. Possible 40 users in the database simultaneously (very light to
> moderate use)
> 2. Each user will have Windows 2000 or XP Professional
> 3. Each user will have their own copy of Access 2k or 2k3
> 4. The application will be FE/BE with the Back End on a powerful server
> with 2 GB RAM and dual processors running 2k3 Server
> 5. Each Front End will be installed locally on each users workstation
>
> The reason I am asking is because I don't know how to use SQL Server and
> would like to stick with Access for FE/BE.
>
> Thanks for any advice.
>
> Paul
>[/color]

[Allen Browne]

Re: Access 97 Capasity

Hi Paul

If "very light to moderate use" means that most of the 40 users will not be
*writing* to the database at the same time, then Access can do this.

What bothers me, though, is that if you currently envisage 40 users, then
within the lifetime of the database (5 years?) there will probably be 80
users, and that's pushing Access pretty hard.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul" <pzspam@rogers. com> wrote in message
news:ifudnTFBr4 cAvQXcRVn-rw@rogers.com.. .[color=blue]
>I know my subject header is wrong 2k....not 97....sorry.
>
>[color=green]
>>I am in need of some advice please:
>>
>> I have a client who wants to use Access 2k and 2k3 on their workstations.
>> The appication will be written using Access 2k.
>>
>> My client has the following setup:
>>
>> 1. Possible 40 users in the database simultaneously (very light to
>> moderate use)
>> 2. Each user will have Windows 2000 or XP Professional
>> 3. Each user will have their own copy of Access 2k or 2k3
>> 4. The application will be FE/BE with the Back End on a powerful server
>> with 2 GB RAM and dual processors running 2k3 Server
>> 5. Each Front End will be installed locally on each users workstation
>>
>> The reason I am asking is because I don't know how to use SQL Server and
>> would like to stick with Access for FE/BE.
>>
>> Thanks for any advice.
>>
>> Paul[/color][/color]

[Darryl Kerkeslager]

Re: Access 97 Capasity

Allen,

I'm out of my depth here, but if the OP has a long-term realtionship with
the client, might he not be able to migrate slowly to SQL Server, assuming
it is on the 2 GB server, perhaps starting with some of the relatively
static tables. I ask this, because I have considered it myself.


Darryl Kerkeslager

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote:[color=blue]
> If "very light to moderate use" means that most of the 40 users will not[/color]
be[color=blue]
> *writing* to the database at the same time, then Access can do this.
>
> What bothers me, though, is that if you currently envisage 40 users, then
> within the lifetime of the database (5 years?) there will probably be 80
> users, and that's pushing Access pretty hard.[/color]

[Allen Browne]

Re: Access 97 Capasity

Darryl, there will be others here that have more expreience with SQL Server
than I do, but it's generally not just a matter of attaching the table to a
different database.

There's things like:
- Code that relinks on startup must know how to handle the this.

- Events fire in different order, e.g.:

http://support.microsoft.com/default.aspx?scid=kb;en-us;234866

- Queries that use Access functions such as Nz() or CLng() need to be
re-written.

- To get good performance or solve permissions issues, you may want to
attach Views rather than use your existing queries.

- There are potential traps with field/table names.

- You may need to redesign your interface. For example, you probably won't
get acceptable performance if your forms are bound to tables/queries that
return all records, or if you have lots of subforms on a form (typically in
pages of a tab control), if you load large numbers of records into
combos/list boxes on your forms, and so on.

- The whole approach to filters probably needs to be reconsidered.

- Differences in SQL interpretation, nested queries, subqueries, operators,
replication, permissions (security), constraints, field types, data missing
from large tables,

There's lots of information on "upsizing", e.g the whitepaper at:

http://support.microsoft.com/?id=294407

or list of issues:

http://support.microsoft.com/?id=328319

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Darryl Kerkeslager" <Kerkeslager@co mcast.net> wrote in message
news:YeqdnXCh2I JOtAXcRVn-3g@comcast.com. ..[color=blue]
>
> I'm out of my depth here, but if the OP has a long-term realtionship with
> the client, might he not be able to migrate slowly to SQL Server, assuming
> it is on the 2 GB server, perhaps starting with some of the relatively
> static tables. I ask this, because I have considered it myself.
>
>
> Darryl Kerkeslager
>
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote:[color=green]
>> If "very light to moderate use" means that most of the 40 users will not[/color]
> be[color=green]
>> *writing* to the database at the same time, then Access can do this.
>>
>> What bothers me, though, is that if you currently envisage 40 users, then
>> within the lifetime of the database (5 years?) there will probably be 80
>> users, and that's pushing Access pretty hard.[/color][/color]

[Larry Linson]

Re: Access 97 Capasity

"Allen Browne" wrote
[color=blue]
> What bothers me, though, is that if you
> currently envisage 40 users, then
> within the lifetime of the database (5
> years?) there will probably be 80
> users, and that's pushing Access pretty hard.[/color]

I second Allen's caution here. I have never worked on a multiuser or
client-server database where the initial estimate of user audience did not
_at least_ double within two years. Your situation may be an exception, but
it is something to consider.

There's an introductory presentation on Access in a Multiuser Environment
that I did for my user group that you can download from
http://appdevissues.tripod.com. It will identify topics that I thought
worthwhile to discuss, and a bit more. The best collection of detailed
information and links on the subject of Access in the multiuser environment
is at MVP Tony Toews' site, http://www.granite.ab.ca/accsmstr.htm.

I am perhaps less pessimistic than Allen about Access multiuser with a
higher number of users. But, I guarantee that you will need to carefully
review the suggestions at Tony's site if your audience grows. And, I would
caution that you need to make sure that you are up-to-date with all the
Service Releases/Packs for your user's Access software. Because of the way
that Access/Jet operates, the "horsepower " of the server is not a major
issue, but the capacity of the network is a critical, limiting factor. I
have not seen any reports of multiuser on a gigabit network, but those are
becoming more common, and I suspect it would support much larger user
audiences. Indeed, the physical 255-user limit might become a factor on a
gigabit network.

Factors in how many users can be supported in multiuser include the
requirements, design, and implementation of the database application and the
hardware, software, and network environments. If all factors are near
perfect, we have reliable reports of over 100 concurrent users. Even if not
all are near perfect, we routinely see reports of 30 - 70 users. But, in
cases where we are rather sure that all are about as far from perfect as can
be, people have reported Access "falling over" with as few as four users.

I'd venture to guess that if someone went out of their way to do everything
wrong, it would be possible to create a database that wouldn't even support
one or two users. <GRIN>

Larry Linson
Microsoft Access MVP

[Trevor Best]

Re: Access 97 Capasity

Allen Browne wrote:[color=blue]
> There's things like:
> - Code that relinks on startup must know how to handle the this.[/color]

I recently posted on of these :-)
[color=blue]
> - Queries that use Access functions such as Nz() or CLng() need to be
> re-written.[/color]

A lot can be translated directly, e.g. Nz() to IsNull(), Clng() to
Cast(), others there are no equivalents, e.g. Access' IsNull().
[color=blue]
> - To get good performance or solve permissions issues, you may want to
> attach Views rather than use your existing queries.[/color]

Too true. I have a simple query, 3 tables, 2 joins. Good job I saved as
I had to kill Access after 25 minutes. Copy and paste into Query
Analyser and it runs in 0.vlittle seconds.
[color=blue]
> - There are potential traps with field/table names.[/color]

SQL Server can handle reserved words as column names by enclosing them
in square brakets, much like Access, the added caveat is that those
brackets will need to go into the table design too. As always, just like
Access, try to avoid reserved words, avoids confusion later.

Nothing wrong (according to Access) to using "Name" as a field name, but
make a typo in code and put rst.Name instead of rst!Name and the trouble
begins. IMHO it should reject reserved words as field names but that's
difficult in that new words are introduced in a new version that would
make upgrading a nightmare, but at the very least they should get rid of
the .FieldName notation to avoid conflicts with property names. Prolly
diffcult to get rid of it now, should never have started but hey, what's
done is done.

--
This sig left intentionally blank

[Darryl Kerkeslager]

Re: Access 97 Capasity

Trevor & Allen,

Thanks. I've already avoided these:[color=blue]
> - Code that relinks on startup must know how to handle the this.
> - There are potential traps with field/table names.
> - you probably won't
> get acceptable performance if your forms are bound to tables/queries that
> return all records, or if you have lots of subforms on a form (typically[/color]
in[color=blue]
> pages of a tab control), if you load large numbers of records into
> combos/list boxes on your forms, and so on.
> - The whole approach to filters probably needs to be reconsidered.[/color]

No sub-forms, no filters, no 'simple' field/table names, no linked tables

But these I'll have to look at:[color=blue]
> - Events fire in different order, e.g.:
> http://support.microsoft.com/default...b;en-us;234866[/color]
[color=blue]
> - Differences in SQL interpretation, nested queries, subqueries,[/color]
operators,[color=blue]
> replication, permissions (security), constraints, field types, data[/color]
missing[color=blue]
> from large tables,[/color]
[color=blue][color=green]
> > - Queries that use Access functions such as Nz() or CLng() need to be
> > re-written.[/color]
> A lot can be translated directly, e.g. Nz() to IsNull(), Clng() to
> Cast(), others there are no equivalents, e.g. Access' IsNull().[/color]
[color=blue][color=green]
> > - To get good performance or solve permissions issues, you may want to
> > attach Views rather than use your existing queries.[/color][/color]

Again, Thanks.


Darryl Kerkeslager

[Paul]

Re: Access 97 Capasity

Thanks Allen,

Actually they have been running for 6 years using FileMaker Pro. And their
usage has not changed. So bearing this in mind...is it viable?

Thanks,
Paul

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
news:419824db$0 $25786$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=blue]
> Hi Paul
>
> If "very light to moderate use" means that most of the 40 users will not
> be *writing* to the database at the same time, then Access can do this.
>
> What bothers me, though, is that if you currently envisage 40 users, then
> within the lifetime of the database (5 years?) there will probably be 80
> users, and that's pushing Access pretty hard.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Paul" <pzspam@rogers. com> wrote in message
> news:ifudnTFBr4 cAvQXcRVn-rw@rogers.com.. .[color=green]
>>I know my subject header is wrong 2k....not 97....sorry.
>>
>>[color=darkred]
>>>I am in need of some advice please:
>>>
>>> I have a client who wants to use Access 2k and 2k3 on their
>>> workstations. The appication will be written using Access 2k.
>>>
>>> My client has the following setup:
>>>
>>> 1. Possible 40 users in the database simultaneously (very light to
>>> moderate use)
>>> 2. Each user will have Windows 2000 or XP Professional
>>> 3. Each user will have their own copy of Access 2k or 2k3
>>> 4. The application will be FE/BE with the Back End on a powerful server
>>> with 2 GB RAM and dual processors running 2k3 Server
>>> 5. Each Front End will be installed locally on each users workstation
>>>
>>> The reason I am asking is because I don't know how to use SQL Server and
>>> would like to stick with Access for FE/BE.
>>>
>>> Thanks for any advice.
>>>
>>> Paul[/color][/color]
>
>[/color]

[Allen Browne]

Re: Access 97 Capasity

Yes, but I would encourage you to follow through Larry's presention in a
parallel reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul" <pzspam@rogers. com> wrote in message
news:v9udnfbXaN xrXgXcRVn-vg@rogers.com.. .[color=blue]
> Thanks Allen,
>
> Actually they have been running for 6 years using FileMaker Pro. And
> their usage has not changed. So bearing this in mind...is it viable?
>
> Thanks,
> Paul
>
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
> news:419824db$0 $25786$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=green]
>> Hi Paul
>>
>> If "very light to moderate use" means that most of the 40 users will not
>> be *writing* to the database at the same time, then Access can do this.
>>
>> What bothers me, though, is that if you currently envisage 40 users, then
>> within the lifetime of the database (5 years?) there will probably be 80
>> users, and that's pushing Access pretty hard.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Paul" <pzspam@rogers. com> wrote in message
>> news:ifudnTFBr4 cAvQXcRVn-rw@rogers.com.. .[color=darkred]
>>>I know my subject header is wrong 2k....not 97....sorry.
>>>
>>>
>>>>I am in need of some advice please:
>>>>
>>>> I have a client who wants to use Access 2k and 2k3 on their
>>>> workstations. The appication will be written using Access 2k.
>>>>
>>>> My client has the following setup:
>>>>
>>>> 1. Possible 40 users in the database simultaneously (very light to
>>>> moderate use)
>>>> 2. Each user will have Windows 2000 or XP Professional
>>>> 3. Each user will have their own copy of Access 2k or 2k3
>>>> 4. The application will be FE/BE with the Back End on a powerful server
>>>> with 2 GB RAM and dual processors running 2k3 Server
>>>> 5. Each Front End will be installed locally on each users workstation
>>>>
>>>> The reason I am asking is because I don't know how to use SQL Server
>>>> and would like to stick with Access for FE/BE.
>>>>
>>>> Thanks for any advice.
>>>>
>>>> Paul[/color][/color][/color]