<p><font color="#000099"><img border="0" src="bd00386_.gif" width="100%" height="4"></font></p> <p align="center"><font color="#0000FF" size="4">Creating a ADO Connection To SQL Server</font></p> <p>Here is a example to create a ado connection. You could create a&nbsp; 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.</p> <p>In the General/declarations of your basic module declare your connection ..</p> <p><font color="#008000" size="2">Global SQLCON As New ADODB.Connection</font></p> <p><font color="#000000">Then , in your project , say under a command button the code to open your connection</font>, would be ...</p> <p><font color="#008000"><font size="2">Public Sub Command1_Click() &nbsp;&nbsp;&nbsp; ' Connect to SQL server through SQL Server OLE DB Provider. </font></font></p> <p><font color="#008000"><font size="2">&nbsp;&nbsp;&nbsp; ' Set the ADO connection properties. &nbsp;&nbsp;&nbsp; SQLCON.ConnectionTimeout = 25&nbsp; ' Time out for the connection &nbsp;&nbsp;&nbsp; SQLCON.Provider = "sqloledb"&nbsp;&nbsp; ' OLEDB Provider &nbsp;&nbsp;&nbsp; SQLCON.Properties("Network Address").Value = &quot;111.111.111.111&quot;&nbsp; ' set the ip address of your sql server &nbsp;&nbsp;&nbsp; SQLCON.CommandTimeout = 180 ' set timeout for 3 minutes &nbsp;&nbsp;&nbsp; ' Now set your network library to use one of these libraries .. un-rem only the one you want to use ! &nbsp;&nbsp;&nbsp; 'SQLCON.Properties("Network Library").Value = "dbmssocn" ' set the network library to use win32 winsock tcp/ip &nbsp;&nbsp;&nbsp; 'SQLCON.Properties("Network Library").Value = "dbnmpntw" ' set the network library to use win32 named pipes &nbsp;&nbsp;&nbsp; 'SQLCON.Properties("Network Library").Value = "dbmsspxn" ' set the network library to use win32 spx/ipx &nbsp;&nbsp;&nbsp; 'SQLCON.Properties("Network Library").Value = "dbmsrpcn" ' set the network library to use win32 multi-protocol</font></font></p> <p><font size="2" color="#008000">&nbsp;&nbsp;&nbsp; 'Now set the SQL server name , and the default data base .. change these for your server !</font><font size="2"><font color="#008000"> &nbsp;&nbsp;&nbsp; SQLCON.Properties("Data Source").Value = &quot;MYSERVERNAME&quot; &nbsp;&nbsp;&nbsp; SQLCON.Properties("Initial Catalog").Value = &quot;MYSQLDATABASE&quot; &nbsp;&nbsp;&nbsp; SQLCON.CursorLocation = adUseServer ' For ADO cursor location &nbsp;&nbsp;&nbsp; 'Now you need to decide what authorization type you want to use .. WinNT or SQL Server. &nbsp;&nbsp;&nbsp; 'un-rem this line for NT authorization.</font></font></p> <p><font size="2"><font color="#008000">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'SQLCON.Properties("Integrated Security").Value = &quot;SSPI&quot;</font></font></p> <p><font color="#008000" size="2">&nbsp;&nbsp;&nbsp;&nbsp; ' Or if you want to use SQL authorization , un-rem these 2 lines and supply SQL server login name and password</font></p> <p><font color="#008000">&nbsp;&nbsp;&nbsp; '</font><font size="2"><font color="#008000">SQLCON.Properties("User ID").Value =&quot;SQLUSERNAME&quot; &nbsp;&nbsp;&nbsp;&nbsp; 'SQLCON.Properties(&quot;Password&quot;).Value = &quot;SQLPASSWORD&quot; </font> <font color="#008000">&nbsp;&nbsp;&nbsp;&nbsp; ' Now we can open&nbsp; the ADO Connection to SQl server&nbsp; !.. &nbsp;&nbsp;&nbsp;&nbsp; SQLCON.Open </font> </font></p> <p>&nbsp;&nbsp;&nbsp;<font size="2" color="#008000"> ' Now we can do a simple test of the new ADO connection &nbsp;&nbsp;&nbsp;&nbsp; ' Lets return the Time and Date the SQL server thinks it is ..</font></p> <p><font size="2" color="#008000">&nbsp;&nbsp;&nbsp; Dim RS As ADODB.Recordset &nbsp;&nbsp;&nbsp; Set RS = New ADODB.Recordset &nbsp;&nbsp;&nbsp; SQLstatement = "SELECT GETDATE() AS SQLDATE &quot; ' Set a Simple Sql query to return the servers time &nbsp;&nbsp;&nbsp; RS.Open SQLstatement, SQLCON&nbsp; ' Lets open a connection with our new SQLCON connection , and our SQL statement &nbsp;&nbsp;&nbsp; ' Move to first row. &nbsp;&nbsp;&nbsp; RS.MoveFirst &nbsp;&nbsp;&nbsp; junk = MsgBox( &quot;Server Time is &quot; &amp; RS(&quot;SQLDATE&quot;), vbOKOnly, &quot; SQL SERVER INFO") </font>&nbsp;&nbsp;&nbsp;</p> <p><font size="2" color="#008000">End Sub</font></p> <p><font color="#008000"> </font><font color="#000000">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.</font></p> &nbsp; <p align="center"><img border="0" src="newlogosmall.jpg" width="480" height="120"></p> <p align="center">