Proper use of forms to navigate records

[Guest]

Hello,

I have been noticing in the MS Access books that I have been reading that
the author(s) have been using the form's record selectors, navigation
buttons, and dividing line as the main form of navigation between records.
This is for many different publishers such as Wrox Publishing or Microsoft
Press. I have also noticed that they bind the table directly to the form and
without using a mid-tier to establish their business rules. I was wondering
do they do it this way for simplicity or is it a common practice to develop a
MS Access application that way? I would like to use more of a client/server
structure as I hear that is the best way MS Access can support a multi-user
and multi-concurrent environment. I will be using Access 2003 and will
probably support upwards of 25 users though that could change either way.

Any help would be appreciated.

Regards,

Paul

 

[Guest]

This is the default 'behavior'. You can work around it but I wouldn't unless
there was a very specific need to do so. Why mess with something that ain't
broke! (as they say). I have a multi-user db working with the default
behavior without any issue whatsoever.

When building a multi-user db it is more important to ensure that you split
your db (front-end - queries, forms, reports, modules .. back-end -
tables/data) and secure it (if security is an issue).

Furthermore, you can still enforce business rules through vba by validating
the input info prior to being committed... and other technics of the sort.

Daniel

 

[Guest]

Thanks Daniel. I guess that by using this method that I won't have full user
acceptance to use this methodology. I'm not too sure if the user would like
to right-click on the customer name text box, type in a name with a
wildcard(*), get the result(s), and then have to "unfilter" the form to get
back to the first record. I am probably thinking more along the lines of a
"formal" VB application. I understand that if the user wants a more robust
application, then they should spend the money on a VB app with an enterprise
db. However, that is not possible, and so I am trying to get my MS Access app
as close to mirror a VB app. I am currently finishing a MS Access app with
unbound forms and class objects with full property let and get statements and
methods to create a distinctly user-friendly app for my employer. What are
your thoughts on this and what I had mentioned in the beginning? Any one
else's thoughts?

Regards,

Paul

 

[Albert D. Kallal]

I have been noticing in the MS Access books that I have been reading that

the author(s) have been using the form's record selectors, navigation
buttons, and dividing line as the main form of navigation between records.
This is for many different publishers such as Wrox Publishing or Microsoft
Press.

Good observation on your part. In my applications, the main edit forms
actually NEVER have navigation buttons. (for sub forms...yes...main
table..not very often).

The reason why you want to avoid navigation is not because they
are bad, but it means you failed to ask the user what they need to
work on in the first place.

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server applications):


http://www.members.shaw.ca/AlbertKallal/Search/index.html

I have also noticed that they bind the table directly to the form and
without using a mid-tier to establish their business rules.

yes, the above is common approach. It really comes down to how
complex the appcation is going to be, and also the fact if you
actually HAVE tools that let you build that middle tier.
(access does not have tools that allow building of the middle
tier).

I was wondering
do they do it this way for simplicity or is it a common practice to
develop a
MS Access application that way?

It is case of simplicity, BUT ALSO ONE OF productively also. The building of
a middle tier was USUALLY the result of a client/server environment, but the
main
problem was that the server side totally sucks. It is quite hard to write
complex
payroll routines in t-sql for sql server. and, without a debugger, and a
nice
integrated development environment, programmers simply gave up on the t-sql
and wrote their code in a middle tier. After all, that complex code had to
go somewhere. It not totally that developers wanted to use a middle tier,
but more so that the server side coding really was rotten, and they
did not want to put all of the business code into the front end.
(especially when that front end also sucks - such as web based!!)

However, the above tends to apply to a web based model.
ms-access actually gets it power and productivity from using
bound forms. It is why you can cut the cost of a project by
a factor of 5 or even more as compared to something like VB.

Further, for most applications, it don't hurt so much that you place the
code
+ ui together. In fact, this merging of code + UI is why ms-access is so
productive.

Here is a few quick easy screen shots of bound data in ms-access:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

So, if you want take a project that costs $7000 in ms-access, and use a nice
middle tier approach, be probably to spend $28,000 on the project. So, the
development of the middle tier also based on how many developers and budgets
you have. And, also, with a middle tier, your application tends to be more
scalable. So, if you need 300 users, you very might well consider a 3 tier
setup.

If you only plan to have 30 users, then I would use ms-access with sql
server, but I would not for the most part introduce a middle tier (well, if
you do that..then you using the wrong tool.). ms-access is 2 tier, and is
productive in that setup. It not designed for a 3 tier..and if that is your
requirements..then don't use ms-access.

the only exception to the above I suppose is using web services -- there is
a
nice soap tool kit add-in for ms-access. So, I suppose if you had a rich
web based system where your middle tier code was designed and written as a
web service, ms-access can consume those web services.

I would like to use more of a client/server
structure as I hear that is the best way MS Access can support a
multi-user
and multi-concurrent environment.

well, client/server tends to mean 2 parts...client side..and server side.
You don't really have a practical way to introduce the 3rd "middle" tier.

I will be using Access 2003 and will
probably support upwards of 25 users though that could change either way.

If you starting out to assume 25 users, then I would certainly use sql
server
for the back end, and you can use ms-access for the front end.

You can most certainly contain some business rules and logic by using
class object. I explain them here:

http://www.members.shaw.ca/AlbertKallal/Articles/WhyClass.html

And, here is interesting article on converting a mainframe application to
ms-access. Give it a read, as it also gives some ideas and concepts
used in ms-access development.

http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000003.html

 

[Guest]

Thanks Albert for the response as I found it to be very useful. However, I
guess that when I meant a "middle tier", I was referring to using class
modules with Property Let and Property Get statements and using public and
private methods. I am hoping that I used the correct terminology.

I am now a little uneasy with my situation when you refer to statements like
"then you using the wrong tool" and "if that is your requirements..then don't
use ms-access". My situation is that I am building a timesheet app with
Access 2003 where the users enter the time they spend on each task throughout
the day. However, the requirements were to prevent the user if they exceeded
their time allottment for the entry and also for the day, are trying to
modify past entries when they are beyond a predefined threshold (in days),
and etc. Thus, it became very cumbersome to try putting all of the code
directly in the form module when I can reuse it in a class module. I am sorry
that I grossly overstated my user base as it is probably closer to 15 users
with about 10 being concurrent. Did I make a mistake somewhere and
overestimate Access and its capabilities?

Regards,

Paul

 

[Albert D. Kallal]

Thanks Albert for the response as I found it to be very useful. However, I
guess that when I meant a "middle tier", I was referring to using class
modules with Property Let and Property Get statements and using public and
private methods. I am hoping that I used the correct terminology.

Well, no, when we refer to the "middle" tier, it not the front end, nor is
it
the back end (server). This normally means you would developer your
business rules code in this middle tier. These business objects would
be developed compiled, and run on the server side. This middle tier
does NOT have any data entry forms. Your front end would thus communicate
with these objects on the server. And, these objects would then communicate
with the sql server. So,
in a traditional sense, a 3 tier system means:

1 your front end (vb, or web based) - runs on pc side

2 your middle tier. There is where those data processing routines and
code would be developed. These objects would have the ability
to communicate with the database system. You front end would
connect to, and use these business objects

3 the sql server, or back end database.

So, yes, I would say your terminology is somewhat incorrect.

when I meant a "middle tier", I was referring to using class
modules with Property Let and Property Get statements and using public and
private methods.

Ah, ok...that is a different meaning then the "developers" term of a middle
tier.

If you read my article on class objects, you note that you GAIN LITTLE if
your
just storing data by using class objects.

The use of class objects is ONLY of use to contain complex
code and data TOGETHER.

I mean, if the code for a given form needs to have some code,
then by means place that code in the form (in case you don't realize, a form
in ms-access contains a code module, and that form is actually a class
object (you can have more then one instance of the same form open at the
same time).

So, sure..it helps to use class objects in ms-access to contain code.
However,
building a object with a bunch of property gets/lets accomplish noting in
terms
of performance if those lets/gets are better served by just storing the data
in a
table, or controls on a form. ONLY use class objects if your code is of
complex or actually needs to be placed in a class object.

For most processing routines, you don't place them in forms code, nor
in a class object, but simply place the code in standard code modules.

I am now a little uneasy with my situation when you refer to statements
like
"then you using the wrong tool" and "if that is your requirements..then
don't
use ms-access".

I was referring to building a middle tier. You can do that with VB, but not
with ms-access. So, if your requirements are to actually build a middle tier
then ms-access is the wrong tool. You can't build a bunch of
code objects and place these on a server (and have the front end communicate
to this middle tier of code objects running ON THE SERVER). So, if were
actually do need to build a 3 tier system, then ms-access is the wrong tool.
So, I not saying access is the wrong tool, but for a 3 tier system, it can't
be done with ms-access.

Thus, it became very cumbersome to try putting all of the code
directly in the form module when I can reuse it in a class module.

Yes, 100% correct. Fro most general code routines, you place them
in a standard code module. And, if you need be, create some class
modules (objects) also.

I am sorry
that I grossly overstated my user base as it is probably closer to 15
users
with about 10 being concurrent. Did I make a mistake somewhere and
overestimate Access and its capabilities?

No, you did not. The key here is if you are staring out with 25 uses, then
that is
a starting point. Then usually those systems grow from that point.

Regardless of growth, if you are starting out with 25 users, then ms-access
is great for the front end, but I would plan and use sql server for the
back end.

If planning only for about 10 users, then you can well start with a shared
mdb
for the back end. If your user count grows, then simply move the back data
to
sql server, and keep your front end as ms-access.

 

[Guest]

Thanks Albert for your guidance. I now realize that I was going in the wrong
direction and didn't realize that I was doing too much work required for the
job. I will make my adjustments and use this as a learning experience. Thanks
again.