Connect VB6 to SQL 2008

Posted on 2009-12-23
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
    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"

It fails at the 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!

Question by:ChadB234
    LVL 14

    Expert Comment

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

    Expert Comment

    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.
    LVL 10

    Accepted Solution

    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.

    Author Closing Comment

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

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    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…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now