question

PeterF avatar image
PeterF asked

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?

sql-server-2005microsoft-access
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
RickD avatar image
RickD answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.