Access VBA: Problem connecting to online SQL 2008 database

Murray Brown
Murray Brown used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Som TripathiDatabase Administrator

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

Commented:
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
Infrastructure and Database Design Consultant
Commented:
You should complete your connection string like this:

"Provider=SQLOLEDB;Data Source=196.220.43.247,1444;Network Library=DBMSSOCN;Initial Catalog=sqlserver1;User ID=murbro;Password=password;"
Top Expert 2012

Commented:
Any reason you are using port 1444?
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Worked perfectly. Thanks
Francis OmorutoInfrastructure and Database Design Consultant

Commented:
Always glad to help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial