ODBC Connection to SQL Server failing from MS Access

I have a Visual Basic 6 exe file and separate MS Access 2000 mde file which both connect to a sql server 2005 database on a remote server via VPN

The ODBC connection string is as follows:

Provider=SQLOLEDB.1;DRIVER=SQL Server;Data Source=192.xxx.xxx.xx,1433;Network=DBMSSOCN;SERVER=Servername;Initial catalog = Databasename;UID=uid;PWD=pwd

Until recently both the vb programme and the Access database were connecting.

Due to the age and reliability of the server a new machine was purchased and the service provider cloned our existing physical Server into a VMWare virtual machine. As far as the existing Server setup is concerned it thinks it is identical, except for the network and video cards.

The only problem is that whilst the vb database programme continues to work the access database no longer connects using the ODBC string.

I created a test MS Access front end and a simple pass through query to the SQL Server database. However when I run the query I get the message “ODBC—connection to ‘SQL Serverservername’ failed”

Interestingly, if I remove the connection string and use a DSN, when prompted the query runs.

Is there a setting in SQL Server that can refuse an ODBC connection from MS Access but not another client source or is there something more ominous preventing the connection?

more ▼

asked Oct 15, 2009 at 11:47 AM in Default

avatar image

13 2 2 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Sounds to me like your drivers are the issue. The ODBC driver you are using is causing problems, which is why if you leave the connection string out, it is calling a method thatr works (which will be different to the one you're trying to use). Re-install the MDAC drivers and you will probably find it starts working again.

more ▼

answered Oct 20, 2009 at 06:02 AM

avatar image

1.7k 2 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 15, 2009 at 11:47 AM

Seen: 3603 times

Last Updated: Nov 08, 2009 at 11:23 AM

Copyright 2018 Redgate Software. Privacy Policy