Multi Use Database Questions

[Jeff]

Hi All

Any help with these questions would be much appreciated - thanks in advance
for any assistance you can provide and if you need any more details please
let me know.

Jeff


I have made multi-user Access database with the back end (data) part of the
database sitting on a network drive of a LAN - with about 10 or more front
end Access database files sitting on desktops. I have linked the tables
from the back end file into each of the front end files. The system will be
used to track campaign related sales activities for a group of about 10
users - with an average of between 100 and 500 records per campaign and
about a hundred campaigns a year. I will need to have concurrent and
efficient access to the system by all 10 users. I am trying to launch this
tomorrow and I started to wonder if Access even supports this many users and
what the data limitations with Access are. My questions are these:

1. Does Microsoft Access work well with a multi-user setup as I have
described and is there a limit to the number of concurrent users?

2. With the level of data I have described will Access support this much
activity and how much data will Access support?

3. Will I have a problem with speed or efficiency setting things up like
this with an Access Database or should I store the data in an SQL database
instead?

3. If I use a SQL database can I simply port my table structure over and
link the tables to the front end Access files that I have created and will
that work any better?

4. Am I barking up the wrong tree entirely trying to use Access for
something like this when I should be doing something in another format or
language?

Thanks again for any input,

Jeff

 

[Allen Browne]

Relax, Jeff. 10 users is a breeze for Access, and the few thousdand records
you are talking about will barely even bother waking the CPU.

Assuming your relational design is correct, you index astutely, and you
build a suitable interface, Access is capable of getting almost instantaeous
results from a table will millions of records.

 

[tina]

comments inline.

Hi All

Any help with these questions would be much appreciated - thanks in advance
for any assistance you can provide and if you need any more details please
let me know.

Jeff


I have made multi-user Access database with the back end (data) part of the
database sitting on a network drive of a LAN - with about 10 or more front
end Access database files sitting on desktops. I have linked the tables
from the back end file into each of the front end files. The system will be
used to track campaign related sales activities for a group of about 10
users - with an average of between 100 and 500 records per campaign and
about a hundred campaigns a year. I will need to have concurrent and
efficient access to the system by all 10 users. I am trying to launch this
tomorrow and I started to wonder if Access even supports this many users and
what the data limitations with Access are.

yikes! if you're launching a finished application tomorrow, isn't it a bit
late to wonder if it's workable?

My questions are these:

1. Does Microsoft Access work well with a multi-user setup as I have
described and is there a limit to the number of concurrent users?

AFAIK, there's no hard-and-fast limit. the more concurrent users, the higher
the possibility of record-locking conflicts, i suppose. but then the volume
of records being written to the table is not high, so...

2. With the level of data I have described will Access support this much
activity and how much data will Access support?

with a maximum of 500 records in each of 100 campaigns a year, that's 50,000
records a year. unless the records are quite large (many fields, perhaps
huge memo fields or OLE fields), that's not very big, as tables go. there's
no hard limit on the number of records per table, the issue is overall size
of the database file. look up "specifications" in Access Help for the file
size limit for the version of Access you're using.

even if you're only running 8 hours a day, five days a week (as opposed to
24x7, for instance), fifty thousand records only works out to 24 records per
hour, or 2-3 per minute. that's not so heavy, as data entry volumes go.

3. Will I have a problem with speed or efficiency setting things up like
this with an Access Database or should I store the data in an SQL database
instead?

if you have a normally fast LAN, it should be okay if the database was
optimized for multiple users in a LAN environment. see
http://www.granite.ab.ca/access/performancefaq.htm for tips on improving
performance.

3. If I use a SQL database can I simply port my table structure over and
link the tables to the front end Access files that I have created and will
that work any better?

i have no experience with SQL Server, and no knowledge other than what i've
read in these newsgroups - so i'll leave that to someone else to answer.

4. Am I barking up the wrong tree entirely trying to use Access for
something like this when I should be doing something in another format or
language?

based on the information you posted, and provided you're working in a LAN
environment rather than a WAN environment, i'd say Access will get the job
done just fine.

 

[Guest]

Hi Jeff,

I have a new article out that should be helpful for you. The indicated
downloads have not been finalized yet, so they're presently not available,
but should be shortly.

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html


Good Luck on your project!

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hi All

Any help with these questions would be much appreciated - thanks in advance
for any assistance you can provide and if you need any more details please
let me know.

Jeff


I have made multi-user Access database with the back end (data) part of the
database sitting on a network drive of a LAN - with about 10 or more front
end Access database files sitting on desktops. I have linked the tables
from the back end file into each of the front end files. The system will be
used to track campaign related sales activities for a group of about 10
users - with an average of between 100 and 500 records per campaign and
about a hundred campaigns a year. I will need to have concurrent and
efficient access to the system by all 10 users. I am trying to launch this
tomorrow and I started to wonder if Access even supports this many users and
what the data limitations with Access are. My questions are these:

1. Does Microsoft Access work well with a multi-user setup as I have
described and is there a limit to the number of concurrent users?

2. With the level of data I have described will Access support this much
activity and how much data will Access support?

3. Will I have a problem with speed or efficiency setting things up like
this with an Access Database or should I store the data in an SQL database
instead?

3. If I use a SQL database can I simply port my table structure over and
link the tables to the front end Access files that I have created and will
that work any better?

4. Am I barking up the wrong tree entirely trying to use Access for
something like this when I should be doing something in another format or
language?

Thanks again for any input,

Jeff

 

[ToddP]

Hello Jeff....A cautionary note:



Access is a file locking database that will work fine as a frontend in a
multi-user, network environment if you designed it correctly for that
deployment. There are books written about this so I won't go into detail,
but I would suggest making sure you are at least familiar with performance,
locking levels, security, error handling, and refresh/updating techniques.



I like to split my database and only keep static objects in the frontend to
reduce network traffic, such as a lookup tables, forms, reports, modules,
and complied queries. The use of select SQL queries to limit the size of the
returning recordset and ADO to define the type of connection is helpful too.



If your application was not designed with any of these considerations, I'd
suggest looking into a Replication deployment.

 

[Larry Linson]

If your application was not designed with any
of these considerations, I'd suggest looking
into a Replication deployment.

Michael Kaplan has as much or more expertise on Access replication than
anyone else I know about, and I believe I remember him agreeing that
"Replication is not for the faint of heart." I doubt seriously that
Replication is a good solution for the situation Jeff describes.

It would be simpler to correct any shortcomings that may exist in his
multiuser implementation. Replication is not "an alternative" to
multiuser -- it is a specific solution for a particular subset of multiuser
environments.

Larry Linson
Microsoft Access MVP