Connecting to a SQL Server Data Source in VB

A. Using SQLOLEDB to connect to an instance of SQL Server: setting individual properties

The following Microsoft Visual Basic® code fragments from the ADO Introductory Visual Basic Sample show how to use SQLOLEDB to connect to an instance of SQL Server.

	' Initialize variables.
	Dim cn As New ADODB.Connection
	Dim ServerName As String, DatabaseName As String
	' Put text box values into connection variables.
	ServerName = txtServerName.Text
	DatabaseName = txtDatabaseName.Text
	' Specify the OLE DB provider.
	cn.Provider = "sqloledb"
	' Set SQLOLEDB connection properties.
	cn.Properties("Data Source").Value = ServerName
	cn.Properties("Initial Catalog").Value = DatabaseName
	' Windows NT authentication.
	cn.Properties("Integrated Security").Value = "SSPI"
	' Open the database.
	cn.Open
	'B. Using SQLOLEDB to connect to an instance of SQL Server: connection string method
	'The following Visual Basic code fragment shows how to use SQLOLEDB to connect to an instance or SQL Server:
	' 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
	'C. Using MSDASQL to connect to an instance of SQL Server
	'To use MSDASQL to connect to an instance of SQL Server, use the following types of connections.
	'The first type of connection is based on the ODBC API <b>SQLConnect</b> function.
	'This type of connection is useful in situations where you do not want to code specific information about the data source.
	'This may be the case if the data source could change or if you do not know it particulars. <br />
	'In the code fragment shown, the <b>ConnectionTimeout</b> method sets the connection time-out value to 100 seconds.
	'Next, the data source name, and authentication type are passed as parameters to the <b>Open</b> method of the <b>Connection</b> object, using an ODBC data source named MyDataSource that points to the <b>northwind </b>database on an instance of SQL Server. <br />
	Dim cn As New ADODB.Connection
	cn.ConnectionTimeout = 100
	' DSN connection
	' cn.Open "DSN=MyDataSource;Trusted_Connection=yes;"
	cn.Close
	'The second type of connection is based on the ODBC API SQLDriverConnect function.
	'This type of connection is useful in situations where you want a driver-specific connection string.
	'To make a connection, use the Open method of the Connection object and specify the driver, server name, authentication type,
	'and database. You can also specify any other valid keywords to include in the connection string.
	'For more information about the keyword list, see <a href="http://msdn.microsoft.com/en-us/library/aa177865%28v=sql.80%29.aspx">SQLDriverConnect</a>. <br />
	Dim cn As New ADODB.Connection
	' Connection to SQL Server without using ODBC data source.
	cn.Open "Driver={SQL Server};Server=Server1;Database=northwind;Trusted_Connection=yes"
	cn.Close

view raw sqlserverconnect.vb hosted with ❤ by GitHub