Connecting to SQL Server Programmatically

This is a very basic example of how to connect to a SQL Server. The Recordset object and Connection object have many more properties than these.

For more information about the other properties, please go to:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnado/html/msdn_workshp2.asp?frame=true

Begin with making sure your Project has a reference to 'Microsoft ActiveX Data Objects 2.x Library' where x is (preferably) the highest number.

[tt]
' **** Create Connection and Recordset Objects ****
Dim cnConn As ADODB.Connection
Dim rs As ADODB.Recordset

' **** Instantiate the new objects ****
Set cnConn = New ADODB.Connection
Set rs = New ADODB.Recordset

' **** Set up connection String ****
With cnConn
.Provider = "SQLOLEDB.1"
.Properties("Data Source").Value = "SQLServerName"
.Properties("User ID").Value = "UserName"
.Properties("Password").Value = "Password"
.Properties("Initial Catalog").Value = "DatabaseName"
.CursorLocation = adUseClient
.ConnectionTimeout = 0

.Open

End With

' **** String to pass to the connection to get records back ****
strSQL = "SELECT * FROM TableName"

' **** Open Records for readonly ****
rs.Open strSQL, cnConn, adOpenStatic, adLockReadOnly

[/tt]

Do what you need to with the recordset.

When you are finished with it:

[tt]
' **** Close Objects ****
rs.Close
cnConn.Close

[/tt]

If you are completely finished with them:

[tt]
' **** Destroy objects ****
Set rs = Nothing
Set cnConn = Nothing
[/tt]