Is access suitable?

**Alan Webb** · Nov 13 '05, 01:54 AM

Re: Is access suitable?

Script Learner,
It's a familiar refrain from me: Buy a commercial package because building
your own is almost always more costly, more time consuming, and more likely
to cause your son to wish you were not his Dad. Your e-mail looks like it
originates out of the UK. If Microsoft's Great Plains Accounting is
available there it is a good investment. There is a cheap(er) version of
Peachtree Accounting that sells for under $100.00 US. Intuit's QuickBooks
is another package which will do all this for a reasonable price.
Now, if you are still willing to invest in the time it will take to build
your own software, start with a careful, detailed plan. Then make sure you
build, test, rebuild, test, implement, re-evaluate, rebuild, test . . . in
an organized manner. Writing software is now a field that is over a
half-century old and in that time much has been learned about how to do it
well. You owe it to your sanity, your son & your company to do the research
needed to find the wealth of material that exists on how to plan & build an
application.
As for my favorite best practices, I love MSDE or some other flavor of SQL
Server. Jet is nice and simplifies things for small projects but running a
company is something that will easily outpace Jet's ability to maintain the
data--so go with MSDE or SQL Server, or something equivilant. Whenever
possible, I try to do everything in SQL or Transact SQL. Failing that I
like working with Access ADP project files that contain the user interface.
Unbound forms & reports are a good idea for lots of reasons. And, given a
choice, I'd work out an object model I can implement in VB that becomes the
foundation for my application.
That's the high notes. The rest is a lot. Good luck.

"script-learner" <jlwilson@webma sters.freeserve .co.uk> wrote in message
news:90da3f22.0 407101332.4da8c a8f@posting.goo gle.com...[color=blue]
> I currently own a fruit and veg business and wish to compuiterise my
> manual methods in order to reduce labour costs. I spoke to my son who
> is a whiz on computers and he had a good chat with me and explained
> that what I needed was possible but he was unsure if he had the
> capabillities to carry it out. He has started drawing some plans and I
> will attempt to explain what I will require from the software. It will
> need to be user friendly and windows based, cover three areas, new
> customer invoice, stock control, and customer database(simila r to
> sage).
>
> All must be interlinked (my son has explained this) when I enter a new
> invoice it will automatically print 3 copies and remove stock from
> current(monitor ed in stock control) which will intern add that invoice
> to the customer database and show an outstanding sum. Firstly my son
> would like to know if there are any peices of software able to create
> such an 'interlinked' database, from reading other posts I figure
> ms-access may be one? If not what language is recommended. If all does
> not go to plan are there IT experts who will take on such a project?
>
> Any help given is greatly apprichiated. J[/color]

**Bernard Peek** · Nov 13 '05, 01:54 AM

Re: Is access suitable?

In message <90da3f22.04071 01332.4da8ca8f@ posting.google. com>,
script-learner <jlwilson@webma sters.freeserve .co.uk> writes[color=blue]
>I currently own a fruit and veg business and wish to compuiterise my
>manual methods in order to reduce labour costs. I spoke to my son who
>is a whiz on computers and he had a good chat with me and explained
>that what I needed was possible but he was unsure if he had the
>capabillitie s to carry it out. He has started drawing some plans and I
>will attempt to explain what I will require from the software. It will
>need to be user friendly and windows based, cover three areas, new
>customer invoice, stock control, and customer database(simila r to
>sage).
>
>All must be interlinked (my son has explained this) when I enter a new
>invoice it will automatically print 3 copies and remove stock from
>current(monito red in stock control) which will intern add that invoice
>to the customer database and show an outstanding sum. Firstly my son
>would like to know if there are any peices of software able to create
>such an 'interlinked' database, from reading other posts I figure
>ms-access may be one? If not what language is recommended. If all does
>not go to plan are there IT experts who will take on such a project?
>
>Any help given is greatly apprichiated. J[/color]

Access can certainly be made to do everything that you have described,
but that doesn't necessarily make it the right tool for the job. If your
son didn't know that then he probably doesn't know enough about Access
to do the job himself without help. He may be able to learn, but that
will take time. During that time you will both make mistakes and that
could cause a lot of angst.

There are people who can come in and look at your business, analyse what
you do and then program Access to help you do it. You probably can't
afford to hire anyone who is any good. Good custom-made software is
expensive.

My advice would be to talk to other people in similar businesses and ask
them what software they use. Talk to whoever they bought their software
from to see if it can be adapted to your needs.

You may find that there isn't anything suitable on the market. In that
case you may want to design your own system. Access is a good tool for
that job. But don't expect to build the complete system in an afternoon,
or even in a year. You should start by building something to assist some
of your existing manual processes, and add new functions when you are
sure that what you have designed works right every time.

You can expect to get some help through newsgroups like this one, but in
general the difficult questions aren't technical ones. They are more
likely to be about the way your business works, and how business
processes can be translated into something you can write a program for.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

**Jim Allensworth** · Nov 13 '05, 01:55 AM

Re: Is access suitable?

On Sat, 10 Jul 2004 19:12:17 -0400, "Alan Webb" <knogeek@hotmai l.com>
wrote:

<snip>[color=blue]
> Jet is nice and simplifies things for small projects but running a
>company is something that will easily outpace Jet's ability to maintain the
>data--so go with MSDE or SQL Server, or something equivilant.[/color]
I would definitely disagree with this statement. For example, I
designed and built a Jet based system for a wholesale manufacturing
company that in 5 years has quadrupled in size (data wise) and has
also increased considerablyl in functionality. This multi-user sytem
runs virtually all of the business. Including just-in-time production
processes; materials usage, tracking and reconciliation. Along with
inventory of products (finished and in-process), materials, order
fulfillment and accounts receivables. And that's only part of it.

The point being, that to dismiss Jet as only suitable for small
projects is flat wrong and possibly indicates a lack of understanding
of its potential.
[color=blue]
> Whenever
>possible, I try to do everything in SQL or Transact SQL. Failing that I
>like working with Access ADP project files that contain the user interface.
>Unbound forms & reports are a good idea for lots of reasons. And, given a
>choice, I'd work out an object model I can implement in VB that becomes the
>foundation for my application.[/color]
Sure there are places where SQL Server would be better, but keep in
mind that approximately 95% of the businesses - in the U.S. - have
fewer (a lot fewer) than 10 employees. Using the methods you describe
would only add to the cost to the project without gain. From a
business perspective that may (most likely) not be wise. Although it
might be viewed as more lucrative from the perspective of a consultant
- more billable hours ;-)

That's my $0.02,
- Jim

**Alan Webb** · Nov 13 '05, 01:55 AM

Re: Is access suitable?

Jim,
Access and Jet can do a lot but I've had experience with both Jet SQL and
Transact SQL and I'll take Transact SQL any day given a choice. My primary
point is one I am fond of making--that in the choice of whether to buy or
build a solution it is almost always cheaper to buy a prepackaged solution.
As for Jet's ability to run a company, that is something much debated here.
I've seen posts that claim developers have built applications that can
support a couple hundred users. It may be better, but when I was building
multi-user applications using Jet my seat of the pants impression was that I
could support roughly 5-10 concurrent connections. You can optimize this by
building your applications using unbound forms that step into the database
to transact data and then get out. But I am an SQL Server fan and it's what
I use most of the time.

"Jim Allensworth" <JimNOT@datacen tricsolutions.c om> wrote in message
news:40f175a7.9 293390@netnews. comcast.net...[color=blue]
> On Sat, 10 Jul 2004 19:12:17 -0400, "Alan Webb" <knogeek@hotmai l.com>
> wrote:
>
> <snip>[color=green]
> > Jet is nice and simplifies things for small projects but running a
> >company is something that will easily outpace Jet's ability to maintain[/color][/color]
the[color=blue][color=green]
> >data--so go with MSDE or SQL Server, or something equivilant.[/color]
> I would definitely disagree with this statement. For example, I
> designed and built a Jet based system for a wholesale manufacturing
> company that in 5 years has quadrupled in size (data wise) and has
> also increased considerablyl in functionality. This multi-user sytem
> runs virtually all of the business. Including just-in-time production
> processes; materials usage, tracking and reconciliation. Along with
> inventory of products (finished and in-process), materials, order
> fulfillment and accounts receivables. And that's only part of it.
>
> The point being, that to dismiss Jet as only suitable for small
> projects is flat wrong and possibly indicates a lack of understanding
> of its potential.
>[color=green]
> > Whenever
> >possible, I try to do everything in SQL or Transact SQL. Failing that I
> >like working with Access ADP project files that contain the user[/color][/color]
interface.[color=blue][color=green]
> >Unbound forms & reports are a good idea for lots of reasons. And, given[/color][/color]
a[color=blue][color=green]
> >choice, I'd work out an object model I can implement in VB that becomes[/color][/color]
the[color=blue][color=green]
> >foundation for my application.[/color]
> Sure there are places where SQL Server would be better, but keep in
> mind that approximately 95% of the businesses - in the U.S. - have
> fewer (a lot fewer) than 10 employees. Using the methods you describe
> would only add to the cost to the project without gain. From a
> business perspective that may (most likely) not be wise. Although it
> might be viewed as more lucrative from the perspective of a consultant
> - more billable hours ;-)
>
> That's my $0.02,
> - Jim
>[/color]

**Bernard Peek** · Nov 13 '05, 01:55 AM

Re: Is access suitable?

In message <lJ_Hc.329$cq3. 26251@news.uswe st.net>, Alan Webb
<knogeek@hotmai l.com> writes[color=blue]
>Script Learner,
>It's a familiar refrain from me: Buy a commercial package because building
>your own is almost always more costly, more time consuming, and more likely
>to cause your son to wish you were not his Dad.[/color]

Very true. The OP should look for existing packages. They might look
expensive to a small business, but that will probably be much less than
the cost of paying a developer to build a system from scratch.

Perhaps if the original poster tells us the approximate size of his
business we could come up with a better estimate.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

**Tony Toews** · Nov 13 '05, 01:55 AM

Re: Is access suitable?

"Alan Webb" <knogeek@hotmai l.com> wrote:
[color=blue]
>. It may be better, but when I was building
>multi-user applications using Jet my seat of the pants impression was that I
>could support roughly 5-10 concurrent connections.[/color]

Define connections.

I have a client running 25 users all day long with very few problems.
[color=blue]
>You can optimize this by
>building your applications using unbound forms that step into the database
>to transact data and then get out.[/color]

So what's the difference between using bound and unbound forms? In terms of
corruption and such that is. And I'm being quite serious.

Using a bound form means that
1) an empty record is created when you hit the first key stroke on a new record
2) it's updated when you do something else such as save the record, move to a new
record, or whatever.

Using an unbound form means that the first step is not done.

Yes, that will increase your risk of corruptions but significantly? Try powering off
your system when you have the pencil on the form indicating that a record is being
updated? I've done that and haven't gotten any corruptions.

That same client has had five or ten power failures in the past four or five years.
Never any corruptions due to the power failure. Yes, the server and switches were on
UPSs but the client PCs were not.
[color=blue]
>But I am an SQL Server fan and it's what
>I use most of the time.[/color]

And the SQL Server Express just announced makes that even easier to deploy.

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

Tony's Main Microsoft Access Page

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Hint's, Tips and FAQ

**Jim Allensworth** · Nov 13 '05, 01:55 AM

Re: Is access suitable?

On Sun, 11 Jul 2004 16:19:11 -0400, "Alan Webb" <knogeek@hotmai l.com>
wrote:
[color=blue]
>Jim,
>Access and Jet can do a lot but I've had experience with both Jet SQL and
>Transact SQL and I'll take Transact SQL any day given a choice. My primary
>point is one I am fond of making--that in the choice of whether to buy or
>build a solution it is almost always cheaper to buy a prepackaged solution.[/color]

That depends. The upfront savings can often - and usually is - quickly
overcome by the time consumed with compromised operational
modifications to make the business conform to the software. Sometimes
that works but many times it deficient. If you do a cost benefit
analysis of the project you might find that the break-even point comes
sooner than you might think. Also, in even the smallest operations
(like maybe the OPs) the business owner has to consider what their
time is worth. What is it worth having their Saturdays free?
[color=blue]
>As for Jet's ability to run a company, that is something much debated here.
>I've seen posts that claim developers have built applications that can
>support a couple hundred users. It may be better, but when I was building
>multi-user applications using Jet my seat of the pants impression was that I
>could support roughly 5-10 concurrent connections. You can optimize this by
>building your applications using unbound forms that step into the database
>to transact data and then get out. But I am an SQL Server fan and it's what
>I use most of the time.
>[/color]
I also think that SQL Server is a top notch C/S product, but it often
is overkill for most businesses. Quite simply an unnecesssary expense
when Jet will do superbly. My reading of the OPs concerns is that SQL
Server *would* be overkill. As for an off-the-shelf solution I have
nothing to suggest, maybe a Google search would yield something.

- Jim

**Larry Linson** · Nov 13 '05, 01:55 AM

Re: Is access suitable?

An example of an Access database application for running a modest-sized
business is the Business! database you can license from
http://www.databasecreations.com. It may do what you need and more, and the
source code is included, so you can modify if needed.

But my primary purpose in mentioning it is to assure that Access, without
any additional bells-and-whistles, can create a database application that
will do what you need.

If I were in your shoes, I'd look at this and others (there are reviews of
others at MVP Tony Toews' site http://www.granite.ab.ca/accsmstr.htm) and
consider using one of them either "as is" or as a starting point... you'd
put uncounted hours of effort into creating an application with "generally
accepted accounting principles" that does nearly what the packages provide.

Just for the record, I have heard good reports on this particular package,
but have not used it myself. I also have no connection, business or
personal, with Database Creations, except that I once had an Access book of
which Cary Prague (one of Database Creations principals) was a co-author
(and found it useful).

Larry Linson
Microsoft Access MVP

"script-learner" <jlwilson@webma sters.freeserve .co.uk> wrote in message
news:90da3f22.0 407101332.4da8c a8f@posting.goo gle.com...[color=blue]
> I currently own a fruit and veg business and wish to compuiterise my
> manual methods in order to reduce labour costs. I spoke to my son who
> is a whiz on computers and he had a good chat with me and explained
> that what I needed was possible but he was unsure if he had the
> capabillities to carry it out. He has started drawing some plans and I
> will attempt to explain what I will require from the software. It will
> need to be user friendly and windows based, cover three areas, new
> customer invoice, stock control, and customer database(simila r to
> sage).
>
> All must be interlinked (my son has explained this) when I enter a new
> invoice it will automatically print 3 copies and remove stock from
> current(monitor ed in stock control) which will intern add that invoice
> to the customer database and show an outstanding sum. Firstly my son
> would like to know if there are any peices of software able to create
> such an 'interlinked' database, from reading other posts I figure
> ms-access may be one? If not what language is recommended. If all does
> not go to plan are there IT experts who will take on such a project?
>
> Any help given is greatly apprichiated. J[/color]

**Tony Toews** · Nov 13 '05, 01:55 AM

Re: Is access suitable?

"Larry Linson" <bouncer@localh ost.not> wrote:
[color=blue]
>If I were in your shoes, I'd look at this and others (there are reviews of
>others at MVP Tony Toews' site http://www.granite.ab.ca/accsmstr.htm) and
>consider using one of them either "as is" or as a starting point... you'd
>put uncounted hours of effort into creating an application with "generally
>accepted accounting principles" that does nearly what the packages provide.[/color]

Actually there are links not reviews to accounting systems.

But I would certainly agree that a thousand or three thousand dollars spent on the
software along with source code which you can customize would save the original
poster hundreds if not thousands of hours time.

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

Tony's Main Microsoft Access Page

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Hint's, Tips and FAQ

**David W. Fenton** · Nov 13 '05, 01:56 AM

Re: Is access suitable?

Tony Toews <ttoews@teluspl anet.net> wrote in
news:tfi3f05kuu igh6sjt5d0h9poi t59d0frmh@4ax.c om:
[color=blue]
> So what's the difference between using bound and unbound forms?
> In terms of corruption and such that is. And I'm being quite
> serious.
>
> Using a bound form means that
> 1) an empty record is created when you hit the first key stroke on
> a new record 2) it's updated when you do something else such as
> save the record, move to a new record, or whatever.
>
> Using an unbound form means that the first step is not done.
>
> Yes, that will increase your risk of corruptions but
> significantly? Try powering off your system when you have the
> pencil on the form indicating that a record is being updated? I've
> done that and haven't gotten any corruptions.
>
> That same client has had five or ten power failures in the past
> four or five years. Never any corruptions due to the power
> failure. Yes, the server and switches were on UPSs but the client
> PCs were not.[/color]

Locks.

If the form is bound, the data page is locked the minute you edit
the record.

So, improving concurrency is one of the main reasons to choose
unbound forms for editing (as distinct from unbound forms used as
dialogs).

But in my deployed apps, there are probably maybe 3 unbound data
editing forms.

Of course, I do regularly use an unbound form for adding *new*
records, because it makes it easy to avoid abandoned records, and
also makes it easy to validate the few fields needed to create the
minimal valid record.

But for editing, I usually use bound forms.

And haven't had any problems.

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

**script-learner** · Nov 13 '05, 01:58 AM

Re: Is access suitable?

I, being the son of the original poster, am not time restricted in any
way. To take a year to write the program would be acceptable, my
father can continue to plod along using his manual methods. He is
simply asking me to attempt something. I am willing to go into the
project as long as you lot suggest that it is possible and sensible,
considering i am taking the project on as both an aid to my dad and an
oppertunity for a learning experience. (plus a little cash!) I like to
spend time on a computer and am patient and willing to learn. Business
consists of around 6 people. This i do not see as being important! My
dad is the only person accessing the software for the sole purpose of
doing the processes described in my original post. I hope this allows
further help, thanks for your time. SL

**rkc** · Nov 13 '05, 01:58 AM

Re: Is access suitable?

"script-learner" <jlwilson@webma sters.freeserve .co.uk> wrote in message
news:90da3f22.0 407130023.41ebb 207@posting.goo gle.com...[color=blue]
> I, being the son of the original poster, am not time restricted in any
> way. To take a year to write the program would be acceptable, my
> father can continue to plod along using his manual methods. He is
> simply asking me to attempt something. I am willing to go into the
> project as long as you lot suggest that it is possible and sensible,
> considering i am taking the project on as both an aid to my dad and an
> oppertunity for a learning experience.[/color]

You say you have nothing but time so take a look at the information
at the following url http://www.guides.sk/reldb_dsgn/index.htm#start.

The information presented there is the foundation of any relational
database project using any tool or combination of tools. You need
to wrap your head around the fundamentals in order to succeed using
any tool.

**Bernard Peek** · Nov 13 '05, 01:58 AM

Re: Is access suitable?

In message <90da3f22.04071 30023.41ebb207@ posting.google. com>,
script-learner <jlwilson@webma sters.freeserve .co.uk> writes[color=blue]
>I, being the son of the original poster, am not time restricted in any
>way. To take a year to write the program would be acceptable, my
>father can continue to plod along using his manual methods. He is
>simply asking me to attempt something. I am willing to go into the
>project as long as you lot suggest that it is possible and sensible,
>considering i am taking the project on as both an aid to my dad and an
>oppertunity for a learning experience. (plus a little cash!) I like to
>spend time on a computer and am patient and willing to learn. Business
>consists of around 6 people. This i do not see as being important! My
>dad is the only person accessing the software for the sole purpose of
>doing the processes described in my original post. I hope this allows
>further help, thanks for your time. SL[/color]

The reason why I asked about the size of the business is that it gives
me a good idea about the scale of complexity, and the possible budget
involved.

The best advice is still to buy an off-the-shelf package if you can. One
man-year is not really enough to build a complete business system,
because inevitably there will always be a need to add more features
"wouldn't it be nice if...."

You could easily spend a man-year tailoring a standard package, with the
advantage that you will start off with something that can already do at
least part of what you want. If you build a system from scratch then it
might be months before you have anything that can perform any useful
business function.

Whatever you do don't underestimate the risk of frayed tempers. Building
a software system requires detailed analysis, you will need to know more
about how the business works than your father does if you are going to
write a robust system. You will need to approach the problem in exactly
the same way as any other developer.

Get a written description of what the system is required to do and make
it crystal clear that you are going to develop a system to meet the
written specification. If it isn't in the spec then it doesn't exist.
Anything that isn't written down is NOT YOUR PROBLEM.

There are two important reasons for this. The first is that having to
write the specifications down will make the user, your father, think of
the problem more logically. The second is that if you go under a bus
then it allows someone else to pick up where you left off.

If you intend to use this as a learning exercise then you need to be
clear about what you want to achieve. If you intend to go into systems
design or development as a career then having a project you can show a
prospective employer is a good thing. They will need to see some
evidence of how you designed the system, so notes and diagrams using
some standard notation would be a good idea. Take a look at UML and the
documents it produces. Also take a look at relational database theory,
in particular look at the theory and practise of normalisation
(normalization if you want to read references originating in the US
too.)

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

**Tony Toews** · Nov 13 '05, 01:59 AM

Re: Is access suitable?

"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote:
[color=blue][color=green]
>> Yes, that will increase your risk of corruptions but
>> significantly? Try powering off your system when you have the
>> pencil on the form indicating that a record is being updated? I've
>> done that and haven't gotten any corruptions.[/color]
>
>Locks.
>
>If the form is bound, the data page is locked the minute you edit
>the record.
>
>So, improving concurrency is one of the main reasons to choose
>unbound forms for editing (as distinct from unbound forms used as
>dialogs).
>
>But in my deployed apps, there are probably maybe 3 unbound data
>editing forms.
>
>Of course, I do regularly use an unbound form for adding *new*
>records, because it makes it easy to avoid abandoned records, and
>also makes it easy to validate the few fields needed to create the
>minimal valid record.[/color]

I have one invoicing routine where, if more than one user goes into it these kinds of
locking problems occur. Whereas other portions of the app are nowhere near as
susceptible to these kinds of locking issues. But that's likely because the records
in most of the other system are added in "batch" mode but updated as you go and very
randomly.

Whereas the invoicing records are added and updated immediately. But for now they
are using Excel to do most of the invoicing so I'm not being paid to fix the problem
so it'll have to wait. <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

Tony's Main Microsoft Access Page

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Hint's, Tips and FAQ

Is access suitable?

Is access suitable?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment