Access limitations

[Sasha B]

Hi I am a Network Admin (not a programmer) who started creating a Company DB
programs with Access. I want to be proactive and understand why and when I
should consider moving from MS Access to MS SQL. Can somebody tell me what
limitations Access has? When I should be ready move to MS SQL?

Thank you in advance,

Alex

 

[DavidF]

Sasha, You should be able to handle 10 simultaneous users without any
apparent problems using the Jet engine. Microsoft will tell you that you can
do up to 25, but it all depends on the situation (complexity of software,
network, processors, etc.); hence I think 10 is a good planning number. You
can later split your application so that each user has their own local copy
of front end items (forms, reports, etc.) and the Jet engine is on the
network. Finally, you can replace the Jet engine with MS/SQL and still use
the Access front end if you really need the horsepower later on. Hope this
helps.

 

[Arvin Meyer [MVP]]

David, Thank you for your observations. Unfortunately they bear no
resemblance to reality. The fact is that the stated specifications for
number of concurrent users in a JET data engine is 255, not 25. That said,
the number is optimistic. Typically, the number considered by some
developers to be useful is 20 concurrent users. I have been an Access
developer for over 15 years and have been a Microsoft Access MVP for 8 of
them. I have successfully deployed a 115 MB Jet backend with a dozen
different Access front-ends and 53 concurrent users opening a total of 75
connections to that back-end. This ran without problems for over 7 years
until it was replaced with a web application. Only corruption was from a bad
Dell aircard, unplanned downtime over 7 years, less than 1 hour per year. I
have worked on single user databases with multiple back-ends that totaled
over 30 GBs.

Sasha,
In order to successfully run Jet back-ends, you need a quality network. Junk
machines with bad video or NICs are the bane of a worry-free operation.
Consider moving to SQL-Server back-end (you never need to change the
front-end) when:

1. You have mission critical data which needs to be up 24/7.
2. You have sensitive data (HIPAA or SSNs) which need to be protected from
inside malevolence as well as anything bypassing your firewall.
3. You run a web interface with a large amount of hits.
4. You need to backup and do maintenance while the database is running.
5. You need to run large server-based transaction processing.

Also, NEVER run any front-end from the server. ALWAYS split the database and
run the front-end from workstations.

 

[Larry Linson]

The max limit on concurrent users could be 10 to 30 (more
common) or up to 100 or more (less common).

Do you have a reliable source for your "lower max limit"? The general
concensus among Access MVPs of my acquaintance is considerably higher, even
if many factors aren't near "perfect". Of course, you are not an MVP of my
acquaintance. Why don't you drop in to the private Access MVP newsgroup and
introduce yourself, if you have some fear of posting your profile?

Chris
Microsoft MVP

Larry Linson
Microsoft Office Access MVP

 

[a a r o n . k e m p f]

if you have a single record and a single table; then you should move
to SQL Server.

upsize to the ADP format; and take a class on SQL Server.

-Aaron

 

[a a r o n . k e m p f]

re:
Only corruption was from a bad
Dell aircard

Don't blame the network; blame the database.

And just because you're a newbie that's been stuck in the first grade
for the past 15 years-- that doesn't mean that Access is right for
everyone.

When all you've got is a hammer-- everything looks like a nail.

-Aaron

 

[(PeteCresswell)]

Per Sasha B:

Can somebody tell me what
limitations Access has?

I take "Access" to mean the JET back end.

One thing that I haven't seem mentioned - but which I'm starting
to run into - is Wi-Fi cards. Just about everybody at one
client's campus has a laptop equipped with a Wi-Fi card and the
campus has Wi-Fi available to those laptops.

When people go from their own desk to another desk, they don't
bother plugging into an Ethernet jack - instead they just rely on
the Wi-Fi connection.

Bottom Line: my experience has been that JET back ends don't work
very reliably over Wi-Fi.

Might be just a series of coincidences.... but it's starting to
add up on my end and I suspect a problem.

 

[Tony Toews [MVP]]

Why don't you drop in to the private Access MVP newsgroup and
introduce yourself, if you have some fear of posting your profile?

We've had this discussion before in another newsgroup. Chris posted
using the MS forum software using his LiveID which was recognized by
the forum software as being an MVP. So I'm satisfied Chris O'C is not
masquerading as an MVP.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

One thing that I haven't seem mentioned - but which I'm starting
to run into - is Wi-Fi cards. Just about everybody at one
client's campus has a laptop equipped with a Wi-Fi card and the
campus has Wi-Fi available to those laptops.

When people go from their own desk to another desk, they don't
bother plugging into an Ethernet jack - instead they just rely on
the Wi-Fi connection.

Bottom Line: my experience has been that JET back ends don't work
very reliably over Wi-Fi.

Might be just a series of coincidences.... but it's starting to
add up on my end and I suspect a problem.

Yes, that's been mentioned in the past as being troublesome with
respect to corruptions.

Wireless cards and WAN access are a major reason why I'm looking at
working more and more with SQL Server even on apps with only 10K or
100K records. While a bit more work at my end this will be cheaper
for the clients than going with Terminal Server.

Mind you, for one of my "shrink wrap" apps this will be optional.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Larry Linson]

We've had this discussion before in another newsgroup. Chris posted
using the MS forum software using his LiveID which was recognized by
the forum software as being an MVP. So I'm satisfied Chris O'C is not
masquerading as an MVP.

The possibility of harm of any kind coming from introducing him/herself in
the private MVP newsgroup is negligible, Tony. The ability to do so is
provided, and Chris is entitled, but an MVP hiding his/her identity from
fellow MVPs makes me wonder, "Why?"

 

[a a r o n . k e m p f]

I agree with your findings.

the answer is to move to SQL Server.

-Aaron

 

[Tony Toews [MVP]]

The possibility of harm of any kind coming from introducing him/herself in
the private MVP newsgroup is negligible, Tony. The ability to do so is
provided, and Chris is entitled, but an MVP hiding his/her identity from
fellow MVPs makes me wonder, "Why?"

We've also had this discussion and I've suggested he visit the private
newsgroups as well.

Chris O'C works for a company which is exceedingly careful about
employees having contact with competitors. Those weren't his exact
words but an approximation.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Arvin Meyer [MVP]]

I have to agree. Reliable WiFi connections are rare. The problem is that a
dropped connection can corrupt any file which is being written while it is
open. Access memo fields are susceptible to that specific corruption because
their pointer is stored in the table, but the data is not. Fortunately, that
corruption is generally very easy to fix.

The solution may, indeed be a server based system, but if only a few
connections are WiFi, it may just be easier to use RDP and even more secure
to use a VPN, not so much because it will make Access more reliable, but
that it will make everything more reliable. If there is significant WiFi
connectivity, I'd prefer a server based system as well.

 

[a a r o n . k e m p f]

bullshit.

Wireless connections never corrupt SQL Server data!!!

If your so-called database keeps on corrupting-- stop blaming the
network, and start blaming the database.

Move to SQL Server and ADP-- 'it just works'

-Aaron

 

[David W. Fenton]

Bottom Line: my experience has been that JET back ends don't work
very reliably over Wi-Fi.

This is absolutely true. WiFi should be treated like a WAN
connection insofar as Access/Jet is concerned.

 

[David W. Fenton]

Chris O'C works for a company which is exceedingly careful about
employees having contact with competitors. Those weren't his
exact words but an approximation.

It seems no more persuasive to me in your paraphrase.

 

[John W. Vinson]

It seems no more persuasive to me in your paraphrase.

So... Chris should publish his MVP connection, and risk getting fired by his
outraged employer. You're ready to hire him at his current income or better?

Some employers ARE very jealous of their employees' time. Your disbelief does
not change that fact.

 

[a a r o n . k e m p f]

in other words-- he's saying 'when your database sucks blame the
network'.

That is not a good conclusion.

when your database sucks then move to SQL Server.

now _THAT_ is a good conclusion.

-Aaron

 

[a a r o n . k e m p f]

more importantly-- the mob tactics on this group include 'narking on
people for posting on the newsgroups on the personal time'.

Maybe 'OC doesn't want to be compromised by your mob tactics?

-Aaron

 

[David W. Fenton]

So... Chris should publish his MVP connection, and risk getting
fired by his outraged employer. You're ready to hire him at his
current income or better?

No, that's not what I'm arguing. I'm arguing that he should post to
the MVP newsgroup, which wouldn't in any way compromise his position
with his employer.

Some employers ARE very jealous of their employees' time. Your
disbelief does not change that fact.

You assumed the wrong meaning of what I wrote. Remember, that all
Larry asked was that he post in the MVP newsgroup, *not* that he ID
himself publicly. And it was to *that* that I found your paraphrase
of Chris's situation an unpersuasive respose.