MS Access Forum / General 2 / March 2007
Is Access Suitable?
|
|
Thread rating:  |
Holts Shoes - 19 Mar 2007 16:14 GMT 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
Kevin3NF - 19 Mar 2007 16:26 GMT Access can hold 3000 records without breaking a sweat, and will perform well-designed queries just fine.
If you google a bit, you should find some templates for stock control on the web that may suit your needs
 Signature Kevin Hill 3NF Consulting http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server: http://kevin3nf.blogspot.com
> Hi > [quoted text clipped - 4 lines] > > Thank you for all your help, Louisa aaron.kempf@gmail.com - 19 Mar 2007 17:41 GMT 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!
> Access can hold 3000 records without breaking a sweat, and will perform > well-designed queries just fine. [quoted text clipped - 18 lines] > > - Show quoted text - BruceM - 19 Mar 2007 16:33 GMT 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
> Hi > [quoted text clipped - 4 lines] > > Thank you for all your help, Louisa Arvin Meyer [MVP] - 20 Mar 2007 03:16 GMT 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.
 Signature Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com
> Hi > [quoted text clipped - 4 lines] > > Thank you for all your help, Louisa George Hepworth - 20 Mar 2007 04:53 GMT 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.
 Signature George Hepworth 2007 Access MVP
>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 [quoted text clipped - 12 lines] >> >> Thank you for all your help, Louisa Holts Shoes - 20 Mar 2007 10:40 GMT 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
> 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 [quoted text clipped - 12 lines] > > > > Thank you for all your help, Louisa David W. Fenton - 20 Mar 2007 15:31 GMT > 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? 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.
 Signature David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
aaron.kempf@gmail.com - 20 Mar 2007 17:17 GMT 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.
> > 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 [quoted text clipped - 9 lines] > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/ aaron.kempf@gmail.com - 20 Mar 2007 17:18 GMT 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 a.s software
> > 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 [quoted text clipped - 9 lines] > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/ Holts Shoes - 20 Mar 2007 17:35 GMT 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?
> > 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 [quoted text clipped - 5 lines] > Anything else is going to be largely a waste of time and effort, and > it's going to be hard to maintain. John W. Vinson - 20 Mar 2007 18:07 GMT >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? 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]
aaron.kempf@gmail.com - 20 Mar 2007 19:04 GMT 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
> On Tue, 20 Mar 2007 09:35:19 -0700, Holts Shoes > [quoted text clipped - 11 lines] > > John W. Vinson [MVP] Arvin Meyer [MVP] - 20 Mar 2007 18:13 GMT > 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? With ease. See my last post for a little more information.
 Signature Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com
aaron.kempf@gmail.com - 20 Mar 2007 19:07 GMT 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
> Thank you very much for your reply, it was very helpful. > In the main office, will approximately 15 users be able to query the [quoted text clipped - 15 lines] > > - Show quoted text - aaron.kempf@gmail.com - 20 Mar 2007 19:08 GMT 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)
> Thank you very much for your reply, it was very helpful. > In the main office, will approximately 15 users be able to query the [quoted text clipped - 15 lines] > > - Show quoted text - Arvin Meyer [MVP] - 20 Mar 2007 18:12 GMT 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.
 Signature Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com
> 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 [quoted text clipped - 27 lines] >> > >> > Thank you for all your help, Louisa aaron.kempf@gmail.com - 20 Mar 2007 19:05 GMT 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
> There are 3 very important issues with regards to using Access as a > mult-user database on a network: [quoted text clipped - 55 lines] > > - Show quoted text - David W. Fenton - 20 Mar 2007 21:21 GMT > 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. Yes. Terminal Server won't make a database app run well that doesn't run well on a single PC or a fast LAN.
 Signature David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
aaron.kempf@gmail.com - 26 Mar 2007 23:21 GMT I reccomend a couple of good books on SQL SERVER and ignoring all the intracies of ACCESS BUGS
> > But first things first. You need > > to learn some basic database principles (I suggest books by John [quoted text clipped - 8 lines] > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/
|
|
|