Directions Please

[DUNNER7]

I have been working on many ways to solve my database problem. I decided
that instead of going off in numerous directions...I need to stop and get the
immediate solution to the immediate task I want accomplished.

I need a database that is going to hold student demographic information, be
able to store attendance information, and be able to store discipline
incident data. The database is going to have to be able to be seen and used
by about a staff of 50-60 teachers, secretaries, social workers, etc. Our
school has its own server that we run other student information from.

Questions: Is Access the correct program to tackle this project? If I
design this in Access 2007 does everyone need Access 2007 on their machine to
run the database? Can I design the database structure in Access 2007 and us
Visual Basic to design the forms and inputs for teachers and make my own
program attached to the Access database? Or maybe I am totally off base.

Thanks for any input
Del Dobbs

 

[Damon Heron]

If it were me, I would forget VB and use Access to design the forms,
reports, etc. Much easier! The tables can also be part of the access
design, or you can use some sql version on the server. The various pcs
would only need Access Runtime installed (its free!) to run your frontend.
It sounds like you are at the very beginning of a big project, so you may
want to read some Access tutorials, and study db design before starting.

Damon

 

[Jerry Whittle]

A well designed Access database can handle that many users.

Users will need Access to run the database. There is a free Access 2007
runtime that you could install.

Also you could save the database as an MDB file. You can still develop an
MDB database using Access 2007. Those with Access 2003 could then use it.
Actually you may not want to use the new 2007 accdb file format as it doesn't
allow user level security. Seems that security might be an issue with such a
database.

You could use Access as the back end to a web-based solution. Users could
get into the database through a web interface.

 

[John W. Vinson]

Questions: Is Access the correct program to tackle this project?

It's certainly very capable of doing this.

If I design this in Access 2007 does everyone need Access 2007 on their machine to
run the database?

Either Access 2007, or the free Access 2007 Runtime which can be downloaded
from Microsoft.

Can I design the database structure in Access 2007 and us
Visual Basic to design the forms and inputs for teachers and make my own
program attached to the Access database?

You could. It would about triple your development and maintenance effort and
would save you very little. For example, Access has builtin Subforms with
linking taken care of automatically; you'ld need to redevelop that feature in
code in VB. I'd go with Access.

 

[TedMi]

Yes, Access is quite suitable for the application you describe. If you
design it in A2007, then each user workstation must have either a copy of
A2007 or a copy of the runtime-only version. The number of users you mention
is near the recommended upper limit of simultaneous users, but assuming that
not all 60 would be online at once, you should be OK.
The architecture should be like this: A backend (BE) database holding the
tables, relationships and integrity constraints resides on a server. Any
queries and UI objects here are meant for maintenance use by an admin, and
never seen by users. A separate fontend (FE) application links to the tables
in the BE and holds all of the queries, forms, reports, macros and modules
that provide the UI. A copy of the FE should be on each user workstation. To
streamline distribution of FE updates, you could write startup code that
checks for the latest version of FE on a server and downloads it if newer
than what's on the workstation. There have been examples posted in this
group. The links to the tables should be via UNC names (i.e.
\\Server\Path\dbName.accdb), not via mapped drives, because these could be
different on different workstations.
Although it is possible to build the FE in VB, the VBA built into Access is
much more convenient and offers almost the same capabilities. Whatever VB
features it lacks is more than made up by the convenience of bound forms,
which automatically write data back to the DB upon user entry - in VB you
have to write code to perform the writes. Also, the report feature of Access
is very good - with VB, you either need to write lengthy code, or obtain a
third-party reporting tool.
So much for the overall architecture. Now you need to carefully study the
business objectives and rules of your environment, then design database
entities and constraints that successfully model these. That is a
non-trivial exercise, requiring a knowledge of relational DB principles. You
might be able to get assistance here if you provide details of your business
requirements.

Good luck!
-TedMi

 

[DUNNER7]

Thanks for all of the assistance. I should of asked the experts at the
beginning. I have been trying to learn SQL, Visual Studio, etc. I do
eventually want to have it web-based. However, you guys have given me enough
to get a good start. I do not understand what you mean by "RUN-TIME" ACCESS?
I think 99% of my employees have ACCESS 03 on their computers, and about 90%
of them are not skilled with computer issues. I want an interface that
basically gives them the ability to fill out a form and send it to
someone...and view reports.

Thanks Again
Del

 

[Fred]

I know 1/4 of what the other respondents do which is why I understand what
they didn't say.

If you design it as an .mdb any Access 2003 or 2007 (and maybe 1-2 more
generations back to 2000) should be able to open.

If you design in .accdb files only 2007 will open it. But you can get a
free run-time 2007 that can open it (but not program)

 

[TedMi]

If most of your users already have Access 03, then by all means design your
app in that version. You can do that with Access 07:
Office button, Access Options, Popular, Creating databases section, drop
down Default file format and select Access 2002-2003. OK out and create a
Blank database, which will be in .mdb, not .accdb, format.
-TedMi

 

[David W. Fenton]

If you design it as an .mdb any Access 2003 or 2007 (and maybe 1-2
more generations back to 2000) should be able to open.

That is, if you use Access 2003 MDB format or before. There is an
Access 2007-specific MDB format, as well, and you wouldn't want to
use that if you want earlier versions to open it.