Access's Ability

[DS]

I have to build a database for a college. I'll need names, address,
grades by semester, teachers and high school transcripts in the records.
Also I'll need attendance. The college has roughly 2,500 a year going
through it. Can Access handle this and for how long?
Thanks
DS

 

[Larry Linson]

I have to build a database for a college.
I'll need names, address, grades by
semester, teachers and high school
transcripts in the records.

Also I'll need attendance.

The college has roughly 2,500 a year going
through it. Can Access handle this and for how long?

A single Access / Jet database can be as large as 2 Gigabytes. Access can
also be used as a Client application to any ODBC-compliant server database
(and that is most of them, commercial and open-source). For vital data, you
might want to consider that reliability and recoverability of the data is of
prime importance, more important than "can it handle the volume".

Many server databases have built-in logging, backup, and restore features
that would make them a data store that would give you more "comfort". You'd
can implement similar functions in Access/Jet, but they are not builtin, and
to implement them would require considerable work.

Larry Linson
Microsoft Access MVP

 

[DS]

A single Access / Jet database can be as large as 2 Gigabytes. Access can
also be used as a Client application to any ODBC-compliant server database
(and that is most of them, commercial and open-source). For vital data, you
might want to consider that reliability and recoverability of the data is of
prime importance, more important than "can it handle the volume".

Many server databases have built-in logging, backup, and restore features
that would make them a data store that would give you more "comfort". You'd
can implement similar functions in Access/Jet, but they are not builtin, and
to implement them would require considerable work.

Larry Linson
Microsoft Access MVP

Thank You Larry, Will SQL Server do the trick. Also how would I do
Loggong, BackUP and Restore with Access, at the very least can you
suggest where I would start or read about it.
Thank You
DS

 

[dbahooker]

Access MDB doesn't have the ability to Log or Backup.

it is reccomended that all new development be in SQL Server; and that
has been the case for almost 7 years now.

Don't listen to these grayhairs that are too lazy to learn Access Data
Projects.

For the record; ADP are a great tool for managing data in a SQL Server
database.

-Aaron

 

[DS]

Access MDB doesn't have the ability to Log or Backup.

it is reccomended that all new development be in SQL Server; and that
has been the case for almost 7 years now.

Don't listen to these grayhairs that are too lazy to learn Access Data
Projects.

For the record; ADP are a great tool for managing data in a SQL Server
database.

-Aaron

Thanks. So is it hard to go from Access to SQL Server? Are they Similar?
Thanks
DS

 

[Larry Linson]

Thank You Larry, Will SQL Server do the trick.

Microsoft SQL Server, or other ODBC-Compatible Servers, can be used. I'm not
familiar with _all_ of them, but many/most do have logging and recovery.
Some are open-source (free) and others can be very expensive. Price is not
a good way to determine functionality nor ease-of-use. I worked on one
project with only about 25 users, that used MS SQL Server just because of
the reliability and recoverability needs -- a split Access/Jet multiuser
database would have provided them with adequate response time.

Also how would I do Loggong, BackUP and
Restore with Access, at the very least can you
suggest where I would start or read about it.

1. You'll have to limit updates to Forms, or to code that you include,
because if you allow users to update Tables directly, or to write their own
Queries, you can't trap the updates to log them.

2. On every Form where update is possible, you'll have to log the change by
writing it to an external file, or to a log table in the DB.

3. You'll have to create a manual or automated process to ensure that the
database is backed up (copied on a regular basis) and that the log file is
copied along with it and properly identified.

4. You'll have to create a process, combination of manual and automated, to
restore a particular backup and to apply all updates on log files since that
date to the restored backup.

The above description is, of course, "flying at 30,000 feet"; actually
designing, implementing, and especially adequately testing such a facility
is going to take a lot of work.

I believe one or more of the Access MVPs has some backup/logging/restore
discussions and/or examples. I suggest you Google to see if you can find a
reference.

But, frankly, I don't think I'd put in the time and effort necessary to
create my own backup/log/restore facility in Access unless I had an
inordinate amount of free time on my hands and unless watching paint dry was
the most exciting thing I could do with that free time. <GRIN>

Larry Linson
Microsoft Access MVP

 

[dbahooker]

moving to SQL Server is DIFFICULT.

but it's not undoable.

what it comes down to; is that the professional training resources that
are available for SQL Server compared to what's available for MDB???

you can buy a cheap book or something to learn about SQL Server
optimization.

when was the last time anyone saw a book on MDB optimization?

SQL Server is worth the price in development time.
if you care enough about your data to build a database; build it on a
database that will be around a year from now.

MDB is DED.

-Aaron