a2k getting multi-user right from the off

[Deano]

Hi,

I feel I have a good learning opportunity here. I have been asked to
knock up a simple db for managing lettings of rooms and halls. I'm
confident about the design but want to make it multi-user even though
it's not strictly necessary right now.

I've not much experience in this even though i've read the correct
websites that have been listed here many times, down to tips about not
using domain aggregrate functions to improve performance (admittedly
that confuses me as I don't see how I could get by without them, but I
digress).

I do have three questions, one general related to the above and two
specific,

First, if you do want to develop a simple multi-user database, what
basic tips would anyone give? I reckon this one will use optimistic
locking and won't be used by more than 10 people at a time, and
probably just 2 in most cases.
What things could I design into (and out of) my app that will save me
grief later on?

Secondly, if I'm distributing a runtime only version and have split
into front and back-ends, how do the users (who don't have Access)
control security? I presume I have to design a form and use VBA to
allow them access to users and passwords? In this scenario I guess the
workgroup security file would be on the server with the tables?

Finally what tips are there for testing on networks? Does working on a
domain vs p2p model make any difference in how Access works?
thanks
Deano (multi-user noobie)

[Lyle Fairfield]

Deano wrote:

I've not much experience in this even though i've read the correct
websites that have been listed here many times, down to tips about not
using domain aggregrate functions to improve performance (admittedly
that confuses me as I don't see how I could get by without them, but I
digress).

I haven't used a Domain Aggregate Function in this century. They used to
be SLOW, or at least that is my recollection and certainly the reason,
good or bad, that I abandoned them; but I believe that has changed and
that they are quite satisfactorily speedy now?

[Deano]

Lyle Fairfield wrote:

Deano wrote:

I've not much experience in this even though i've read the correct
websites that have been listed here many times, down to tips about
not using domain aggregrate functions to improve performance
(admittedly that confuses me as I don't see how I could get by
without them, but I digress).

I haven't used a Domain Aggregate Function in this century. They used
to be SLOW, or at least that is my recollection and certainly the
reason, good or bad, that I abandoned them; but I believe that has
changed and that they are quite satisfactorily speedy now?

Well i'd love to hear from people about this because they are so handy,
not least of all in reports where I can use them to count totals and
use them with immediate IF statements to check for nulls etc.

[pietlinden]

Check with Trevor Best and see if his replacements for domain functions
make a large difference. Use GetTickCount from (maybe) Randy Birch's
website VBNet, which in spite of its name is for VB6, *not* VB.NET.
Then test and decide for yourself. Should prove a very handy function
when testing on a network. Some other helpful stuff is in Peter
Vogel's book, Visual Basic Object and Component Handbook, which you can
read about here:

http://www.amazon.com/exec/obidos/AS...406578-8650243

He has code that allows you to test various things in your application
so you know where to look for problems.

[Rick Brandt]

Deano wrote:

Lyle Fairfield wrote:

Deano wrote:

I've not much experience in this even though i've read the correct
websites that have been listed here many times, down to tips about
not using domain aggregrate functions to improve performance
(admittedly that confuses me as I don't see how I could get by
without them, but I digress).

I haven't used a Domain Aggregate Function in this century. They used
to be SLOW, or at least that is my recollection and certainly the
reason, good or bad, that I abandoned them; but I believe that has
changed and that they are quite satisfactorily speedy now?

Well i'd love to hear from people about this because they are so
handy, not least of all in reports where I can use them to count
totals and use them with immediate IF statements to check for nulls
etc.

The magazine Total Access/VB/SQL Server (name probably not exact) once did tests
comparing all of the "usual" lookup methods and found that DLookup() was only
bested by "Seek" in any significant amount and since Seek is only useful against
local tables that pretty much makes the Domain functions perfectly legitimate
tools to use "as appropriate".

My understanding is that the downside of the domain aggregates is that they
instantiate a database collection in the same manner as...

Dim db as Database
Set db = CurrentDB

The above creates a copy of the current database *and* a refresh of all its
collections. It is this refresh which imposes an overhead "penalty" as compared
to...

Dim db as Database
Set db = DBEngine(0)(0)

....which does not refresh the collections.

So, while DBEngine(0)(0) is reported to be "thousands of times faster" most
developers acknowledge that this difference is only going to matter in a loop or
other usage which would cause the action to be repeated many times in a short
interval. So except in cases where this usage is expected, most just go ahead
and use CurrentDB.

The same holds true for the domain aggregate functions. If you use one in a
query that will process many rows or in a code looping operation the performance
is likely to be poor and a custom replacement can do better. However if the
custom function replacement uses CurrentDB you are simply replacing a poor
choice with an equally poor choice as you introduce the same overhead problems
that the domain function has.

It is also a widely held belief that the domain functions do not utilize
indexes. This is also false and easily disproved by simple testing. It is my
belief that in the early days of Access people saw bad performance from the
domain functions when they were used in queries and from this they earned the
reputation of being "poor performers" all around.

Some people simply prefer to use...

Dim val as Variant
Dim db As Database
Dim rs as Recordset
Set db = CurrentDB (or DBEngine(0)(0))
Set rs = db.OpenRecordSet("SomeQuery")
val = rs!SomeField

....instead of...

Dim val as Variant
val = Dlookup("SomeField", "SomeDomain")

....because they feel like they have more control and in many cases it offers
more flexibility, but unless one uses DBEngine(0)(0) there is no reason to
believe that the RecordSet method will be any faster or more efficient.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Bob Quintal]

Lyle Fairfield <ly******@yahoo.ca> wrote in
news:%o********************@read2.cgocable.net:

Deano wrote:

I've not much experience in this even though i've read the
correct websites that have been listed here many times, down
to tips about not using domain aggregrate functions to
improve performance (admittedly that confuses me as I don't
see how I could get by without them, but I digress).

I haven't used a Domain Aggregate Function in this century.
They used to be SLOW, or at least that is my recollection and
certainly the reason, good or bad, that I abandoned them; but
I believe that has changed and that they are quite
satisfactorily speedy now?

Domain aggregate functions are not inherently slow, but use one to
calculate a starting balance for a record in a query with 100 000
records and the 100 000 calls to the function will definitely slow
down query execution, if you catch my drift.

--
Bob Quintal

PA is y I've altered my email address.

[DFS]

Lyle Fairfield wrote:

Deano wrote:

I've not much experience in this even though i've read the correct
websites that have been listed here many times, down to tips about
not using domain aggregrate functions to improve performance
(admittedly that confuses me as I don't see how I could get by
without them, but I digress).

I haven't used a Domain Aggregate Function in this century. They used
to be SLOW, or at least that is my recollection and certainly the
reason, good or bad, that I abandoned them; but I believe that has
changed and that they are quite satisfactorily speedy now?

They're very, very fast nowadays.

I just released a system that uses them on linked Oracle tables, accessed
across a VPN. The function uses DCounts() to confirm records received =
records transmitted. And they work well. For instance, an operation like
DCount("SurveyID","Responses","SurveyID = 123")) takes < 1 second to poll a
700,000 row Oracle table, across the hi-speed VPN connection.

They're still slightly slower than recordsets, though.

[Albert D. Kallal]

"Bob Quintal" <rq******@sPAmpatico.ca> wrote in message
news:1110042441.8c0b32ba64a00d913c0793f9d0e30447@t eranews...

Domain aggregate functions are not inherently slow, but use one to
calculate a starting balance for a record in a query with 100 000
records and the 100 000 calls to the function will definitely slow
down query execution, if you catch my drift.

Yes, I have to agree with the above. It is not that they are slow, but users
abuse them! .....

For example, you don't want to use 5 lookups to grab 5 field values based on
a key value. You would either use a recrdset, and grab the ONE record, or
perhaps even better use a sql join to bring in the child record data.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Deano]

Albert D. Kallal wrote:

"Bob Quintal" <rq******@sPAmpatico.ca> wrote in message
news:1110042441.8c0b32ba64a00d913c0793f9d0e30447@t eranews...

Domain aggregate functions are not inherently slow, but use one to
calculate a starting balance for a record in a query with 100 000
records and the 100 000 calls to the function will definitely slow
down query execution, if you catch my drift.

Yes, I have to agree with the above. It is not that they are slow,
but users abuse them! .....

For example, you don't want to use 5 lookups to grab 5 field values
based on a key value. You would either use a recrdset, and grab the
ONE record, or perhaps even better use a sql join to bring in the
child record data.

Interesting. Thanks for all the replies so far. If anyone can answer
one of my original questions, perhaps this one it would be of great
help;
"...if I'm distributing a runtime only version and have split into
front and back-ends, how do the users (who don't have Access) control
security? I presume I have to design a form and use VBA to allow them
access to users and passwords? In this scenario I guess the workgroup
security file would be on the server with the tables?"

[Albert D. Kallal]

"Deano" <ma**********@gmail.com> wrote in message
news:xn***************@usenet.plus.net...

"...if I'm distributing a runtime only version and have split into
front and back-ends, how do the users (who don't have Access) control
security?
Yes...that is correct. However, with a good design, then likely this process
can be simplified a lot (low, medium, and super users for example).
I presume I have to design a form and use VBA to allow them
access to users and passwords?
Yes, that is generally what you do. I actually prefer this, even when NOT
using the runtime. I give some example screen shots of this idea here

http://www.members.shaw.ca/AlbertKal...erFriendly.htm

In this scenario I guess the workgroup
security file would be on the server with the tables?"

Yes, that is correct....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Deano]

Albert D. Kallal wrote:

"Deano" <ma**********@gmail.com> wrote in message
news:xn***************@usenet.plus.net...

"...if I'm distributing a runtime only version and have split into
front and back-ends, how do the users (who don't have Access)
control security?

Yes...that is correct. However, with a good design, then likely this
process can be simplified a lot (low, medium, and super users for
example).

I presume I have to design a form and use VBA to allow them
access to users and passwords?

Yes, that is generally what you do. I actually prefer this, even when
NOT using the runtime. I give some example screen shots of this idea
here

http://www.members.shaw.ca/AlbertKal...ity/UserFriend
ly.htm

In this scenario I guess the workgroup
security file would be on the server with the tables?"

Yes, that is correct....

Thanks Albert, it's useful to have these basic questions answered.
Site's looking nice as well.