x

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 '09 at 11:47 AM in Default

PeterF gravatar image

PeterF
13 2 2 2

(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 '09 at 06:02 AM

RickD gravatar image

RickD
1.7k 1 1 4

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1933
x48

asked: Oct 15 '09 at 11:47 AM

Seen: 2589 times

Last Updated: Nov 08 '09 at 11:23 AM