[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2004-10-14
7
Medium Priority
?
6,394 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
[X]
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
  • 4
  • 3
7 Comments
 
LVL 8

Accepted Solution

by:
SNilsson earned 2000 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

656 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