Solved

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

Posted on 2004-10-14
7
6,365 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now