How to connect to Sql Server 2008 database from Visual Basic 6?

Question

What is the correct connection string and what are the requirements for connecting to Sql Server 2008 database from Visual Basic 6?

Solution 1

' Initialize variables.
Dim cn As New ADODB.Connection
Dim provStr As String

' Specify the OLE DB provider.
cn.Provider = "sqloledb"

' Specify connection string on Open method.
ProvStr = "Server=MyServer;Database=northwind;Trusted_Connection=yes"
cn.Open provStr

For illustration only (Ref.):

Set rsOrders = New Recordset
rsOrders.Open "Select * from orders", cn
Do While Not rsOrders.EOF
    '
    ' If the order matches some custom business logic then get the details for
    ' that order, without opening a new connection.
    '
    If SomeBusinessLogic(rsOrders("CustomerID")) Then
        Dim rsDetails As Recordset
        Set rsDetails = New Recordset
        '
        ' Open a new recordset using the same connection. Normally it's not
        ' possible to have two recordsets simultaniously using the same
        ' connection, but MARS makes this possible
        '
        rsDetails.Open "Select sum(quantity * unitprice) as total " & _
            "from [order details] " & _
            "where OrderID=" & rsOrders("OrderID"), _
            cn
        grandTotal = grandTotal + rsDetails("total")
    End If
    rsOrders.MoveNext
Loop

lblTotalOrders = grandTotal

Solution 2

Public Cnn As New ADODB.Connection
Cnn.Open Provider=SQLNCLI10;Server=10.1.100.1;Database=DataJualLama;Uid=sa;Pwd=sa;

It required to install sql server 2008 native client runtime from microsoft site.

Solution 3

You would use ADODB. And here are some connection string samples.

Comments

[marc_s]

If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!