[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Is the Access database can handle 60+ user?

Posted on 2008-11-03
53
Medium Priority
?
399 Views
Last Modified: 2013-11-29
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
0
Comment
Question by:MTCCOMP
  • 22
  • 10
  • 8
  • +5
53 Comments
 
LVL 3

Expert Comment

by:SreejithG
ID: 22873536
I think Microsoft SQL Server will be a better choice
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 360 total points
ID: 22873559
"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
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 160 total points
ID: 22873567
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:MTCCOMP
ID: 22873619
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22873636
Hello MTCCOMP,

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

Regards,

0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22873642
MTCCOMP,

using SQL Server.

Jim
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 360 total points
ID: 22873648
"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
 
LVL 75
ID: 22873650
"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
 
LVL 75
ID: 22873656
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
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 480 total points
ID: 22873691
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22873731
mx,

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

Jim

0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 22873752
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
 
LVL 75
ID: 22873782
"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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22873813
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
 

Author Comment

by:MTCCOMP
ID: 22873866
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
 
LVL 75
ID: 22873903
"What would be best to use if want to do browser based forntend?"

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

mx
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 360 total points
ID: 22873927
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
 
LVL 85
ID: 22875492
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
 

Author Comment

by:MTCCOMP
ID: 22879691
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 360 total points
ID: 22879798
"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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 22881278
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
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 480 total points
ID: 22882513
"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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22882521
I've had 65 users on Jet over a LAN and it's not easy to get it to perform well.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22882545
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22882606
This illustration is a good example of SQL vs Jet
sqlservervsfileserver.gif
0
 

Author Comment

by:MTCCOMP
ID: 22883133
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
 
LVL 18

Accepted Solution

by:
jmoss111 earned 480 total points
ID: 22883239
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
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 160 total points
ID: 22883284
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22883315
Linked tables isn't in the realm of possibility for this gig do you think kelvinsparks?
0
 
LVL 75
ID: 22883325
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 22883331
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22883383
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22883466
My thinking was wrong, a landslide is over 300 electoral votes.
0
 
LVL 75
ID: 22883580
"a landslide is over 300 electoral votes."

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

mx
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22883602
It's a pretty blah evening around here, I work in a defense and aerospace town...
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 22884027
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
 
LVL 85
ID: 22884437
I personally think it's sad that America would vote in a semi-Socialist as our next president.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22885420
I've had all the change that I can stand as a result of the 06 elections.
0
 

Author Comment

by:MTCCOMP
ID: 22892541
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
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 480 total points
ID: 22892586
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22892740
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
 

Author Comment

by:MTCCOMP
ID: 22901504
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
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 480 total points
ID: 22901534
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
 

Author Comment

by:MTCCOMP
ID: 22901636
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22901689
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22901695
One area that hasn't been covered is security and that is beyond my expertise.
0
 
LVL 3

Expert Comment

by:garyb2008
ID: 22911018
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 22911160
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
 
LVL 3

Expert Comment

by:garyb2008
ID: 22913658
To claify, yes, id recommend investigating the the learning curve of the various solutions.




0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22913739
From the FileMaker web site:

"FileMaker Instant Web Publishing allows five simultaneous web sessions. "
0
 
LVL 3

Expert Comment

by:garyb2008
ID: 22914507
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
 

Author Comment

by:MTCCOMP
ID: 22927909

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
 

Expert Comment

by:Happ1mar
ID: 37627243
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question