Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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


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!
2 Solutions
FujiMedAuthor Commented:
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.
Alpesh PatelAssistant ConsultantCommented:
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.
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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.
FujiMedAuthor Commented:
Hi Guys,

Thank you for the responses, let me try them out and get back to you.
FujiMedAuthor Commented:
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.
FujiMedAuthor Commented:
@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 :(

FujiMedAuthor Commented:
I mean, relinking the table.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now