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?
