Avatar of codeguy
codeguy asked on

Multi-User Configuration with MSDE

I am building an Access application (ADP) that will be used by 70 users in 11 different locations.  Just found out that I do not have access to SQL Server, so I must rely on MSDE.

Can I install MSDE on each client machine and attach to a database (mdf) file on a central file server?  Will I run into any concurrent user limitations since there will be multiple instances of MSDE?

Can anyone suggest alternative configurations?

Thanks
Microsoft Access

Avatar of undefined
Last Comment
donpricejr

8/22/2022 - Mon
donpricejr

There can be up to 16 instances of MSDE on any one given machine.

-Don
ASKER
codeguy

To clarify my question.  Is the concurrent user limit of 5 applicable when multiple clients (with seperate instances of MSDE) are connecting to a data file on a central server?

donpricejr

Some say that the 5 is actually 8, but that is not really your question--that said, however, I must agree with the fact that it is 5 because "5" will be the basis of my answer.

MSDE only allows 5 threads to the engine at any given time; therefore, any additional threads (eg, stored procedures, views, etc.) will sit in queue unti which time only 4 are available. To that end, you may have 20 concurrent users and only 3 requests at the same time going on. I hope that this helps to clear up your question.

-Don
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
codeguy

Thanks Don.  If you and I (and three of our friends) had MSDE installed on each of our workstations, and the data (mdf/ldf files) sat on a file server that we have access to, is this only one instance on each of the five machines, or is this five instances because each MSDE is attached to a central data source?

Note that I am increasing the points here because of the rephrased question...
ASKER CERTIFIED SOLUTION
donpricejr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
codeguy

Thanks Don.  I welcome other responses to this question, because I agree that it could be educational.  I want to award you points for the followup and great answer.  I will do a pilot test with multiple MSDE versions, but its nice to hear that this is not problematic on its face.

I will also consider a nightly ETL job.  I assume we are talking about some type of batch job that might run an Access procedure that fires an update query, etc.  Or, is there another third party tool that you were thinking of?  It would be nice to just develop a DTS package, but again, no SQL Server available.

Thanks again...
donpricejr

Thank you for the points--I appreciate the support.

I will create the ETL tool in my example above because it should not take very long to hand-code, as independent systems have minimal impact. That said, however, I like Informatica for much larger projects--I believe this to be a solid piece of software, but it comes with a handsome price and is usually reserved when you are loading from CRM, Payrolling, HR, etcetera systems. Without knowing the specifics, I would recommend that you write your own pushes or pulls (depending on the architecture) and this way you have total control. DTS is nice, but sometimes building it yourself is more flexible because you know the inner workings of the machine! As for other 3rd party tools, I am sure there are DTS (scaled down) like packages, but I have never looked to be honest. With the ghosting of machines and being constrained by policies, making our own is what I am usually left with.

Best of luck!

-Don
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.