Which type Database to use?

[Guest]

I need to develop a database for data input and report generation for a small
group. There will be 20 - 40 users, and <5 administrator types, to run
reports. I'd like to develop excel "logs" for the 20 - 40 users to document
their activities. These excel pages will be linked to the database. Data is
not sensitive, so I don't think security is a major issue. Users will be in
multiple locations. I'm familiar with Access, but not SQL Server. Do I need
to build this with SQL Server? If so, suggestions for user-friendly
reference material is appreciated. (I understand how to use Access, but I'm
not a developer.) I appreciate your thoughts.

 

[Joseph Meehan]

I need to develop a database for data input and report generation for
a small group. There will be 20 - 40 users, and <5 administrator
types, to run reports. I'd like to develop excel "logs" for the 20 -
40 users to document their activities.
These excel pages will be linked to the database.

Do you mean you want them to use MS Excel to enter data? Why? Access
does very nice input forms.

Data is not sensitive, so I don't think
security is a major issue. Users will be in multiple locations. I'm
familiar with Access, but not SQL Server. Do I need to build this
with SQL Server? If so, suggestions for user-friendly reference
material is appreciated. (I understand how to use Access, but I'm
not a developer.) I appreciate your thoughts.

I assume they do not share a LAN as you refer to "multiple locations."
I will let someone with experience in this situation address that part of
your question.

 

[Guest]

Thanks for the response. Yes, they are connected via LAN network. My
concerns were that there could be too many users accessing the database at
the same time. Also, they currently do not have the Access applications.
Sooo, thought the MS Excel might be the way to go to get their data into the
database. Since I am not a developer, just experienced user, am I getting in
over my head?

Thanks!

 

[Guest]

Or, should I use the split database option to resolve potential issue? If I
do split database, will linked MS Excel document be considered front end or
back end?

Thanks again!!

 

[Joseph Meehan]

Or, should I use the split database option to resolve potential
issue? If I do split database, will linked MS Excel document be
considered front end or back end?

Yes, Even with two users, you should split the database. Access has a
limit of 255 users at any one time. The general rule is that any well
designed split database can handle about 50 smoothly. Poor design or very
large numbers of users at the same time, can cause problems in which case
you may want a different solution.

It sounds to me that a split Access database should be fine for your
needs.

As for the MS Excel question, we need to know more about the general
design and what is going on. For starter's why do you want to get Excel
mixed up in this?

 

[Arvin Meyer [MVP]]

I've run Access MDB databases with up to 53 users actively editing data. I'd
say try to work with Access first and go to a SQL-Server back-end if you
start encountering problems. Definitely read up on good design before you
start. I'd recommend one of John Viescas's books, and perhaps:

Database Design for Mere Mortals by Michael J. Hernandez

I'd avoid using Excel for input because most folks will start formatting the
Excel data and add totals cells, causing significant problems with your
input. Instead, if you don't want to buy full versions of Office (highly
recommended) go for building runtime versions with one of the Developer
versions. When you run into a problem, come back here. We'll be glad to
help.

 

[Guest]

I really appreciate your response. I understand now that MS Excel is not the
way to go (thank you!).

The needs have changed slightly from my note yesterday (sorry....but things
are evolving as we go along). Now, there will be a WAN networked server, and
number of users needs to be able to grow quickly to 250 - 300. (Server not
in place yet.) Users (ones who will be inputting their data) currently have
Small Business Edition, and do not have the Access application. I'm thinking
that we will need to use SQL Server as the back end. To keep costs down, we
can purchase the Developers version of Access, so that the users can input
their information on the front end into Forms. Other decision to be
made....should we go with Office 2007? Users currently have 2003. Does this
sound do-able? Thanks!!!

 

[Arvin Meyer [MVP]]

With much over 50 users, I'd use SQL-Server as the data engine without any
question whatsoever. If more than 50 concurrent users will be on the WAN,
and Access front-end is probably not your best solution. Perhaps it is time
to hire a professional and have an ASP or ASP.NET application built. If the
overwhelming majority of users will be on the LAN and never more than 50 on
a WAN, you could conceivably still build it in Access and put the WAN users
on a Terminal Server. At between $6K and $8K a server running Terminal
Services will be expensive, but not anywhere near the cost of building a web
application which typically costs 8 to 10 times as much (or more) as an
Access application.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Thank you!!

With much over 50 users, I'd use SQL-Server as the data engine without any
question whatsoever. If more than 50 concurrent users will be on the WAN,
and Access front-end is probably not your best solution. Perhaps it is time
to hire a professional and have an ASP or ASP.NET application built. If the
overwhelming majority of users will be on the LAN and never more than 50 on
a WAN, you could conceivably still build it in Access and put the WAN users
on a Terminal Server. At between $6K and $8K a server running Terminal
Services will be expensive, but not anywhere near the cost of building a web
application which typically costs 8 to 10 times as much (or more) as an
Access application.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com