MVP Question (database Design & Server Requirements)

[rayrock]

Thanks in advance.

I work at a wholeseller, we currently use a unix server with a unix based
software package for invoicing and wharehouse management. However extremely
expensive and customer support is bad. We are looking into buy a New server
running windows small business 2003 our company has 5 employees.

Question 1. I want to build a new database in Microsoft Access to handle
our Inventory and Invoicing, AR and AP. Is this a good approach? Any
Sujestions or prebuilt templates?

Question 2. I would like to eventually provide real time stock reports to
our web page created in ASP. Is This Possible and what would I need Server
wise?

Question 3. How would I set up my database to be used by 5 or more users?
And what is the limit on Access? What server requirements are need?

Question 4. Access Database is there a size limitation?

Question 5. If I want to buy support for access and what is the yearly cost

Question 6. Can I migrate Unix info in Access.

Question 7. I have customers in China (They Speak English) Can they access
the database and make changes to data?

Thanks

Ray

 

[Allen Browne]

Answers embedded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks in advance.

I work at a wholeseller, we currently use a unix server with a unix based
software package for invoicing and wharehouse management. However
extremely
expensive and customer support is bad. We are looking into buy a New
server
running windows small business 2003 our company has 5 employees.

Question 1. I want to build a new database in Microsoft Access to handle
our Inventory and Invoicing, AR and AP. Is this a good approach? Any
Sujestions or prebuilt templates?

Sounds feasible. You will need an understanding of normalization to ensure
the database is relationally correct.

Question 2. I would like to eventually provide real time stock reports to
our web page created in ASP. Is This Possible and what would I need
Server
wise?

Doable. Might be worth considering whether it is worth creating the forms in
Access as well as creating the web-based forms. If they are both very
similar, it sounds like doubling up. If the web-based stuff is just a small
subset, the Access forms could be worth the effort.

Question 3. How would I set up my database to be used by 5 or more users?
And what is the limit on Access? What server requirements are need?

Theoretical limit is 255 users. In practice a couple of dozen users is no
problem in a well designed mdb.

Question 4. Access Database is there a size limitation?

2GB file size is the major limitation. In practice, Access is suitable
unless:
- You envision millions of records in some tables.
- The database must operate 24 x 7 (can't come off-line for a backup).
- You have many dozens of users.
- Security is a serious issue. (Access security is a padlock, not a vault.)

Question 5. If I want to buy support for access and what is the yearly
cost

Depends who you buy it through, and what you're after.

Question 6. Can I migrate Unix info in Access.

You could read/write the data, but not use the Access interface (forms
reports) in Unix.

Question 7. I have customers in China (They Speak English) Can they
access
the database and make changes to data?

Using the ASP.NET they'll be fine, but not trying to use Access forms and
reports directly over an unstable network. Access is great on wired
ethernet, but doesn't handle any unstable network (not even WiFi) unless you
run the sessions on the server (e.g. Citrix, Terminal Server.)

 

[rayrock]

Thanks for response.

The access database will be on the server, what would I need to install on
the users machine and how would make it point to the database on the server?

Speed will be an issue on the database will access be fine with 5 users?
And is there anything I should get special for the server (i.e. Ram)

I know 2 gig is a huge file, but what if it go's past that? Also how big
can a single table get?

The support I would look for is just a call here or there I know that a call
per issue but I think I would want a yearly subscrip for unlimited calls.

Do you know of any templetes or pre-constructed databases?

Ray

 

[Allen Browne]

Interspersed replies.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for response.

The access database will be on the server, what would I need to install on
the users machine and how would make it point to the database on the
server?

Split the database into a back end (mdb data file, containing only the
tables, residing on the server) and a front end (mde program file, installed
on each workstation, linking to the data file on the server.) If that's a
new idea, see:
Split your MDB file into data and application
at:
http://allenbrowne.com/ser-01.html

Speed will be an issue on the database will access be fine with 5 users?

5 users is nothing to Access. A well-normalized data structure, with
appropriate indexing and a correctly designed interface (no unnecessary
dirtying) on a stable network handles 20 or even 50 users almost
instantaneously.

And is there anything I should get special for the server (i.e. Ram)

Typical server stuff. Hardware is pretty inexpensive these days, so the
network is usually the bottleneck.

I know 2 gig is a huge file, but what if it go's past that? Also how big
can a single table get?

If you anticipate more than 2GB of text data ahead of time, you're talking
tables with many millions of records. In that case, consider using the SQL
Server engine to store the data, and Access as the front end.

The 2GB limit is per file, so in theory you can store each table in its own
mdb, so that each table can be up to 2GB. You have that up your sleeve if
you run into trouble, but it would not be a good way to plan initially.

The size of the Access database will blow out quickly if you store binary
data (pictures, sounds, ...) in the table. However, if you store links to
these objects instead of embedding them, 2GB is a lot of textual data.

The support I would look for is just a call here or there I know that a
call
per issue but I think I would want a yearly subscrip for unlimited calls.

Lots of people in the business.

Do you know of any templetes or pre-constructed databases?

There are more copies of Access around than any other database in history,
and there is no way for us developers to keep track of them all. We usually
encourage potential clients to talk to others in their own industry, to see
what they are using and if there are any off-the-shelf solutions available.
For sample data structures, there are lots of examples at:
http://www.databaseanswers.com/data_models/index.htm

If you decide your needs are specific and you need someone to write it for
you, make sure you have a well-defined spec, covering exactly what is to be
included. If you work in IT, you probably know that already.

HTH