Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2004-10-14
7
6,373 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

789 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