Solved

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

Posted on 2004-10-14
7
6,371 Views
Last Modified: 2012-08-13
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?

0
Comment
Question by:showstopper1970
  • 4
  • 3
7 Comments
 
LVL 8

Accepted Solution

by:
SNilsson earned 500 total points
ID: 12309869

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
 

Author Comment

by:showstopper1970
ID: 12309904
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
 
LVL 8

Expert Comment

by:SNilsson
ID: 12310294

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 8

Expert Comment

by:SNilsson
ID: 12310427

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
 

Author Comment

by:showstopper1970
ID: 12311423
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
 
LVL 8

Expert Comment

by:SNilsson
ID: 12311612

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
 

Author Comment

by:showstopper1970
ID: 12312015
I'm not sure if I gave you the points. If not let me know so I can take care of it.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql help 8 55
Importing Backpac? 1 21
SQL Server - Set Value of Multiple Fields in One Query 10 22
SQL Server 2012 - Merge Replication Issue 1 19
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

786 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