MSDE - ODBC --connection to ...... Failed

I have searched this site but nothing I have come across helped me solve this problem.

Background:

With no experience I managed to set up MSDE on one of our servers. Our Microsoft Access guru has been working with it and one of his databases is now operational.

Every user is able to connect without any difficulty except one user who is running Windows 2000 Professional. All other people running WIN2K and XP connect without any problems.

The server SQL is running on is Wndows 2003 Server

Error Message:

ODBC --connection to 'SQL ServerSERVERNAME\Instance' failed

The user gets this error message when he attempts to open the Access Database.

Where can I start looking? Any suggestions?

showstopper1970Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SNilssonCommented:

1 - check the system dsn (do this on the control panels ODBC manager)that access use on the client computer and verify that it's working
2 - make sure you have the correct MDAC version installed on the client
3 - MSDE is more for single user app's or very few, the limit is 5 connections I belive, if you have more then that might be way it fails to connect.

If it's 3 then consider buying areal SQL Server licence
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
showstopper1970Author Commented:
Ok... You are talking to an SQL novice

1. How can I check the System DSN? I know if I go into Control Panel > Administrative Tools > Data Sources ... But how do I check from here? What am I looking for here?

2. What is MDAC on the client

3. I believe MSDE allows 25 connections

Thanks in advance
0
SNilssonCommented:

Ok, let's forget about MDAC for the moment.

Access uses a dsn as standard to connect to other databases (can be done via code and OLEDB also) if you open the Data sources gui and check the 'user dsn' and 'system dsn' tab first.
If you find a dsn under those tab's that have a driver called 'SQL Server' it's most likely that one the Access application is using.
If you dont find it then ask your MS Access guru how he connect to MSDE.

If you did find it highlight it and press edit, this will bring upp the dialog to configure the connection.
You need to know Server name, database, (username and password also if not integrated sequrity is used) to MSDE to continue, if you do know this walk through the wizard and when you reach the last page of the wizard there is a button 'Test the connection' press this and see iff it succeds.

If it works then your connection is fine and MS Access should be able to connect (provided it's that dsn it uses).

If it still wont work I might be the MSDE limitation, I looked it up and we are both wrong :)
MSDE is throttled for 8 concurrent operations.

So count your users and investigate in what way the Access app works (note it's concurrent operations).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SNilssonCommented:

Then again...

MSDE 1.0 is tuned for desktop and shared solutions where there are fewer than five concurrent workloads hitting the database at any one time. If your solution needs to support more than this number of concurrent batches, you may need to migrate to SQL Server or SQL Server Enterprise editions for optimal performance at this higher level of scalability.

Found at :
http://msdn.microsoft.com/vstudio/downloads/addins/msde/examining.aspx

I have found pages that say 25 also, but that's users via a ASP page.

I need to check this up further to get it straight, anyway there is limitations, so it would be nice to know how many users you have.
0
showstopper1970Author Commented:
Ok...

I installed the MDAC Client

http://www.microsoft.com/downloads/details.aspx?FamilyID=9ad000f2-cae7-493d-b0f3-ae36c570ade8&DisplayLang=en

Logged in as domain admin on the local machine and I was successful able to "Test the connection" and also connect to the Database, using Access. Live was good.

However, when I log in as the user and create the user DSN and "Test the connection" it fails. Is there any reason why? This is just out of curiosity, because the user is now able to connect to the database.

Showstopper.


0
SNilssonCommented:

So, it was MDAC after all :)

Not sure about why the useraccount fails to connect, must be some rights missing somewhere... I would have looked it up for you, but it's getting late (Sweden here) so I bettre get some sleep, godnight !
0
showstopper1970Author Commented:
I'm not sure if I gave you the points. If not let me know so I can take care of it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.