?
Solved

Connect VB6 to SQL 2008

Posted on 2009-12-23
4
Medium Priority
?
2,080 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:ChadB234
4 Comments
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 26114663
Add this:
User ID = YourID;
Password = YourPassword;
0
 
LVL 25

Expert Comment

by:jogos
ID: 26115687
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.
0
 
LVL 10

Accepted Solution

by:
3_S earned 2000 total points
ID: 26117041
In Sql Server configuration manager
Did you enabled TCP/IP and named pipes on the protocols for the sql server? (under sql server network configuration)
Normally this is default disabled and you can only connect to the server locally.
0
 

Author Closing Comment

by:ChadB234
ID: 31669531
Thank you very much, short and sweet, something I just overlooked! Thanks Again!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question