Is Access Suitable?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

Im looking into putting a paper based stock control system (approximately
containing 3000 shoes & bags) into a database.
I would like to know if Access is the ideal solution. (Will it be able to
hold all the data? Will it be able to perform queries quickly?)

Thank you for all your help, Louisa
 
The ideal solution (to the extent that "ideal" is possible) is the one that
suits your needs. Access can handle millions of records under many
circumstances (the limitation is the size of the database file, not the
number of records), so no problem there.
That being said, it is often worthwhile to see if an off-the-shelf program
will answer your needs. Access is very versatile, but has a considerable
learning curve, so will probably end up costing far more than the purchased
program if the developer's time is taken into account.
An in-between solution may be to adapt databases that are already available.
The Microsoft web site has an assortment of Access templates, including
inventory management. This link should take you to a page where you can
choose listings based on your version of Access (watch for word wrapping in
your e-mail reader):
http://office.microsoft.com/en-us/templates/CT101527321033.aspx?av=ZAC
 
for once I concur.

of course.. I did work for a company; 5 or so years ago.. and we had
'only 3000 products' but we were a chain with 50 locations.. and I
would reccomend against using MDB for anything if you're planning on
doubling in size the next five years.

This products table I was working on; the company didn't have a ERP
system, MRP system; nothing along those lines.. and I built a fairly
comprehensive Product database for this client.

One of the limitations that was the biggest concern for me?? Was the
limit on column count in Access; I simply couldn't make do with 255
columns and I sure as heck wasn't going to over-normalize.. I mean
it's a friggin product; not 40 records in 30 different tables!
 
I might as well stick my 2¢ in here. I currently support a database with
well over 300,000 records used by 53 users that can find a record in under a
second. The slowest query takes less than 3 seconds.

I have built databases with several million records that take only a few
seconds to lookup any record. With only 3,000 records, a well designed
database on a stable hi-speed network, or on a PC, will return your records
in milliseconds.
 
I just wanted to second the suggestion that you first look for an
off-the-shelf solution. What you are doing--stock control--is not simple.
Getting it right is going to take a bit more expertise than many other
applications might. Therefore, a solution that costs you a few hundred
dollars might turn out to be more cost-effective than trying to build your
own and spending weeks or months getting it right.

FWIW:

My largest Access db currently has over 1,100,00 records in the primary
transaction table.
 
Thank you very much for your reply, it was very helpful.
You said you support a database used by 53 users. So do this mean
approximately 15 users can query the database, simultaneously across a
network?
Also, in the business we have a main office where the database will be and 4
shops where they could access the database. Is this possible to do over the
Internet or a WAN?
Thank you for your time, Louisa Holt
 
Also, in the business we have a main office where the database
will be and 4 shops where they could access the database. Is this
possible to do over the Internet or a WAN?
Click to expand...

No, you can't do it across the WAN.

In that scenario, Windows Terminal Server is a great solution.
Anything else is going to be largely a waste of time and effort, and
it's going to be hard to maintain.
 
David;

you're a jackasss

Access Data Projects work GREAT across a LAN

lose the training wheels; kids

I mean seriously here.
I can do ADP over the public internet; or over a WAN.

you don't need to buy terminal server licenses. that is the most
ridiculous thing I've ever heard.
I mean seriously

Terminal Implementations cost 10 grand

ADP is FREE.
 
ADP IS EASIER TO MAINTAIN DUDE

I mean seriously

'Index Tuning Wizard'
and 'Reporting Services' and 'DTS' and 'SSIS' and 'SSAS'

spend your money wisely
terminal is not required; MDB is a piece of crap ass software
 
Thank you very much for your reply, it was very helpful.
In the main office, will approximately 15 users be able to query the
database, simultaneously across a network?
 
Thank you very much for your reply, it was very helpful.
In the main office, will approximately 15 users be able to query the
database, simultaneously across a network?
Click to expand...

Yes.

Nominally 255 users; in real life, over a hundred *read only* users is quite
practical, given a solid network and well designed queries. I'd be leery if
more than 25 or so users were actively updating the database though. Your
milage may vary!

John W. Vinson [MVP]
 
There are 3 very important issues with regards to using Access as a
mult-user database on a network:

1. Build a well-designed, normalized, properly indexed, database.
2. Install it on a high-quality stable network that well maintained.
3. Train users to not do stupid things like leave their machines on over
night, or turn them off without properly closing down.

Access will not run quickly over a WAN unless you either access it with an
asp or asp.net application, or use terminal services. Of the 2, I prefer the
second because it cost far less and deploys the same database that you've
written for the LAN. There are a few experts here on using terminal servers
(myself, Steve Shapel, and David Fenton come to mind) so you should have no
trouble should you decide to go that route. But first things first. You need
to learn some basic database principles (I suggest books by John L. Viescas
and Michael J. Hernandez as starters) then build a good database. We're here
to answer specific questions when you need some help.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
Holts Shoes said:
Thank you very much for your reply, it was very helpful.
In the main office, will approximately 15 users be able to query the
database, simultaneously across a network?
Click to expand...

With ease. See my last post for a little more information.
 
to these MDB babies; a 'solid network' means

NO VPN
NO WIRELESS
NO GIGABIT
NO WAN
NO PUBLIC INTERNET

ADP works _GREAT_ over all of these platforms.. but when MDB files
start crapping out; these kids point the finger to the networking
guys.

I find it funny

THE BUCK STOPS HERE JUST USE ADP
 
re:
3. Train users to not do stupid things like leave their machines on
over
night, or turn them off without properly closing down.

what the heck are you talking about?

ADP doesn't _REQUIRE_ this crap

you can rebuild indexes while people are hitting the tables; you can
backup the database while people are entering data

MDB is too flaky for real world use
 
yes; if you're asking if ADP can support that many users 'of course it
can'

I reccomend getting a copy of SQL Server developers edition for sure.

it's $49; but it's probably the best 50 bucks you could ever spend

with ADP/SQL you don't need to spend half your time with workarounds
and terminal server setup / config
 
use Developers Edition for development of SQL.. I really reccomend SQL
2000 over SQL 2005; and I reccomend not moving to vista until it
supports sql 2000 (i heard that was going to be allowed with Vista
SP1)
 
But first things first. You need
to learn some basic database principles (I suggest books by John
L. Viescas and Michael J. Hernandez as starters) then build a good
database. We're here to answer specific questions when you need
some help.
Click to expand...

Yes. Terminal Server won't make a database app run well that doesn't
run well on a single PC or a fast LAN.
 
I reccomend a couple of good books on SQL SERVER and ignoring all the
intracies of ACCESS BUGS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Records In A Table 1
Question Regarding Visual Basic 1
is access suitable 1
Help Needed With DSum? 2
Help Needed With DSUM??? 1
Help Needed With Append Query? 7
Need Help With Table Performance 1
Am I disturbing a machine in production floor if I access its database remotely? 4

Back
Top