Connecting to an SQL Server

The ADO provides a number of ways of connecting to an SQL Server database. To access data residing on Microsoft SQL Server 6.5, 7.0, or 2000, you can use SQLOLEDB, which is the native Microsoft OLE DB provider for SQL.

©Hands-On 10-12: Connecting to an SQL Server Database

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the ConnectToSQL_SQLOLEDB procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub ConnectToSQL_SQLOLEDB() Dim conn As ADODB.Connection

Set conn = New ADODB.Connection ' Modify the connection information With conn

.Provider = "SQLOLEDB"

.ConnectionString = "Data Source=Mozartv4;" & _ "database=Musicians; UserId=sa; Password=;" .Open End With

If conn.State = adStateOpen Then

MsgBox "Connection was established." End If conn.Close Set conn = Nothing End Sub

Notice that in the example above, the Connection object's Provider property is set to SQLOLEDB and the ConnectionString property includes a server name, database name, user ID, and password information. You should modify the

Creating and Manipulating Databases with ADO

connection information in the connection string to connect to the SQL Server database you have access to.

Another way of connecting to an SQL database is by using the MSDASQL provider. This provider allows you to access any existing ODBC data sources. You can open a connection to your remote data source by using an ODBC DSN. This, of course, requires that you create a Data Source Name (DSN) entry on your workstation via the 32-bit ODBC tool in the Windows Control Panel (in Windows 2000, use Data Sources (ODBC), which is available in Administrative Tools in the Windows Control Panel).

The code example below opens then closes a connection with the remote data source based on a DSN named Pubs. You could skip setting the Provider property because MSDASQL is the default provider for ADO. All you really need to establish a connection in this case is a DSN.

With conn

.Open "Provider=MSDASQL; DSN=Pubs" .Close End With

DSN connections are inconvenient, as they require that you create a Data Source Name (DSN) on each user computer. Fortunately, there is a workaround. You can create a DSN-less connection independent of user workstations. Instead of creating a DSN on a user machine, simply provide all the connection information to the ODBC data source in your VBA code. See the two procedures in Hands-On 10-13 for the complete example of establishing DSN-less connections. (Review Hands-On 10-7 if you need more practice with DSN-less connections.)

©Hands-On 10-13: Connecting to an SQL Server Using a DSN-less Connection

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Connect_ToSQLServer and Connect_ToSQLServer2 procedures shown below.

3. Choose Run | Run Sub/UserForm to execute each procedure.

Sub Connect_ToSQLServer() Dim conn As ADODB.Connection

Set conn = New ADODB.Connection With conn

' DSN-less connection using the ODBC driver ' (modify the data source information below) .Open "Driver={SQL Server};" & _ "Server=11.22.17.153;" & _ "UID=myId;" & _ "PWD=myPassword;" & _ "Database=SupportDb" .Close End With

Part II

Set conn = Nothing End Sub

Sub Connect_ToSQLServer2() Dim conn As ADODB.Connection

Set conn = New ADODB.Connection With conn

' DSN-less connection using the SQLOLEDB provider ' (modify the data source information below) .Open "Provider=SQLOLEDB;" &_ "DataSource=Mozart;" & _ "Initial Catalog=MusicDb;" & _ "UID=myId; Password=myPassword;" .Close End With

Set conn = Nothing End Sub

0 0

Post a comment