Would Access work?

[leeinnh]

I work with a commercial work order program at work. The program has lots of
bugs and a lot of bells and whistles that we don't need. I would like to
attempt to design a program with only the things that we really need.
I need to be able to enter work orders, requistions, purchase orders. I
need to track equipment by equipment number as well as how much time is
expended on each piece of equipment.
I need to list each employer by their employee code.
There are other things that I would need as well but right now I would just
like to know if it is possible to develop my own work order program using
Access.
Any info would be greatly appreciated.
Thank you,
Lee

 

[Arvin Meyer MVP]

I work with a commercial work order program at work. The program has lots
of
bugs and a lot of bells and whistles that we don't need. I would like to
attempt to design a program with only the things that we really need.
I need to be able to enter work orders, requistions, purchase orders. I
need to track equipment by equipment number as well as how much time is
expended on each piece of equipment.
I need to list each employer by their employee code.
There are other things that I would need as well but right now I would
just
like to know if it is possible to develop my own work order program using
Access.
Any info would be greatly appreciated.

To Dirk's able comments I would add:

As easy as it is to create simple programs with Access, a full-fledged
complex work order program is not trivial. I have no doubt that it is not
difficult to learn Access and VBA well enough to create almost any program.
That is Access's strength. But you need to consider what your time is worth,
and whether or not it's worth the effort, or your company should hire a
professional to do that.

If you decide to hire the professional, be aware that just hiring a
programmer is not sufficient, and will usually produce the results that you
already have. Find a developer who works exclusively at creating databases
for business, and be prepared to teach him your business. A good database
developer will pick up your business and work effort very quickly.

The best way to determine your answer is to start to work in Access and then
take an educated guess as to how long it will take to do the job. Now double
or triple it because one of the hallmarks of inexperience is to think how
easy it will be. Now get an estimate from a professional. Or, you can hire a
professional to design your structure and give you an estimate, then make
the decision.

 

[Dirk Goldgar]

I would have 25 users but not all 25 would be accessing the program
concurrently.
Maybe 5 or 6 would be using the program at the same time.

That's not a problem. Access is designed to handle that many users just
fine.

We have network access.

If by that you mean access will be over the local network, not a WAN or the
Internet, then that's fine, too. If you need to run over a WAN, things get
more complicated.

We need a high level of security...we need 24x7 uptime with back-up.

Depending on how literally you mean these requirements, they argue for using
a SQL Server back-end, which will probably increase your costs somewhat --
either by a little or a lot depending on what edition of SQL Server you can
use. SQL Server Express is free (with a maximum database size of 4GB), but
you'll still have expenses related to setting it up and adminstering it. If
you already have SQL Server installed, of course, that's not much of an
issue.

By back-end, I refer to the database that actually stores your data. Any
multi-user Access application is best split into a front-end (containing all
the user-interface elements, forms, reports, and code) and a back-end
(containing the data tables). For an Access application, the front-end must
be an Access (Jet/ACE) database, but the back-end can be of a number of
database types. The front-end database, installed on the user's PC, links
to the back-end on the server, and the user never needs to know or care
about the split.

The reasons I asked about security and up-time are:

1. The Access (Jet/ACE) database format is not nearly as secure as that of
an enterprise-scale server database like SQL Server. Essentially, if a user
can get access to the database file, they can crack it and extract data from
it. Also, it's harder to restrict a determined user from seeing data he
shouldn't see. You can design your front-end to limit user access to
specific areas, but it won't keep out a determined, technically savvy user
who understands Access very well.

2. An Access (Jet/ACE) database should be inactive, and ideally closed, when
backups are taken. A backup taken when there is some database process going
on may prove to be invalid later on. Therefore, if your database is going
to be active 24x7, you need to use some other back-end database such as SQL
Server. On the other hand, if you can count on there being a period when
you can reasonably lock the database against all outside connections, you
can backup a Jet database or perform other maintenance at that time.

So you see, you can develop your application with an Access front-end,
giving you all the power of Access as a development environment, and use
either an Access database or a client-server database such as SQL Server as
the back-end, as circumstances warrant.

answer to your question 2: I have no problem taking the time to
learn...learning is like a hobby to me.

That's the fun part!

In regards to my answers, do you think this would be something that could
be
accomplished for my office? Or is the number of users, level of security
or
the need for 24x7 uptime and back-ups a negative for programming with
Access?

I can only hope I've given you more information to make an informed judgment
on the question. *I* would do it, but then this is what I do both as
vocation and avocation.

 

[Armen Stein]

If you decide to hire the professional, be aware that just hiring a
programmer is not sufficient, and will usually produce the results that you
already have. Find a developer who works exclusively at creating databases
for business, and be prepared to teach him your business. A good database
developer will pick up your business and work effort very quickly.

Good point, Arvin.

Ask a "programmer" to write some code for you that does X, Y and Z.
The answer is "okay". And they do. And sometimes that's just fine.
Especially if you are already a good business analyst and you know the
best approach to take already, and the correct database design to go
with it.

Ask a "developer" the same thing. The answer should be "What's the
business problem you are trying to solve with X, Y and Z? Maybe
there's a cheaper/smoother/cooler/simpler/better way to solve it."

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tony Toews [MVP]]

Ask a "programmer" to write some code for you that does X, Y and Z.
The answer is "okay". And they do. And sometimes that's just fine.
Especially if you are already a good business analyst and you know the
best approach to take already, and the correct database design to go
with it.

Ask a "developer" the same thing. The answer should be "What's the
business problem you are trying to solve with X, Y and Z? Maybe
there's a cheaper/smoother/cooler/simpler/better way to solve it."

Now twenty and thirty years ago there was a position called Systems
Analyst which did all the work with the users and business logic and
such. According to Wiki though that has changed somewhat in some
organizations. http://en.wikipedia.org/wiki/Systems_analyst

And we never heard the term developer until relatively recently
especially with the Internet era.

(I always figured the HTML jockeys called themselves developers so I
never liked the term myself.)

So what's the difference? <shrug> Not sure.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Philip Herlihy]

That's not a problem. Access is designed to handle that many users just
fine.


If by that you mean access will be over the local network, not a WAN or
the Internet, then that's fine, too. If you need to run over a WAN,
things get more complicated.


Depending on how literally you mean these requirements, they argue for
using a SQL Server back-end, which will probably increase your costs
somewhat -- either by a little or a lot depending on what edition of
SQL Server you can use. SQL Server Express is free (with a maximum
database size of 4GB), but you'll still have expenses related to setting
it up and adminstering it. If you already have SQL Server installed, of
course, that's not much of an issue.

By back-end, I refer to the database that actually stores your data.
Any multi-user Access application is best split into a front-end
(containing all the user-interface elements, forms, reports, and code)
and a back-end (containing the data tables). For an Access application,
the front-end must be an Access (Jet/ACE) database, but the back-end can
be of a number of database types. The front-end database, installed on
the user's PC, links to the back-end on the server, and the user never
needs to know or care about the split.

The reasons I asked about security and up-time are:

1. The Access (Jet/ACE) database format is not nearly as secure as that
of an enterprise-scale server database like SQL Server. Essentially, if
a user can get access to the database file, they can crack it and
extract data from it. Also, it's harder to restrict a determined user
from seeing data he shouldn't see. You can design your front-end to
limit user access to specific areas, but it won't keep out a determined,
technically savvy user who understands Access very well.

2. An Access (Jet/ACE) database should be inactive, and ideally closed,
when backups are taken. A backup taken when there is some database
process going on may prove to be invalid later on. Therefore, if your
database is going to be active 24x7, you need to use some other back-end
database such as SQL Server. On the other hand, if you can count on
there being a period when you can reasonably lock the database against
all outside connections, you can backup a Jet database or perform other
maintenance at that time.

So you see, you can develop your application with an Access front-end,
giving you all the power of Access as a development environment, and use
either an Access database or a client-server database such as SQL Server
as the back-end, as circumstances warrant.


That's the fun part!


I can only hope I've given you more information to make an informed
judgment on the question. *I* would do it, but then this is what I do
both as vocation and avocation.

I think the OP is asking all the right questions, in the right way, and
could well be encouraged.

Access is a simply marvellous tool, if you are prepared to work fairly
hard at learning it and work with it (and not against it). A beginner,
properly guided, can build powerful and valuable systems relatively
quickly, although it's invaluable to have expert help on hand (this
group is one of the most expert and most helpful I've ever come across,
as it happens).

I guess the test is:
1) Is the OP prepared to invest in some systematic learning?
2) Does the OP have the time to get that learning under his/her belt
before results are expected? (run like mad otherwise).

I'd suggest the OP have a look at the excellent video learning provided
by lynda.com, and/or works through one of the many good books on the
subject. If he or she is prepared to spend an hour most days for
several months, asking questions here when stuck, then there's no reason
a system such as the one described shouldn't emerge, with corresponding
sense of fulfilment and achievement. Access is addictive...

However, if you see this as a one-off, rather than the beginning of a
new skill, then get someone to do it for you.

Phil, London

 

[Armen Stein]

Now twenty and thirty years ago there was a position called Systems
Analyst which did all the work with the users and business logic and
such. According to Wiki though that has changed somewhat in some
organizations. http://en.wikipedia.org/wiki/Systems_analyst

And we never heard the term developer until relatively recently
especially with the Internet era.

(I always figured the HTML jockeys called themselves developers so I
never liked the term myself.)

So what's the difference? <shrug> Not sure.

Tony

The same ideas are recycled under different names. Systems Analyst,
Business Analyst... similar concepts. I was just expanding on Arvin's
point that a "developer" will try to look at the overall business
system beyond just the programming. We've all seen good "programmers"
make some pretty awful system and database designs.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Arvin Meyer MVP]

The same ideas are recycled under different names. Systems Analyst,
Business Analyst... similar concepts. I was just expanding on Arvin's
point that a "developer" will try to look at the overall business
system beyond just the programming. We've all seen good "programmers"
make some pretty awful system and database designs.

I've seen the name Information Systems Architect, and Database Architect as
well.

The point is that someone designing a database needs to be much more
experienced than a programmer. That experience needs to cover general
business operation, and business operation specific to the business the
database is being designed for.

 

[Fred]

The Eskimos need and have 50 words for what we call snow. We probably need
the same thing for "developer"

 

[Tony Toews [MVP]]

The Eskimos need and have 50 words for what we call snow. We probably need
the same thing for "developer"

This Canadian, who shovels the sidewalks, has several words for snow.
Light and fluffy vs heavy and wet. Seriously there is a big
difference between snow at -20 and at 0. Err -10 and 32 American.

I'm also grateful for the neighbour who comes out with his snowblower
when there's an 8" dump of snow and does the entire block.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Fred]

But at the magic number (-40 F = -40C ) snow is bone dry and great for
camping.

For those who want to be the life of the party, bring "Handbook of Snow" an
800 page technical manual.

 

[Arvin Meyer MVP]

But at the magic number (-40 F = -40C ) snow is bone dry and great for
camping.

I've never gone camping in weather that cold. The coldest I've ever been
was -25°F, and I never expected it to get anywhere near that. I used to love
camping at 20°F or so. No bugs, no snakes, no bears. Then I got married.

 

[Fred]

Believe it or not the biggest challenge to deal with with prolonged camping
at that temp is the buildup of condensed and frozen moisture in your gear.
The solution is a snow hours (insulated) or, better yet, heating the tent.
Tell your wife you'll have a warm tent at the same time as great Margaritas
made with the snow. Paradise below zero.

 

[David W. Fenton]

The Eskimos need and have 50 words for what we call snow. We
probably need the same thing for "developer"

The Eskimo thing is an urban legend -- it's just not true.

Also, a frog *will* jump out of a pot of water as it's heating up to
a boil.