Creating a ADO connection to SQL Server

Creating a ADO Connection To
SQL Server

Here is a example to create a ado connection. You could create a  basic
module and add it to your project and then create a Global ADO connection, so
your program will use one connection instance for the whole program. That way
once you open up your connection it will stay until you close the connection or
exit the program. Make sure in your VB project , you have in your references
menu option,Microsoft Activex Dataobjects selected. And also Dcom installed.

In the General/declarations of your basic module declare your connection ..

Global SQLCON As New ADODB.Connection

Then , in your project , say under a command button the
code to open your connection, would be ...

Public Sub Command1_Click()

    ' Connect to SQL server through SQL Server OLE DB Provider.

    ' Set the ADO connection properties.

    SQLCON.ConnectionTimeout = 25  ' Time out for the
connection

    SQLCON.Provider = "sqloledb"   ' OLEDB Provider

    SQLCON.Properties("Network Address").Value =
"111.111.111.111"  ' set the ip address of your sql server

    SQLCON.CommandTimeout = 180 ' set timeout for 3 minutes



    ' Now set your network library to use one of these libraries
.. un-rem only the one you want to use !

    'SQLCON.Properties("Network Library").Value = "dbmssocn" ' set the network library to use win32 winsock
tcp/ip

    'SQLCON.Properties("Network Library").Value = "dbnmpntw" ' set the network library to use win32 named
pipes

    'SQLCON.Properties("Network Library").Value = "dbmsspxn" ' set the network library to use win32
spx/ipx

    'SQLCON.Properties("Network Library").Value = "dbmsrpcn" ' set the network library to use win32
multi-protocol

    'Now set the SQL server
name , and the default data base .. change these for your server !

    SQLCON.Properties("Data Source").Value = "MYSERVERNAME"

    SQLCON.Properties("Initial Catalog").Value = "MYSQLDATABASE"

    SQLCON.CursorLocation = adUseServer ' For ADO cursor location



    'Now you need to decide what authorization type you want to
use .. WinNT or SQL Server.

    'un-rem this line for NT authorization.

        
'SQLCON.Properties("Integrated Security").Value = "SSPI"

     ' Or if you want
to use SQL authorization , un-rem these 2 lines and supply SQL server login name
and password

    'SQLCON.Properties("User ID").Value ="SQLUSERNAME"

     'SQLCON.Properties("Password").Value =
"SQLPASSWORD"




     ' Now we can open  the ADO Connection to SQl
server  !..

     SQLCON.Open

    ' Now we can do a simple
test of the new ADO connection

     ' Lets return the Time and Date the SQL server thinks
it is ..

    Dim RS As ADODB.Recordset

    Set RS = New ADODB.Recordset

    SQLstatement = "SELECT GETDATE() AS SQLDATE " ' Set a
Simple Sql query to return the servers time

    RS.Open SQLstatement, SQLCON  ' Lets open a connection
with our new SQLCON connection , and our SQL statement

    ' Move to first row.

    RS.MoveFirst

    junk = MsgBox( "Server Time is " & RS("SQLDATE"),
vbOKOnly, " SQL SERVER INFO")

   

End Sub

Of course , you need to add error handling routines
, and more user friendly code, if you want selectable logon options, but this
should at least get you talking to the SQL server.