asked on

Access VBA: Problem connecting to online SQL 2008 database

Hi

I am using the VBA code below to connect to a Microsoft SQL Server database that I set up wiith my internet hosting company, but am getting the error:
"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
The following information is from my control panel:
Database Type: Microsoft SQL Server 2008
Database Name: sqlserver1
Connection Information: sql7.[yourdomain.com],1444 (196.220.43.247) (Web Manager) (Port 1444)

Sub ConnectToSQLServer()

Dim con As ADODB.Connection
Set con = New ADODB.Connection

con.Open "Data Source=196.220.43.247,1444;Network Library=DBMSSOCN;Initial Catalog=sqlserver1;User ID=murbro;Password=password;"
'sql7.[yourdomain.com],1444 (196.220.43.247) (Web Manager) (Port 1444)

'/ Note that connection string from here: http://www.connectionstrings.com/sql-server-2008#p2

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.Open "SELECT * FROM MyTable", con


End Sub

Open in new window

Som Tripathi

You need to have sql server 2008 client installed. It looks like driver problem.

Francis Omoruto

http://www.connectionstrings.com/Articles/Show/define-sql-server-network-protocol

barlet

The following explanation is taken from http://forums.devarticles.com/microsoft-sql-server-5/data-source-name-not-found-and-no-default-driver-specified-8346.html. hope it's useful in your solution!

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I had this error and want to let you know how it was resolved.

First, this was an ASP web application using a vb 6.0 dll to get data from a sql server 2005 database on a 64 bit windows server 2008 enterprise (vista like) server. I could only get the dll to work in component services as opposed to simply registering it.

It all worked fine upon setup, but after four windows updates one night, the error above was posted in the event viewer, and the web app crashed.

Here is the resolution:

In a 64 bit windows server operating system, there are TWO odbc managers. When you pull up the usual menu for the odbc / dsn system, it is for the 64 bit odbc manager, and 32 bit applications (vb 6.0) will not work using these dsn's.

This is where the 32 bit odbc manager is:

C:\Windows\SysWOW64\odbcad32.exe

ASKER CERTIFIED SOLUTION

Francis Omoruto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Any reason you are using port 1444?

Murray Brown

ASKER

Worked perfectly. Thanks

Francis Omoruto

Always glad to help!