Microsoft Access concurrent users

[Brian Boynton]

Hello,
I would like to implement my database so roughly 30 points of contact
(no more than 10 users at a time I would imagine) can update
information to the database at their leisure. I saw someone had asked
this question and the response was roughly "...sure, as long as the
database is designed well with a good server backend".
I wish this response had been a little more in depth, as it leaves me
with a good number of questions. So...in order to have concurrent
users, I have to have a separate DB Front-end and Back-end? I can't
just have a bunch of Linked Excel Spreadsheets for my users to fill in
whenever they want, with the fields linked to Access Tables?

That's what I want, as opposed to having to occupy the resources of a
server, I would rather just have Microsoft Access and a Bunch of Flat
Excel Files for my users to access to input their data. Upon closing
the spreadsheet the data would be updated on the corresponding access
tables.


Thanks in advance for any guidance anyone offers on this project of
mine.

Regards,
Brian

 

[microb0x]

I would suggest an Access front-end(containing your forms) for the
users to each place on their desktop for entry. And a Access
back-end(containing the tables) placed on a network drive somewhere
they call all access. Then just link the tables on the back-end to the
front-end.

You could just place a single .mdb or .mde file on a network drive
containing both the tables and forms and have everyone use that single
file. I would suggest using my first suggestion however.

In both cases, I have had 16-30 concurrently accessing the database and
entering data. This doesn't mean you wont ever have problems and I
would need more information for your specific case. Factors like how
much data is being updated at the same time, which tables everyone is
writing to, etc. play in to how successful either approach would be.

 

[Jerry Whittle]

While what you propose MIGHT work, I wouldn't depend on it. I've created a
number of Access databases that have 10-25 concurent users. These databases
are well designed (patting myself on the back); split with a FE on each
user's machines; and on good networks (pat the network guys and gals on the
back).

On the other hand I have been called in to fix, or attempt to fix, poorly
designed databases, some not split, on lousy networks that had trouble with 5
concurrent users. None of these depended on linked Excel spreadsheets either.

Speaking of Excel and using it in links:

Due to a Patent dispute, you cannot change, add, or delete data in tables
that are linked to an Excel workbook in Office Access 2003 or in Access 2002!

http://support.microsoft.com/kb/904953/en-us

You can't have indexes on the spreadsheets. This could slow queries down.

You can't assign a primary key or unique constraint to keep out duplicates
in the spreadsheet.

While you can create a relationship in the Relationships window, you can not
enforce referential integrity.

Roger Carlson added the following to a similar discussion.

1) It's awfully easy for a user to mess up the spreadsheet so it won't be
usable as a table. I have an application where I get the data from users in
spreadsheets. Every time, I have to manually modify the tables before I can
import them. There is no way I could use them as linked.

2) You can't change the datatype of a column in Excel as it is linked in
Access. Access may decide that a field is numeric when in fact it is
alphanumeric and you'll get a #NUM in some fields.

 

[Brian Boynton]

What's an FE? Full Executable?

Not sure what you're suggesting as far as my Access Front End and Back
End are concerned, although I very much appreciate the Excel advice.
You've convinced me.

-Brian

 

[dbahooker]

FE stands for front end.

these dipshits around here have this idea that you can translate a mdb
into a magic client-server database.. by simply splitting it.
then they refer to the front-end and back-end
hence FE and BE

technically; they're all full of shit.

use Access Data Projects and keep your data and your queries in one
place-- a sql server database.

spit on anyone that tells you otherwise.

 

[Jerry Whittle]

FE = Front End
BE = Back End

Sorry for the confusion.

 

[microb0x]

Slanderous comments like yours below do nothing for proving any point
you attempt to make. This just makes you look more ignorant than you
claim others to be.

If you don't have a positive critisism to make just keep it to
yourself. These forums aren't a place for you to get on your soap box
and preach to us.

 

[aaron.kempf]

Microb0X

I'll preach wherever I want. How dare you try to tell me what I am
allowed to talk about.

MDB are for sissies and you can eat shit.

Grow up and use a real database, like SQL Server or mySql.

My 'positive criticism' says don't use MDB files for anything; and
you're a complete sissy if you use them for anything.

-Aaron

 

[microb0x]

If you are so against the use of .mdb files why are you even viewing
posts in a forum dedicated to MS Access? And your second worthless
response just re-afirms my comment about your ignorance. I guess some
people are just too dumb to be helped...

If you were any type of a real developer in any corporate environment
you would know that there is a difference between a desired approach to
a problem and working with what is provided to you. A SQL server or
mySql may not be available to the original poster. Unfortunately you
are too busy showing us how robust your obscene vocabulary is to
consider things like this.

Feel free to respond with another worthless rant, I've said my peace
and won't bother devoting any more of my time to exposing your
ignorance.

 

[aaron.kempf]

Microb0x

I am militantly against the use of MDB files.

For the record; Access Data Projects are the rightful heirs to the
Access tradition.

MDB is obsolete.

ADP won this war; almost 6 years ago and anyone that is using MDB in
the year 2006 should buckle up and start using ADP.

ADP are the best interface, anywhere-- ever; it's just that MDB isn't a
real database ENGINE.

A Sql Server may not be available? MSDE is free.
SQL Server Express is free.

 

[Ron2006]

But IT departments willing to let "users" "dable" in sql on IT's
resources are not.

 

[aaron.kempf]

every end user anywhere should have a local copy of MSDE or SQL Server
Express on their desktop.

Problem solved; and it's free.

 

[aaron.kempf]

and i"m not reccomending that people DABBLE in SQL.

I'm reccomending that every friggin person anywhere in the world; they
ever uses excel-- should uninstall excel and use a database for a year.

i'm reccomending that you and your companies start CONQUERING that
mountains of data that you already have.

Amazon.com didn't get there by having 40 people access the same
spreadsheet at the same time.

it's all about the databases, baby

-Aaron

 

[Ron2006]

The "Dabble" part is IT's response, not mine.

 

[aaron.kempf]

well then get a new IT department.

Databases are the most important piece of technology in the world.
it makes no logical or practical sense to prohibit end users to develop
ANYTHING THEY PLEASE in sql server.

the training resources that are available for SQL Server and other
enterprise RDBMS are 100x more available than an obsolete desktop
database like MDB.

-Aaron

 

[dbahooker]

so MicrodOrk

what you got to say about it now?

I am the rightful heir to the access name; you mdb idiots are a bunch
of obsolete imposters

why dont you mdb dorks leave my Access form; because i claim that this
forum is now a MDB-free zone.

Who in the hell do you think that you are; saying i can't complain
about MDB?

it's like.. in a Exchange 2003 forum; people complaining about shit
regarding exchange 5.5-- of course im going to biutch up a storm.

stop using obsolete mdb format and maybe i'll stop bithching about
obsolete mdb files.

 

[microb0x]

Your moronic response doesn't warrant a rebutle. Any competent reader
will surely just scim past your non-sense.

 

[dbahooker]

rebutle?

are you a native english speaker??

my non sense?

Access Data Project solutions don't require:

a) indexing - sql does it automagically and its a LOT friggin better
than any of you mdb script kiddies
b) no front-end back-end BS
c) you don't have to sync queries or tables every time your user opens
their front end
d) you have a LOT more power with SPROCS than you do with crappy little
MDB queries.
e) your queries (i am referring to views and sprocs collectively) don't
crap out when you stack them on top of each other (try doing THAT with
mdb)
f) you have a 4gb limit per db instead of a 2gb limit
g) you can STORE DATA IN MEMORY in order to make it faster. 1gb memory
= what $50?
h) you don't randomly lose data
or
I) you dont randomly have corruption
J) no compacting and restoring
K) you can still backup your database if someone leaves their app open
at night
(try doing THAT with mdb)
L) i've got source control; versioning-- on my DDL. VSS against MDB is
a goddamn joke
M) i've got a thousand 3rd party vendors
if MDB didn't SUCK BALLS then litespeed would have made a litespeed
for MDB

need i continue?

eternal damnation on your mdb dorks; you should be ashamed of
yourselves

grow some balls and keep all your data in sql server.

it's free; it's faster; it's better; it's easier

it's better documented; it's more powerful; it's STABLE

how many times have you had to rummage through a factory floor...
asking people to 'please get out of their mdb files so that you can do
some versioning? file maintenanct?

why dont you STFU then kid

goddamn script kids should be shot



but hell; at least you're not an excel user..

 

[microb0x]

I just noticed that you tend to rant about ADP in several different
posts in Access forums. Always the same ignorant comments by you, the
same insulting tone toward anyone who doesn't fully agree with you,
maybe you should get a life?

And if rebutle was too complex a word for you to understand, I truely
pity you...

I hope this entire series of posts has you all worked up and bothered.
Because you obviously enjoy this.

 

[aaron.kempf]

http://dictionary.reference.com/search?q=rebutle

No entry found for rebutle.
Did you mean rebuttal?



grow some balls; lose the training wheels.

mdb is for babies.

compact and repair; splitting; and sync'ing queries?

there is a better way; it is called ADP