Taking a stab in the dark connecting an application to system DSN to SQL Server 2005

Posted on 2011-05-09
Last Modified: 2012-06-27

I have an application developed by a former colleague who has left the company without leaving any documentation or source code.

The application is simple enough, it is to allow user to query a database in order for them to do audits / statistical analysis.

The application connects to the System DSN which is connected to the SQL Server.
We have not been having any problems until we had to do a migration.
Previously the application connects to DSN to SQL Server 2000.
Now, the SQL Server has been moved to another server and upgraded to SQL Server 2005.
DSN has been reconfigured, and there are no errors when setting up DSN. Connectivity test is good too.
When I tried running the application, it gives me "ODBC --Call Failed" error message.

I copied the application and a copy of the database locally, uploaded the database to SQL Server 2005 and configured the DSN and the application works fine.

The application is in *.mde format.

So far, the pattern that I noticed is that the server where I'm moving my database to does not have Microsoft Access installed, but I don't think this would be a problem, would it?

any pointers to the right direction will be appreciated!
Question by:FujiMed

    Author Comment

    The error comes up when I tried to log-in, btw, judging that it works locally and how quickly the error message comes up, it seems to fail to connect to the database, doesn't seem to be logic issue with the select statement.
    LVL 21

    Expert Comment

    by:Alpesh Patel
    It seems that Application and database on different server right? Please try to connect database using SSMS. If not able to do so. Please enable remote connection, check firewall etc.
    LVL 5

    Assisted Solution

    To test on the application server, just install msaccess, create a new mdb and create a link table to ms sql to see if can successfully connect.

    if can, relink the table in the problem mdb
    LVL 9

    Accepted Solution

    No, you don't need Access installed on the server side, only on the client PCs.

    Since your connection test succeeds, it seems your DSN is okay and your SQL Server is reachable.  Create an empty database with one linked table to see if it connects.

    You may need to delete and relink all the linked tables in your Access application.

    Without seeing how your previous developer handled the connection strings and changing servers, it's hard to diagnose further.

    Author Comment

    Hi Guys,

    Thank you for the responses, let me try them out and get back to you.

    Author Comment

    SSMS connects just fine.

    @chewhoung & ArmenStein:
    I have a much older version of the mdb and I found that the linked tables have the database name specified to it, I thought it would use the default database specified in DSN, I must be wrong then.
    I think this must be why it's not working and I have to delete and relink the tables.
    I'll give this a go with the older mdb and see if it is working, if it is, I can accept the solution.

    Author Comment

    @chewhoung & ArmenStein:
    Thanks guys! after relinking the db it's working now.
    Now I just have to work from an older source code and see how far I can get :(


    Author Comment

    I mean, relinking the table.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    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.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Viewers will learn how the fundamental information of how to create a table.

    759 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

    14 Experts available now in Live!

    Get 1:1 Help Now