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

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

Hi,

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!
0
FujiMed
Asked:
FujiMed
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.
0
 
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.
0
 
chewhoungCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.
0
 
FujiMedAuthor Commented:
Hi Guys,

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


0
 
FujiMedAuthor Commented:
I mean, relinking the table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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