HELP!!!Developping a FE/BE application

[Guest]

Hello experts,

Our department is revising the use of MsAccess to improve the performance,
reliability and user-friendly aspect of our database.

I really need your feed-back about the following point: one programmer in
our team wants to develop the FE without using the forms and just by coding
and she never used it up to now. The other programmer thinks that it is not
possible to develop the FE without using the forms. At my point of view - I
am a Business Analyst - if the feature exists in MsAccess, it is there
because it simplify the programming and increase performance. Also, I have to
admit I douth about the first programmer's conceptualisation because I have
many reasons to think she is not competent on MsAccess and our MsAccess
expert is the second programmer.

My first question is: Is it possible to do an MsAccess FE without using the
forms? and my second question would be: If yes, what would be the more
advantageous for performance and reliability?

Thanks a lot for your feedback!

 

[Rick B]

Not sure I understand the first person's perspective. If a user will add a
record to the database, they will do so using a form. How would she do this
using only "coding"? Is she just gonna pop up a bunch of input boxes for
the user to fill in? Wouldn't it be MUCH MUCH easier to maintain this if
you use the features built into Access?

I would absolutely build the front end using a combination of forms, reports
and queries. Of course, most of these object will also have code. The code
will be tied to functions performed in the various objects. Any button on a
form would have code.

Personally, I would NOT use the built-in switchboard manager. I would
create my own "switchboard" or main menu form. I would use buttons and code
to have this menu open the other objects in the database. Typically, I
include other data on my opening menu such as: the currently signed on
userID, any important numbers like open orders, past due items, etc.

 

[Norman Yuan]

I susppect that the first programer's "FE without using form" opinion refers
FE developed outside Access, using VB/VC++/.NET...while still using *.mdb
(JET database) as BE (thus, no form in Access). If it is the case, you need
to carefully study your case to decide whether to base your sustem on Access
only (FE+BE) or on other technology. At least, look into all possible
solution before doing it.

 

[Tony Toews]

Our department is revising the use of MsAccess to improve the performance,
reliability and user-friendly aspect of our database.

I really need your feed-back about the following point: one programmer in
our team wants to develop the FE without using the forms and just by coding
and she never used it up to now. The other programmer thinks that it is not
possible to develop the FE without using the forms. At my point of view - I
am a Business Analyst - if the feature exists in MsAccess, it is there
because it simplify the programming and increase performance. Also, I have to
admit I douth about the first programmer's conceptualisation because I have
many reasons to think she is not competent on MsAccess and our MsAccess
expert is the second programmer.

My first question is: Is it possible to do an MsAccess FE without using the
forms? and my second question would be: If yes, what would be the more
advantageous for performance and reliability?

You've already gotten two excellent answers with speculation about the
first programmers reasoning.

If you need a web based app with external users then Access is not
your solution. However Access is an excellent solution for database
apps in terms of developer productivity due to such things as bound
forms and easy to use reports. Much better than any other product
out there such as VB or C++ or VB.Net.

There are some tips for performance at the Access Performance FAQ page
at http://www.granite.ab.ca/access/performancefaq.htm

The biggest problems in terms of reliability are in stability of the
hardware and the number of users making changes. Reporting or inquiry
only users don't make a difference. Editing/Updating users have been
successfully in the ten to fifteen range.

However your big concern is how mission critical is the data and can
the data be rekeyed if you lose a day.

Mission critical means can you afford to lose an hour if the database
is down. Frequently the data can't be rekeyed. A classic example
being a call centre where you are receiving incoming calls.

Losing a day means that if you have to restore from backup do your
users have the paperwork in place so they can rekey the data? Are
there enough staff to re-enter that data?

I recall a posting by someone working for a large casino/hotel
operation in the mid to late 80s stating to what lengths they spend
over a million dollars duplicating their IBM S/38 mini-computer in
another off site location with data inserts and updates being copied
from the main system to the backup system in under a second.

Their attitude was that they could never afford to lose a room
reservation. Imagine the mess if they lost a days worth of phone
calls. <shudder> And the newspaper stories by the upset clients.
And expenses while they placate the customers so they don't go to the
newspapers. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

Hi Norman,

First thing, thanks for your fast answer. If possible, I would like to have
your opinion on the following:

Considering that our databases are needed to stock data for history, to
generate queries and reports;
That we are not a computer department so we can not have access directly to
the server or we can not create a tool on our web internal site;
That it will be a multi-user database;

What do you think would be the better way to create the DB: by using the
forms or as you said?

Thanks again!

 

[Guest]

Hi Norman,

First thing, thanks for your fast answer. If possible, I would like to have
your opinion on the following:

Considering that our databases are needed to stock data for history, to
generate queries and reports;
That we are not a computer department so we can not have access directly to
the server or we can not create a tool on our web internal site;
That it will be a multi-user database;

What do you think would be the better way to create the DB: by using the
forms or as Norman said:

"I susppect that the first programer's "FE without using form" opinion refers
FE developed outside Access, using VB/VC++/.NET...while still using *.mdb
(JET database) as BE (thus, no form in Access). If it is the case, you need
to carefully study your case to decide whether to base your sustem on Access
only (FE+BE) or on other technology. At least, look into all possible
solution before doing it."


Thanks again!

 

[Guest]

Excuse me Tony for having addressed my last courrier to the attention of
Norman in the header instead of putting your name...

 

[Norman Yuan]

Without knowing much details of your situation, I can only give a little
very generic comments.

When design a database system, you usually start from BE, the data store
part. At this end, Access DB (more precisely, JET DB) is usually used for a
system with not too many concurrent access, say, less than 30, 50, or even
100, all depending on how the data is accessed and how the system is
designed. good design could serve a quite big office with a few million
records, not so good design could only server a couple of dozen users with
constant maintenance issues.

Assume the JET DB does meet your needs, obviously, an Access FE should be
the first target in your radar for consideration, if the system will be used
in LAN. But do remeber, with the same BE, you could develop different type
of FE, Access is only one of the options. Consider these:

1. Do you want to access the data via WAN/the Internet (JET db is not
suitable for wep app, in general, but could cope with, if not too many
users/access). FE in Access can not be used in such type of app.

2. Access FE/BE is typically a two-tier system. If the data in DB has
multiple targeting users and could be integrited into different type of
application (office, CAD software...), you may consider multi-tier approach
to seperate the system into data access layer, business logic layer,
presentation layer. With Access FE you could do it, but you may do it better
outside Access FE.

3. Expertise available. A good Access programmer may not be good on other
language and the technology associated with thoses languages. OTH, almost
most generic programmers are not good at Access FE developing, such as
myself, because he focuses on VB/C++/.NET/JAVA too much, and only (or not at
all) wet his feet a little bit in Access developing in his career. However,
if a programmer only dealed with Access, his prespective on the entire IT
system could be limited. My impression is good generic db programmer is
easier to find than a good Access programmer (but there are a lot not so goo
d Access programmers/users).

4. For FE app, all users need Access installed (or Access runtime. for
current version, you need VisualStudio2003 Architect or MSDN universal
subscription to get it. So, you will have latest MS generic programming
tool, which may please the "first programmer"), while other type of FE does
not require Access installed, even for the server where *.mdb locates.

In general, I'd consider all-in-Access solution first, IF you have limited
users (less than 50?) and the system is on the LAN and the BE is JET DB.
OTH, if the BE could be other type of DB Server, I'd seriously look other
options before making decision. For example, even with currently small
system, if there is good chance of increase, you may consider MSDE as BE, in
order to move to SQL Server smoothly later.

 

[Guest]

Hi Norman,

Thanks for the detailed answer, it is appreciated! Following are some
details concerning our department needs and possibilities to evaluate the
most valuable solution for the the development of the front-end:

- mainly, upload of data from Oracle for history;
- filtering infos into tables;
- executing some queries;
- possible need for specific analysis using queries and more;
- for multi-users inside and outiside our department ;
- on the shared drive of our department;
- not available from our internal website;
- we are not the IT department but Inventory Control so we do not have
direct access on the servers, the website or Oracle...etc;
- security;
- reliability;
- performance in the execution of queries or when tables are filtered;
-user-friendly.

If you need more information to evaluate what would be the best option for
us, just ask me and I will be really please to give you more details.

Thanks again!

Pascale

 

[Larry Linson]

4.. . . (or Access runtime. for current version,
you need VisualStudio2003 Architect or MSDN
universal subscription to get it.

You do not have to subscribe to any MSDN subscription to obtain the Access
2003 runtime -- you have to license the product "Visual Studio Tools for
Office 2003 System". That is considerably less expensive than the MSDN
subscriptions you mention... along with the Access runtime, it includes
VB.NET and C# for use with Word or Excel (they cannot be used with any other
Office software in this release).

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

- mainly, upload of data from Oracle for history;
- filtering infos into tables;
- executing some queries;
- possible need for specific analysis using queries and more;
- for multi-users inside and outiside our department ;
- on the shared drive of our department;
- not available from our internal website;
- we are not the IT department but Inventory Control so we do not have
direct access on the servers, the website or Oracle...etc;
- security;
- reliability;
- performance in the execution of queries or when tables are filtered;
-user-friendly.

An anlysis of your requirements and environment to give a definitive answer
the question: what application do we need, and how should it be implemented
is far beyond the scope of a thread in a newsgroup. These kinds of studies
can take a lot of time and cost a lot of money. We try to help to the extent
we can, but can't provide free, in-depth consulting.

People who have specific questions about how to use a product to do some
specific functions have the best chance of getting the answers they need.
For more information and good suggestions on effective use of newsgroups,
see the FAQ at http://www.mvps.org/access/netiquette.htm.

I would suggest, if you have a limited number of users, and all those users
are on a LAN with a central server on which to store your data (and that is
what I think you have described), that it would be folly, sheer folly for
you, as a user department, to attempt to develop your database with some
other tool than Access.

The nearest tool to Access for this type of application, in terms of
productivity, has been "classic VB" (VB6 or earlier), and those experienced
with both have, many times, stated that to build the same application will
take at least 3 - 5 times the time and effort in VB as in Access. Other
solutions, such as VB.NET or C++ or C# would take even more time and effort.
And, few other tools can provide the assistance in querying and reporting
that Access provides, certainly not VB, C++, C#, Delphi, or any
commonly-used "programming language".

On the other hand, I wonder if it makes sense at all for this application to
be constructed by anyone not experienced in computer application
development. I think you might want to consider finding someone who is
experienced in Access development to gather and document the requirments;
design the application and, at least, prototype the main functionality. If
all goes well, that prototype can be the basis for your first stage of
production application.

Then, with guidance and mentoring from that person or those people, and
modeling on what they have done, you may be able to extend the application
to the subsequent stages you desire.

Performance and security are just words unless there is a detailed
definition of what you expect for each. I don't think you are going to find
any tool that end-users can use to create a database application that
provides any better performance and security.

There are other "desktop databases" that compete with Access, but none that
I would consider for an application as you describe.

Now, as an alternative: Access can, and often is, used to create client
applications for use with server databases. Since you already have Oracle,
perhaps you can find a Database Administrator to assist you in setting up
the tables, relationships, etc. to store your data in Oracle, and then you
link to those tables to create a client application in Access.

Larry Linson
Microsoft Access MVP

 

[Guest]

Thanks Larry for your detailed answer: it help me to have a better
perspective of what we should do.

I would like to know, do you think it is possible to create a splitted FE/BE
application on MsAccess just using code into the modules without using the
forms? I have found out that this is what the first programmer is actually
doing. Strangely, from the time she started to program her DB, her computer
crashes all the time and that is why I am questionning the coding capacity of
MsAccess if she proceed that way. Do you think that using the forms could
reduce the size of the overall coding because the forms are already
programmed in the most efficient way?

Thanks a lot

 

[Norman Yuan]

You keep saying "coding without form" thing. That is something very
confusing. In Windows Desktop app, including Access FE app, everything user
sees (well, almost everything) is on a "FORM". Even if the app let user see
and wirk directly on code (but why), the code must be presented to the user
on FORM of some kind. Unless the "first programmer" is doing thing in a DOS
way without user interface, just type some commands in a command window, or
it is a Windows service that does not need to interact with user.

Whether you talking a FE app developed with Access or stand-alone Windows
desktop app, or Web app, form is the thing used as user-friendly interface.
With Access, you can create some simple FE app, including a few forms,
Macros, reports, without writing code. However, if in many cases, code (VBA)
can make your Access app handle more complicated processes and more
functional. From what yo have described, if the app is to developed in
Access, VBA code is very much likely needed. Form in Access app is almost
always needed. Code is not there against form, it is supplement to the form
and in most serious Access app, code is a must.

Actually, from your requirement, I see the case is not that as simple as
whether using Form or Code in Access FE. As Larry suggested, you do need
someone who is capable to analyse the system to sort out a clear system
specification. It is almost impossible to give useful suggestion in detail
without first hand closer look.

 

[Tony Toews]

Considering that our databases are needed to stock data for history, to
generate queries and reports;
That we are not a computer department so we can not have access directly to
the server or we can not create a tool on our web internal site;
That it will be a multi-user database;

What do you think would be the better way to create the DB: by using the
forms or as Norman said:

"I susppect that the first programer's "FE without using form" opinion refers
FE developed outside Access, using VB/VC++/.NET...while still using *.mdb
(JET database) as BE (thus, no form in Access). If it is the case, you need
to carefully study your case to decide whether to base your sustem on Access
only (FE+BE) or on other technology. At least, look into all possible
solution before doing it."

Clearly some of your functionality you describe won't be using the
forms. Downloading of data from Oracle would be one of those. But
that will likely be done using some append queries and such.

But as Larry has already stated Access is an excellent tool for
database development and considerably quicker to develop database apps
in than competitive technology. But Larry and I, and many others in
this newsgroup share a common bias. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm