asked on

Connect VB6 to SQL 2008

I have several programs that I've been using for years in VB6 that connect to a SQL 2000 server, I'm looking to upgrade the Db server to SQL 2008 and am having trouble connectiong to the 2008 server. Do I have to make changes to the connection string for SQL 2005/2008?

Here is my connection that im using now to connect to the SQL 2000 server:

Dim DBNAME As String, SERVERNAME As String

DBNAME = "database" 'SUBSTITUTE YOUR DATABASE NAME HERE
SERVERNAME = "server\instance" 'SUBSTITUTE YOUR SQL SERVER NAME HERE

cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & DBNAME & ";Data Source=" & SERVERNAME
cn.Open '<---Fails when opening connection with the attached error message

rs.ActiveConnection = cn
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockBatchOptimistic

rs.Source = "SELECT * FROM table ORDER BY lastname, firstname"
rs.Open

It fails at the cn.open with "SQL server does not exist or access denied"
Im using integrated security and the login i'm using has dbo on the database.

Any help would be greatly appreciated!

Thanks!
ConnFail.JPG

VBClassicGuy

Add this:
User ID = YourID;
Password = YourPassword;

jogos

New server, new database .... if possible first try to connect to SQLCMD or so with the credentials to the database you want. Many times people loose time focusing on connectstring while they forgot to check if the security on the new server is as it should be.

ASKER CERTIFIED SOLUTION

3_S

membership

This solution is only available to members.

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

Start Free Trial

ChadB234

ASKER

Thank you very much, short and sweet, something I just overlooked! Thanks Again!