Access upper limit

[Tom Ellison]

Dear Mark:

There are so many variables in choosing a book for you. What is your level
of competence? What are your specific interests? How far are you likely to
be going with this?

Fortunately, there is a very good solution. Spend an afternoon in a book
store that has a large selection. Set your goals before you get there, and
shop for specific elements you want to accomplish. Make sure of readability
at your level of experience.

I truly believe this will serve you best. This is the way I do it!

Tom Ellison

Dear Tom:

I read your reply and I have a question. I bought the Microsoft SQL Server
2000 Personal Edition CD. I installed it on my computer. Now I cannjot
figure
out where to even begin using the program and how to get it to work with
Access.

Do you have any suggestions on any books or other resources I can use to
get
started learning what to do?

Thanks
Mark

 

[Guest]

Well, you just gave me a scare... I have been told that my 2003 Access
database is pushing the edge at 131 mb for a database that has five users who
are adding and changing data on a daily basis. I've been told that we need to
move to SQL Server ASAP to avoid database corruption issues. Are these
concerns valid?? We're a non-profit, so I hate to spend development money if
it is not needed. What "front end" components does Access have that SQL
Server does not? I have developed several smaller Access databases that are
interlinked with the 131 mb db. I don't want a nightmare if we convert just
the one db. And I don't want to have to learn SQL Server.

Thanks for any advice you can give me!

Dear Larry:

There are pros and cons to this discussion. As someone who has "made this
switch" I can offer you some observations.

First, SQL Server is NOT a replacement for Access. SQL Server does not
include the "front end" components that Access does. You could certainly
continue to use Access for 2/3 of the work. Only the tables and queries,
with subsidiary functions like data rules and relationships, would move to
SQL Server. Access works well with SQL Server to provide forms and reports,
which are a lot of the effort you'll be expending. Indeed, this marriage is
sufficiently successful that Access ships with the MSDE database engine,
which is "full scale SQL Server" in terms of function, but not capacity.
(SQL Server reportiong and several other peripheral functions are not
included. Buy Developer Edition of SQL Server by all means!)

So, what do you get? What are the advantages of this change?

SQL Server capacities, such as Stored Procedures and User Defined Functons
integrate with your Access application (front end) very well. I have had
spectacular results with this. In one case, I replaced a module that
manipulated my data in Access, requiring 30 minutes, with a UDF that did the
same thing in 2 seconds. Now that kind of thing requires some quite
advanced analysis and programming, but I credit SQL Server with providing
superb tools that permit that kind of thing to happen. I would say I am
amazed at what SQL Server has accomplished, not what I did. But you'll
probably need to work with it a year or two before you would start enjoying
such benefits, unless you're tapping into something someone else has
developed and published. There's a lot of that, too!

SQL Server MSDE gives you an upgrade path to virtually unlimited database
size and performance. Having multiple processors and even multiple servers
working together to provide you with more and more capacity and performance
is a potential huge advantage.

I could go on and on.

The disadvantages are mostly that there is a lot to learn to be able to take
advantage of all this. How quickly you will begin to be able to take
advantage of this depends on your current level, experience, capability, and
the effort you put into it. It's not for everybody. It's not for the
"typical" Access novice, nor for many intermediates. It is a huge step for
a good, professional database programmer who is ready for more advanced
tools to learn, with the corresponding rewards.

The product is reliable, stable, and highly productive in the right hands.
If you're ambitious and competent for such a challenge, the cost is small to
get started. Buy some books, Developer Edition, and dig in. If the effort
is too much right now, put it on the shelf. You may be drawn back to it
later. $200 may get you started, plus a weeks worth of time to get into it.

Good luck. Let me know how it goes.

Tom Ellison

 

[Larry Linson]

Well, you just gave me a scare... I have been
told that my 2003 Access database is pushing
the edge at 131 mb for a database that has
five users who are adding and changing data
on a daily basis.

131MB is "child's play" for Access. Many people are running databases that
push the 2GB limit, with millions of records in their tables. Five users,
likewise, is "child's play" for Access in a multiuser Access - Jet
environment.

I can't see preceding messages in this thread, so I don't know what you were
told, nor who told you. Of course, especially with the anonymity of the
Internet, anyone can tell you anything. But, them telling you that doesn't
necessarily make it so -- I could tell you that the sun is going to rise in
the western sky in the morning, but I wouldn't expect you to be so gullible
as to set your alarm to wake up early and be out on the lawn at sunrise so
you wouldn't miss this once-in-an-eternity event. "Adding and changing data
on a daily basis" is rather imprecise, but a well-designed and
well-implemented Access-Jet multiuser database can easily handle ten times
that many concurrent users, not just "daily" users. (And, I've worked on
databases where users updated the information daily, and there were rarely
ever even two users concurrently logged in.)

I've been told that we need to move to SQL Server
ASAP to avoid database corruption issues. Are these
concerns valid?? We're a non-profit, so I hate to
spend development money if it is not needed.

It's possible that your database may have some specific situations that make
it more vulnerable than most, but, as a general rule, this sounds like the
"scare tactics" often used by people wanting to make money off a conversion
project. Even if you do have such specific situations (and only some
detailed study would likely reveal that), it would likely be much more
cost-effective to identify and resolve those situations than launch a
conversion project.

What "front end" components does Access
have that SQL Server does not?

SQL Server has no "front end components". You can store your data in, and
retrieve it from, SQL Server, but you can't build a user interface to the
database with SQL Server as you can with Access.

I have developed several smaller Access data-
bases that are interlinked with the 131 mb db.

I don't know what, specifically, you mean by "interlinked." But, in my
experience you can use SQL Server as a back-end, with some Tables in the
user's front-end. In the database applications I worked on, we used
front-end Tables for rarely-ever-changing Lookup Tables (e.g., U.S. states
and Canadian provinces) and for some other Lookup Tables that did change
occasionally but were frequently used -- which we'd download from the back
end when a user started up the front-end).

I don't want a nightmare if we convert just
the one db.

My observation is that, if you have good SQL Server personnel to help you
over a few rough spots, "nightmare" would be too strong a word. On the
other hand, server databases need more "administration" so you had best plan
on at least a part-time Database Administrator (DBA) if you move.

And I don't want to have to learn
SQL Server.

As with most anything in the computer business, "you've gotta know what
you're doing." If you switch your back-end storage to SQL Server, someone is
going to have to know SQL Server -- the administrator, for sure, and, if you
are the developer, you, too, unless you are willing to produce a junk
application.

Further, if you let someone scare you into switching, you'd better ask them
what they suggest you use to create the "client application." If it is
going to be running on a LAN, view all their suggestions with some
skepticism if they don't say, "Oh, you can stick with Access for that part."

Finally, here are some observations I've posted before on the subject of the
number of users a multiuser Access-Jet system can support:

"Factors in how many users can be supported in multiuser include the
requirements, design, and implementation of the database application and the
hardware, software, and network environments. If all factors are near
perfect, we have reliable reports of over 100 concurrent users. Even if not
all are near perfect, we routinely see reports of 30 - 70 users. But, in
cases where we are rather sure that all are about as far from perfect as can
be, people have reported Access "falling over" with as few as four users.

I'd venture to guess that if someone went out of their way to do everything
wrong, it would be possible to create a database that wouldn't even support
one or two users. <GRIN>"

There's lots of detailed information available on avoiding performance
problems and avoiding database corruption in a multiuser environment. MVP
Tony Toews' site http://www.granite.ab.ca/accsmstr.htm is one that has a
great deal of such information.

Larry Linson
Microsoft Access MVP

 

[Van T. Dinh]

Have you been doing Compact & Repair of the Back-End MDB file?

If you haven't, then your database is a lot less than 131 MB (which is quite
OK as Access / JET can go up to 2 GB).

I am a lot more conservative than Larry though. I would look for an upgrade
path at around 500 MB. Not that I experienced any failure of the JET
Back-Ends but I like to have proper back-up procedure if the database gets
big. MS-SQL Server and other database servers have things like full
backups, differential backups, transaction log backups to help securing the
data.

BTW, it is not that hard to learn the *basics* of MS-SQL Server if you know
Access / JET well. I have upsized a number of Access applications from
Access FE / JET BE to Access FE / MS-SQL BE and basically used books and Web
resources to handle the MS-SQL Server BE's without difficulties. Some of my
clients don't have SQL Server administrator and I become the "default" SQL
Server administrator also.

--
HTH
Van T. Dinh
MVP (Access)

Well, you just gave me a scare... I have been told that my 2003 Access
database is pushing the edge at 131 mb for a database that has five users
who
are adding and changing data on a daily basis. I've been told that we need
to
move to SQL Server ASAP to avoid database corruption issues. Are these
concerns valid?? We're a non-profit, so I hate to spend development money
if
it is not needed. What "front end" components does Access have that SQL
Server does not? I have developed several smaller Access databases that
are
interlinked with the 131 mb db. I don't want a nightmare if we convert
just
the one db. And I don't want to have to learn SQL Server.

Thanks for any advice you can give me!

 

[Arvin Meyer [MVP]]

Well, you just gave me a scare... I have been told that my 2003 Access
database is pushing the edge at 131 mb for a database that has five
users who are adding and changing data on a daily basis. I've been
told that we need to move to SQL Server ASAP to avoid database
corruption issues. Are these concerns valid?? We're a non-profit, so
I hate to spend development money if it is not needed. What "front
end" components does Access have that SQL Server does not? I have
developed several smaller Access databases that are interlinked with
the 131 mb db. I don't want a nightmare if we convert just the one
db. And I don't want to have to learn SQL Server.

I can only give you advice based upon 14 years experience with Access, over
10 of which have been as a professional database developer. I haven't seen
every database, but I've seen quite a few of them.

What you were told above is absolute nonsense. I presently work for a
Fortune 500 company (and I have worked for several others as a consultant)
and I develop and administrate a database that exceeds 100 MB size and has
up to 53 concurrent users. We haven't corrupted anything in 3 1/2 years when
a bad Dell WiFi access point dropped connections. Even then I was able to
fix it in about a 1/2 hour. The largest database I've ever worked on was one
built in Access 2.0, which had a 1 GB size limit. That one had 78 databases,
each with a single table chained together to form a database that was about
30 GB in total size. It corrupted once in 6 years due to a SQL-Server
"solution provider" purposely unplugging the machine to create FUD. I fixed
it then, losing 1 record in the process, and haven't heard from them in the
last 4 years.

Yes, Jet databases can corrupt, so can Oracle, SQL-Server, and any other
database. Server based systems do corrupt less, and if administered
properly, do have robust methods of recovery. The 2 worst corruptions that I
ever encountered were on SQL-Server. One needed rebuilding and I was able to
import almost all of the data. The other was a total loss, I even had to
wipe the drives. Neither was caused by something within the database engine.
They were caused by equipment failure and Administrator stupidity.

SQL-Server has no front-end components, other than query builders. The good
news is, that should you ever decide that SQL-Server is required (and there
are things that require it, like securing transactions), you can use your
Access front-end "as is" and convert your data to SQL-Server. Later, you can
take your time and do optimizations.

So, the correct question here should be: "How can I assure that my Access
database will function worry-free for the next several years until I need to
once again re-evaluate my situation?" The answer is backup and maintenance.
Make sure your equipment is working well, not over 4 or 5 years old, and not
running near capacity. Make sure your staff understands how to shut down in
case of emergency. And buy an external USB hard drive and use it to keep
your system backed up.