Is the Access database can handle 60+ user?

I am thinking of creating Access 2003 database, which should be able to handle

Up to 64 User concurrent  70% read access and 30% write access
Up to 4Mil records divide in 2 databases (assuming 0.5K record)

Only 10 user will be on LAN connection to the database server and rest of the users will be on WAN.

Please advice
-      Choice of Access is right
-      Access 2003/2007 will be able handle 64 users
-      As Access database size is limit to 2GB, split the database in 2 file will do the job
-      Access the MS Assess from WAN, is it adviable

Please suggest any other database for front and back end in not Access

Thanks
MTCCOMPAsked:
Who is Participating?
 
jmoss111Connect With a Mentor Commented:
My vote would be to steer away from TS and use Access front end using pass through to SQL Server backend which would probably be the quickest, easiest, most cost effective, and most maintainable solution. Here's why:

I know nothing about the state of your user communities workstations, but Access front end would probably run better than .net on non cutting edge hardware. If your WAN is good it would be hard to beat for speed or at least that's my experience. I think but i'm not an expert in that area that hardware and licensing  to support 64 users on TS would be more expensive and harder to maintain than SQL Server (Express). If Express wasn't strong enough and you had to go full SQL Server then licensing would be offset but hardware costs would still most likely be higher for TS.

Jim
0
 
SreejithGCommented:
I think Microsoft SQL Server will be a better choice
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
"split the database in 2 file will do the job"
What exactly do you mean by that?  Two back ends?

It *can* be done. However, it's going to depend largely on:

1) The stability of the WAN
2) The design of your Front End and Back End.

I currently have 15 Access database running on a VERY well maintained high speed WAN, with typically 5 to 20 users (sometimes more) per mdb, all doing Read/Write.  Each workstation has a copy of the Front End master - linked to the corresponding backend.   I have virtually zero problems.  Your results *may* very ... and others results have varied for sure.

2GB is the MDB max size, including ALL data and objects.
255 is the max concurrent users; however, I'm SURE that is pushing the edge of reality.

Other Options:

Access Front End connect to SQL Server backend, or some other ODBC database.
VB.Net web app connected to SQL Server (or other) backend

mx
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kelvin SparksConnect With a Mentor Commented:
I'm a great fan on Access, BUT I honestly think, that while this would work, that Access will not give you the stability and performance you seek.

The way that Access works does make WAN performance questionable. A client server style of development would be preferable here.

Kelvin
0
 
MTCCOMPAuthor Commented:
DatabaseMX:
"  "split the database in 2 file will do the job"
   What exactly do you mean by that?  Two back ends?  "

I was thinking creating everything in fornt end and tables (data) in back end.
Now when you said TWO BACK ENDS, is it possible?

In my case i will have 64 concurrent user, but all have there fornt end on there workstation and when they read or write, it have to communitcate with back end, which will be on server 2003 over the WAN connnection. I am not sure right now how i will do that, but that a plan. HOW ARE YOU CONNECTING OVER WAN.
Other method i was thinking,was to use Terminal Server and both fornt and back end stay on the server and user connect through TS.

Thanks

 



kelvinsparks:
"client server style development "  can you suggent, what to use?

Thanks
 

Thanks




0
 
jmoss111Commented:
Hello MTCCOMP,

I think that you would have a much better chance of having a robust, well performing system using SQL

Regards,

0
 
jmoss111Commented:
MTCCOMP,

using SQL Server.

Jim
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
"I was thinking creating everything in fornt end and tables (data) in back end."

Yes ... do that.

"Now when you said TWO BACK ENDS, is it possible?"
Possible yes, but not a good idea ... because you cannot establish Relationships across MDB's ... and a SOLID Relational design of the back end is going to have a postive impact on performance and stability.

"HOW ARE YOU CONNECTING OVER WAN. "
Linked tables FE to BE.

"Other method i was thinking,was to use Terminal Server "
TS is a great option.  I have two clients running TS for remote access.  However, at most there are 10 users simultaneously, but I have heard of many more concurrently.

mx

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"In my case i will have 64 concurrent user, but all have there fornt end on there workstation"

And this will help you with that:

Auto Updater:  http://www.granite.ab.ca/access/autofe.htm

I have a highly modified version of this concept  - sort of Tony's on Steroids.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Again, this can all be done ... and ... made to work.  However, everything has to be in 'order' so to speak.  I have to say ... since you are starting from the git go ... I would look at SQL Server as the BE ... IF ... you have that option. I did not have that option.

mx
0
 
jmoss111Connect With a Mentor Commented:
I have 100+ users 70/30 read on SQL Server 2005 Express backend, about 40 local US users, with the rest in EU and Central America without one glitch in over 2.5 years. The server has only been booted for service packs and once when local power was out and battery expired.

Largest database table has almost 10 million rows; that database size 3.1 gb including tables and a couple of indexes. It's very fast and has very good infrastructure to support it.

0
 
jmoss111Commented:
mx,

Would you have posted your Tony's on Steroids anywhere?

Jim

0
 
Kelvin SparksCommented:
kelvinsparks:
"client server style development "  can you suggent, what to use?

I'd Certainly go with the flow here with some form of SQL Server BE. SQL Server 2005 Express is free which can help if costs are an issue, and also runs on a PC. As to the client end Access is very good as a "frontfor SQL Server (either Express or full blown server version). Go beyond that and it's some form of web development.


Kelvin
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Would you have posted your Tony's on Steroids anywhere?"

Not really possible ... way to much involved - part of my Library ... and current integrated into the environment at work.  Maybe someday ...

mx
0
 
jmoss111Commented:
My Access front end is quicker than a .net web front end runs that I built but maybe that says something about my .net skills. But a browser based frontend wins in the deployment area.
0
 
MTCCOMPAuthor Commented:
jmoss111:
If you the SQL Server, will the SQL server express 2008 will do the my JOB.
64 concurrent connection? and the Size?
And Is it possible to do the fornt end with it also?
What would be best to use if want to do browser based forntend?

thanks
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"What would be best to use if want to do browser based forntend?"

VB.Net / ASPX.Net .....

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
If you have the option of doing a browser based FE ... and ... you feel you can create the UI and reporting you need with a browser based scenario, then that is *clearly* the way to go.  However, with the current state of the art, I doubt still ... that a browser based FE could be created to emulate some of my Access FE functionality ... but, it's getting much closer.  Now reporting ... that's another story totally. It's really hard to beat the Access reporting engine.

mx
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
While mx has had good luck with Access over a WAN, the experience I've had (and the experience of a HUGE majority of Access devs) has been - just don't do it. Unless you can be assured of a very fast and very reliable connection (and that typically equals a large outlay of cash) then you'll have a very difficult time of doing this.

60+ concurrent users will almost certainly be a non-starter as well, but as others have said this depends on the complexity of the app, the basics mechanics you use when dealing with data (i.e. standard bound forms would certainly render this unusable), and the skill of the programmer.

That said, my vote would be for:

An Access FE with a Server-type BE (i.e. SQL Server, or SQL Server Express), or a web-based with same db engine.

If you MUST use Access as the database, then the only real alternative is a Terminal Server (or equivalent) setup.
0
 
MTCCOMPAuthor Commented:
After reading pro and con for access
I think i should go for the Access as FE and SQL for BE

But
for BE  SQL Express will be good enough for my senario?
for FE using Access locally on Workstation or going throug TS will be better.


0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
"for FE using Access locally on Workstation or going throug TS will be better."

Well, if your users are 'local' - then the Workstation approach. If remote, the TS ... OR ... a combination of the two ... which is what my clients have - so they can access the app from home, etc - via a browser.

mx
0
 
Kelvin SparksCommented:
SQL Express is a desktop version of SQL Server and has mostly the same functionality. You need to keep the 4Gb database size limit in mind here, other than that perfectly fine for your use.


Kelvin
0
 
jmoss111Connect With a Mentor Commented:
"jmoss111:
If you the SQL Server, will the SQL server express 2008 will do the my JOB.
64 concurrent connection? and the Size?
And Is it possible to do the fornt end with it also?
What would be best to use if want to do browser based forntend?"

2005 Express does the job for me; 2008 should be no different. The full version is better becaause of a more robust tool set, but Express is doable.
 
I seriously doubt that there would ever be 64 concurrent connections out of 64 users.

I'm storing 10 million rows in one table that is about 1.6 gb without indexes, 3.1 gb with indexes. SQL Server is much more efficient at storage than Access is and not the worry about bloating like Access does.

Maintenance is done nightly including dropping and recreating the indexes, and running contig.exe on the databases; that is where the speed comes from.

You can't build a front end on sql server, there is no gui interface only SSMS which requires typing T-SQL strings or executing, views, stored procedures, etc.

For a browser based frontend asp.net, c#.net, or vb.net would be the best choice but there is a fairly steep learning curve to those products.

Also, there will be a learning curve with Access over WAN because linked tables won't cut it. You'll have to deal Jet out and use pass throughs to get any real performance, which also has a learning curve but not as steep as .net and probably perform just as well if not better, especially on not cutting edge client machines. I wouldn't attempt using Jet over a WAN and wouldn't recommend it unless you're as sharp as mx.
0
 
jmoss111Commented:
I've had 65 users on Jet over a LAN and it's not easy to get it to perform well.
0
 
jmoss111Commented:
You could use a workstation for hosting SQL Express but you'd be limited to 10 connections on XP; and the workstation couldn't be used as a workstation. Much better to go to some version of Windows Server with a real server box with dual processors, SCSI or WD Raptor hard drives, and 2 gb RAM. SQL Express will only use 1 gb RAM and 1 processor which leaves one processor and one gb RAM for the OS. Also, don't think about using that box for a file server.
0
 
jmoss111Commented:
This illustration is a good example of SQL vs Jet
sqlservervsfileserver.gif
0
 
MTCCOMPAuthor Commented:
jmoss111:This illustration is a good example of SQL vs Jet

Thanks. This definitely shows it is better to use SQL as the performance in concern.

kelvinsparks:
 "SQL Express is a desktop version of SQL Server and has mostly the same functionality. You need to keep the 4Gb database size limit in mind here, other than that perfectly fine for your use."

But can you easily upgrade to standard SQL as the structure should be same, if data size or 2 CPU need to be used?

DatabaseMX:
"Well, if your users are 'local' - then the Workstation approach. If remote, the TS ... OR ... a combination of the two ... which is what my clients have - so they can access the app from home, etc - via a browser."

Your are right there will be few local user and mosly remote user. What i do know what other options are to connect the SQL database (on win2003 server) to remote work station other then TS.

jmoss111:
"For a browser based frontend asp.net, c#.net, or vb.net would be the best choice but there is a fairly steep learning curve to those products.

Also, there will be a learning curve with Access over WAN because linked tables won't cut it. You'll have to deal Jet out and use pass throughs to get any real performance, which also has a learning curve but not as steep as .net and probably perform just as well if not better, especially on not cutting edge client machines. I wouldn't attempt using Jet over a WAN and wouldn't recommend it unless you're as sharp as mx."

My concern is the speed, so what do you suggest
browser based or Access over WAN or Access through  TS for fornt end.
As all of them will have learning curve for me.

thanks
0
 
Kelvin SparksConnect With a Mentor Commented:
The SQL Express databases can just be ported onto a full version - detach the mdf file and attach it to a full blown copy.

I routinely do this when testing development under a number of setups, or if I have to take an SQL Server db and demo it with a notebook that only has express on it.

I prefer to use a dsn-less connection if you need to link tables into Access as there's no setup needed. I suspect in this case, you'll be using ADO and server based development rather than linked tables.
0
 
jmoss111Commented:
Linked tables isn't in the realm of possibility for this gig do you think kelvinsparks?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'll get back to y'all in a bit ... right now ... an event of monumental on historical proportions ... as I predicted (read: LANDSLIDE)... has just occurred!  

mx
0
 
Kelvin SparksCommented:
Not if you have a generic solution. Possibly if you use a different build for the LAN and another for the WAN - although I doubt there is any good reason to do that.

Just threw that bit in as a wider piece of knowledge that could assist MTCCOMP at some stage in the future.

Kelvin
0
 
jmoss111Commented:
mx, a win but not quite a landslide; 51.5% to 46.5% is a little bit short but enough to kill my business.

Reagan's 1984 win over Mondale, 59 percent to 41 percent in popular votes was a landslide and a mandate.
0
 
jmoss111Commented:
My thinking was wrong, a landslide is over 300 electoral votes.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"a landslide is over 300 electoral votes."

And still 5 states to go ... as of 22:55 PST !!

mx
0
 
jmoss111Commented:
It's a pretty blah evening around here, I work in a defense and aerospace town...
0
 
Kelvin SparksCommented:
And I live and work in New Zealand. The results will probably affect us significantly. We also have a full election for our parliament this week-end.

Most of my work is government based!!

Kelvin
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I personally think it's sad that America would vote in a semi-Socialist as our next president.
0
 
jmoss111Commented:
I've had all the change that I can stand as a result of the 06 elections.
0
 
MTCCOMPAuthor Commented:
jmoss111:
My vote would be to steer away from TS and use Access front end using pass through to SQL Server backend which would probably be the quickest, easiest, most cost effective, and most maintainable solution.

I know nothing about the state of your user communities workstations, but Access front end would probably run better than .net on non cutting edge hardware. If your WAN is good it would be hard to beat for speed or at least that's my experience. I think but i'm not an expert in that area that hardware and licensing  to support 64 users on TS would be more expensive and harder to maintain than SQL Server (Express). If Express wasn't strong enough and you had to go full SQL Server then licensing would be offset but hardware costs would still most likely be higher for TS.

Hardware on workstation will be NEW as of currently stardare PC (Core2, 2GB ect.)
Hardware on BE will be New HP server
WAN connection would as High speed 8 m/s

Using SQL Express for now for BE and
For FE  I have two things that I have to do access the database and transfer PDF (scaned doc)files
So using Acces for FE for database and to upload file OR or using web base with upload file optionI would be better or any other Method.
PDF file upload is not often but it is required.

kelvinsparks:
The SQL Express databases can just be ported onto a full version - detach the mdf file and attach it to a full blown copy.
I routinely do this when testing development under a number of setups, or if I have to take an SQL Server db and demo it with a notebook that only has express on it.

So that solve one problem that I can eaily upgrade to FULL SQL from Express without change much. when data grow and need 2 CPU.  Just for Info Express support 1 CPU can it be Quad Core?

What is dsn-less connection  or ADO ? Which is diff and better? As I have no idea about it.

Thanks
0
 
jmoss111Connect With a Mentor Commented:
Express will only utilize one processor, I don't think that the # cores per processor matters, and only 1 GB RAM. Extra processors would be beneficial for the OS. I would plan on not running anything else on the server or using it as a file server or web server. You didn't mention the OS on workstations and if it happens to be Vista, you might want to consider 4 GB RAM, if XP you'd be fine.

"What is dsn-less connection  or ADO ?" = database connection method. You'll want to steer clear of linked tables.
0
 
jmoss111Commented:
I missed the pdf comment first reading; I do mostly Excel file file builds and pushes because I work with accountants and the love their Excel files, but .pdfs are doable.
0
 
MTCCOMPAuthor Commented:
jmoss111:
I was thinking of HP server with Quad Core Xeon Processor with option of adding 2nd CPU if need.
Now you said not to put file on this server, but as said PDF file that will be scanned upload to server are going to on same server, you think that will be problem OR i should add the 2nd CPU now.
Workstaion will be XP, but putting 4GB is not much differece in price, but I think XP never take advantage of that anyway.

"What is dsn-less connection or ADO ?" = database connection method. You'll want to steer clear of linked tables.

Thats for ACCESS, what about using web base access, do i need separate server.
Sorry I have to will ask same question again which is better and faster
FE using Access vs. Web Base
 
 
0
 
jmoss111Connect With a Mentor Commented:
I would opt for dual processors so that SQL Express has it's own.

You said that .pdf traffic would be light, which should be OK.

XP Pro 32 will take advantage of 3+ Gb of the four; XP Pro 64 would use 8 or more if you had it.

I wouldn't put a database server and a web server on the same box but I think that the web server box wouldn't have to be as robust as the sql server box, IE you really want fast drives on the sql box.
0
 
MTCCOMPAuthor Commented:
pdf traffic will not be often but when whenever it will be, it will be lot (25-30 scanned files)
I only need the webserver if i Go when web base FE, right
if I go with Access FE then i do not need it, right.
Back to same qestion which is better?
 
0
 
jmoss111Commented:
You are right on top of it, affirmative.

If you ask 10 different people which is better you'll get multiple answers and an argument started.

For me the Access solution works just fine. It has less of a learning curve, requires less hardware, and my response time with updates or searches is very fast. State of  the art, it's not but it works. My client has very good networking and my databases are indexed well. A user in Guadalajara MX or Glasgow Scotland can search a 10 million row table and data is returned almost instantly like they were here in Huntsville, AL.
0
 
jmoss111Commented:
One area that hasn't been covered is security and that is beyond my expertise.
0
 
garyb2008Commented:
To be honest, this seems a big project for someone without a lot of experience. I think that Access front ends with an SQL server back end is the best of the discussed options under your circumstances; thats what i use, but if i were you i would research other alternatives too.

I have no experience with the product, but Filemaker Server i believe allows you to make the front and backend in the one environment. It delivers the front end to the users via webpages also, so you would need a windows server runinng IIS to deploy it. I am pretty sure it doesnt have the flexibility/scaleability of the Access/SQL Server solution, but just how much of your time are you going to spend learning Access, VBA, SQL Server / T-SQL.

Do any of the Experts here have experience with Filemaker Server?
0
 
jmoss111Commented:
I had some experience with Filemaker about 6 years ago and didn't care much for it.

So you're saying that Filemaker has no learning curve?
0
 
garyb2008Commented:
To claify, yes, id recommend investigating the the learning curve of the various solutions.




0
 
jmoss111Commented:
From the FileMaker web site:

"FileMaker Instant Web Publishing allows five simultaneous web sessions. "
0
 
garyb2008Commented:
yea thats right for the standard version, thats why the server version would be required, best get some experts in the field to way up the options hey
0
 
MTCCOMPAuthor Commented:

Yes the project started as small, then customer start adding stuff and requirements start to increase for the future. For sure they are not going to have 64 users now, but I have to think in advance and accommodated their initial system for the future needs.

I like to thanks all of you for helping me out and giving options. For sure you will see me soon for further help as the project will do in implementation phase.

Thanks
0
 
Happ1marCommented:
Curious how the project went. I stumbled across this quite by accident but have been asked these same questions on multiple occasions.  in 2008, I would have agreed with everyone saying there are many ways to optimize the front end with SQL in the back. Even as I type this, I grapple about the balance of getting solutions to users fast with access, vs. the waterfall methodology on our main frame systems.  The fact is, we got so good supporting access that at least one of our mainframes is now solely used as a data repository.  The problem for funding a replacement is that we cant come up with enough benefits to implement some of the great Access programs in our mainframes where they belong.  Today, I'm leaning towards forcing rapid development on the main frames instead of Access.  Ask me tomorrow and I'll be siding again with Access.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.