Connecting to a Server

To access data on a remote server, your application must establish a connection. This connection creates a communication link to the server across the network. You can use any of the following methods to connect to a server:

• Link tables and SQL views from the server
• Use pass-through queries to send SQL statements straight to the server
• Directly access the server by using Visual Basic

This section describes each of these methods, as well as their respective time and memory requirements on both the client and the server.

Linking Tables and SQL Views from a Server

The easiest and most common way to access data on a server is to link tables from the server. With few exceptions, you can use linked tables in your application just as you would Microsoft Access tables. Microsoft Access automatically connects to the server when you open a linked table.

If your server supports SQL views, you can link them to your local database. An SQL view is a virtual table that is derived from other tables or previously defined views. It has a name, a list of attributes, and a query which define the contents of the view. Microsoft Access treats a linked view exactly like a linked table without indexes. The processing defined in a view is always performed by the server.

Û To link a table or an SQL view from a server

In your Visual Basic code, create an ODBC connection string that references the data source you set up with the ODBC Data Source Administrator. Assign the connection string to a variable.

1. Create a new TableDef object and set its Connect property to the variable you created in step 1.
2. Append the TableDef object to the TableDefs collection to link the table or SQL view to your Microsoft Access database.
3. Open a Recordset object on the linked table or SQL view. You can use the properties and methods of the Recordset object to manipulate the data in the ODBC data source.

The following example uses the Pubs sample database included with Microsoft SQL Server to link the Authors table, and counts the number of authors whose phone number begins with the area code that is passed as an argument to the function.

Function CountByAreaCode(strAreaCode As String) As Long
	Dim dbs As Database, rst As Recordset
	Dim strConnect As String, tdf As TableDef
	Dim lngCount As Long

	strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"

	Set dbs = OpenDatabase("C:\data\cs.mdb")

	' Delete any existing links named LinkedAuthors.
	On Error Resume Next
	dbs.TableDefs.Delete "LinkedAuthors"
	dbs.TableDefs.Refresh

	On Error GoTo ErrorHandler

	' Create a TableDef and set its Connect property.
	Set tdf = dbs.CreateTableDef("LinkedAuthors")
	tdf.Connect = strConnect
	' Specify the table to access within the Pubs database.
	tdf.SourceTableName = "Authors"
	dbs.TableDefs.Append tdf
	dbs.TableDefs.Refresh

	' Open a recordset on the linked table.
	Set rst = dbs.OpenRecordset("LinkedAuthors", dbOpenForwardOnly)

	' Count number of authors whose phone number begins with specified area code.
	Do Until rst.EOF
		If Left$(rst!phone, 3) = strAreaCode Then
			lngCount = lngCount + 1
		End If
		rst.MoveNext
	Loop
	rst.Close
	dbs.Close
	CountByAreaCode = lngCount
	Exit Function

ErrorHandler:
	MsgBox "Error " & Err & ": " & Err.Description
	Exit Function
End Function

In this example, the Authors Recordset object is opened as a forward-only-type Recordset object to optimize performance. Because forward-only-type Recordset objects are snapshots, you cannot update data in them. If you want to be able to update data in the recordset, open it as a dynaset-type Recordset object by using the dbOpenDynaset constant with the OpenRecordset method.

Note   Although you can update information in ODBC databases by using update queries or dynaset-type Recordset objects, you should consider using a pass-through query, which is usually much more efficient. For more information on pass-through queries, see the next section “Using Pass-Through Queries.”

Microsoft Access stores field and index information for the tables you link, which improves performance when you open the tables. Relink remote tables if you change their structure or the structure of their indexes on the server.

See Also   For information on relinking tables, search the Help index for “RefreshLink method.”

Tip   When you link a table from a server, you usually have the option of storing your server password in the local database so that you don’t have to type it each time you start the application. However, if you don’t want users to have this option when they link tables, you can disable the feature. For more information, search the Help index for “MSysConf table.”

See Also   For information on linking remote tables and creating connection strings, see Chapter 18, “Accessing External Data.” For general information on linking, search the Help index for “tables, linking.”

Creating Indexes on Linked SQL Views

If your server allows you to update data by using SQL views and you want to take advantage of this updatability in Microsoft Access, you need to create a pseudo index on the linked view. This index tells Microsoft Access the field or fields that make up the primary key for a record returned by the view—the field or fields that uniquely identify the record. Microsoft Access can then create an updatable dynaset on the view, which forms and queries can use to view and update data.

For example, suppose you link an SQL view named SeptOrdersView that returns a subset of records in the remote Orders table, and you name the linked view SeptemberOrders. Because the OrderID field is still unique within the view, you would use a data-definition query to create the index by running the following SQL statement:

CREATE UNIQUE INDEX index1 ON SeptemberOrders (OrderID)

This doesn’t create a real index on the server or in your local database, nor does it take up much space locally. It simply tells Microsoft Access that the OrderID field is the primary key for the linked view.

See Also   For more information on data-definition queries, search the Help index for “data-definition queries.”

Using Pass-Through Queries

The second way to access data on a server is to use pass-through queries. Pass-through queries are SQL statements that are sent directly to a database server without interpretation by Microsoft Access. You can use pass-through queries in your application to directly manipulate the features of a database server that may or may not be supported by Microsoft Access.

In many applications, you’ll use both ordinary Microsoft Access queries based on remote tables and pass-through queries. When you use an ordinary query to retrieve information from the tables on your database server, Microsoft Access compiles and runs the query, combining the power of the server with the capabilities of the Microsoft Jet database engine. With a pass-through query, you create an SQL statement that Microsoft Access sends directly to the server without stopping to compile the query.

Pass-through queries offer the following advantages over ordinary Microsoft Access queries:

• You don’t need to consider how much of your pass-through query is being sent to the server for processing because the query is sent without modifications. This means that more processing occurs on the server and less traffic is generated over the network.
• Pass-through queries can use server-specific functionality—such as stored procedures and server-based intrinsic functions—that have no Visual Basic equivalent.
• Pass-through queries can log informational messages returned by the server, such as errors, warnings, and statistics.
• With pass-through queries, you can use nonstandard SQL extensions, as well as data-definition, server administration, and security commands that are supported by the server.
• Use a pass-through query if you want to join tables from different databases on a server (if the server supports multiple databases). Because each database is a separate ODBC data source, an ordinary query would consider the join to be heterogeneous and would run at least part of the query locally.

  See Also   For more information on heterogeneous joins, see “Heterogeneous Joins” later in this chapter.

• Bulk update, delete, and append pass-through action queries are faster than action queries based on linked remote tables, especially when many records are affected. In addition, a pass-through action query cannot partially succeed as an ordinary action query can.

On the other hand, pass-through queries do have several disadvantages:

• A pass-through query always returns a snapshot, which can’t be updated. An ordinary query usually returns a dynaset, which reflects other users’ changes and can be updated.
• When you design pass-through queries, you type the commands directly into the SQL Pass-Through Query window. In contrast, when you design ordinary queries, you use the graphical query design grid.
• To create a pass-through query, you must know the exact SQL syntax supported by your server. SQL syntax and conventions often vary from server to server, particularly outer-join syntax, intrinsic function names, wildcard characters, and the use of single versus double quotation marks. All ordinary Microsoft Access queries use the same syntax, regardless of where the data is stored. Microsoft Access automatically translates Visual Basic intrinsic function names, wildcard characters, and other elements into the appropriate ODBC syntax.
• You can’t call user-defined functions in a pass-through query, but ordinary queries can use them as if they were intrinsic Visual Basic functions.
• You can add parameters to an ordinary query, causing Microsoft Access to automatically prompt the user for the parameter value. (Alternatively, the value can be supplied from a form control.) In contrast, you can’t add parameters to pass-through queries.

Creating Pass-Through Queries

Pass-through queries consist of an SQL statement and an ODBC connection string. The SQL statement is interpreted only by the database server and must follow the server’s SQL language specification. The SQL statement cannot contain any Microsoft Access–specific elements or any Visual Basic functions or commands.

See Also   For information on connection strings, see Chapter 18, “Accessing External Data.”

Û To create a pass-through query

1. Create a QueryDef object by using the CreateQueryDef method. Specify only the name argument for the query. If you want to create a temporary QueryDef object, specify a zero-length string ("") for the name argument.
2. Set the Connect property of the QueryDef object to a valid connection string.

   Important Always set the Connect property before you set the SQL property so that Microsoft Jet passes the query directly to the ODBC data source without processing it.

3. Set the SQL property of the QueryDef object to an SQL statement that is compatible with the ODBC data source you are querying.
4. If your query is an action query that does not return a recordset, set the ReturnsRecords property of the QueryDef object to False and use the Execute method to run the query. Otherwise, set the ReturnsRecords property to True and run the query by opening a recordset on the stored query definition.

The following example sends three pass-though action queries to the Pubs sample database included with Microsoft SQL Server. The first query creates a new table called sales_archive with the same structure as the Sales table in the Pubs database. The second query appends all of the sales records that were created before 1993 from the Sales table to the sales_archive table. The third query deletes all of the sales records created before 1993 from the Sales table.

Sub ArchiveSales()
	Dim dbs As Database, qdf As QueryDef
	Dim strConnect As String
	Dim strSQL As String

	On Error GoTo ErrorHandler

	Set dbs = CurrentDb
	strConnect = "ODBC;DSN=Pubs;UID=sa;PWD=;DATABASE=Pubs"
	Set qdf = dbs.CreateQueryDef("")
	qdf.Connect = strConnect

	strSQL = "CREATE TABLE sales_archive (stor_id char (4) "
	strSQL = strSQL & "NOT NULL, ord_num varchar (20) NOT NULL, "
	strSQL = strSQL & "ord_date datetime NOT NULL, "
	strSQL = strSQL & "qty smallint NOT NULL, "
	strSQL = strSQL & "payterms varchar (12) NOT NULL, "
	strSQL = strSQL & "title_id tid NOT NULL)"

	qdf.ReturnsRecords = False

	qdf.SQL = strSQL
	qdf.Execute

	qdf.SQL = "INSERT INTO sales_archive SELECT * FROM sales WHERE ord_date < '1/1/93'"
	qdf.Execute

	qdf.SQL = "DELETE FROM sales WHERE ord_date < '1/1/93'"
	qdf.Execute

	Exit Sub

ErrorHandler:
	MsgBox "Error " & Err & ": " & Err.Description
	Exit Sub
End Sub

In the previous example, the ReturnsRecords property is set to False because all three queries are action queries. The following example uses a pass-through query to return records. It returns all sales records in the Pubs sample database for the year 1994.

Dim dbs As Database, qdf As QueryDef
Dim strConnect As String, rst As Recordset
Dim strSQL As String

Set dbs = CurrentDb
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
Set qdf = dbs.CreateQueryDef("94Sales")
qdf.Connect = strConnect
strSQL = "SELECT * FROM sales WHERE ord_date BETWEEN "
strSQL = strSQL & "'1/1/1994' AND '12/31/1994'"
qdf.SQL = strSQL
qdf.ReturnsRecords = True

Set rst = dbs.OpenRecordset("94Sales", dbOpenSnapshot)

Do Until rst.EOF
	Debug.Print rst!ord_date
	rst.MoveNext
Loop

Note Recordset objects opened on a pass-through queries are always opened as snapshots and cannot be updated.

See Also   For more information on pass-through queries, search the Help index for “pass-through queries.”

Using Stored Procedures

In some environments, your access to server data is limited to a set of server-based stored procedures. You must carry out all data requests and updates through these stored procedures; you have no direct access to the remote tables. In such an environment, you use pass-through queries exclusively.

Your server may have two sets of stored procedures defined: one to retrieve data and one to update data. If you don’t need to update data and only want to retrieve data, you can create a pass-through query for each stored procedure you want to call. You can then base other queries, forms, and reports on these queries as if they were linked tables.

However, if you need to update data in this type of environment, you can collect the user’s input locally and then send the data to the server by running a pass-through query that calls the appropriate stored procedure. You may find either of the following two approaches helpful:

• Create a form based on a local holding table that temporarily stores records before they are sent to the server. Add code to the form’s AfterUpdate and AfterInsert event procedures that calls the data-updating stored procedures for each record in the holding table.
• Create an unbound form to collect data and add command buttons to the form for saving the data. You can then use the values in the form’s unbound controls when you call the data-updating stored procedures.

In either case, you need to write Visual Basic code to create a pass-through query that uses the name of the stored procedure and the values to be supplied from the form.

If the stored procedure runs an action query, you can determine whether the stored procedure ran successfully by examining the first element of the Fields collection of the Recordset object that the stored procedure is based on. If the value is 0, then the procedure ran successfully. Otherwise, the procedure did not run successfully.

For example, the following SQL Server stored procedure increases or decreases the royalty schedules in the Roysched table of the Pubs sample database included with Microsoft SQL Server. The amount of the increase or decrease is specified in the integer parameter named delta:

CREATE PROCEDURE change_royalty @delta int AS UPDATE roysched
	SET royalty = royalty + @delta

The following function calls the change_royalty stored procedure in the Pubs sample database, examines the first element of the Fields collection, and returns 0 if the stored procedure ran successfully.

Function intChangeRoyalty(intDelta As Integer) As Integer
	Dim dbs As Database, qdf As QueryDef
	Dim strConnect As String, rst As Recordset
	Dim strSQL As String
	On Error GoTo ErrorHandler
	Set dbs = CurrentDb
	strConnect = "ODBC;DSN=Pubs;UID=sa;PWD=;DATABASE=Pubs"
	strSQL = "declare @status int execute @status = change_royalty"
	strSQL = strSQL & intDelta & " select 'return value' = @status"
	Set qdf = dbs.CreateQueryDef("")
	qdf.Connect = strConnect
	qdf.ReturnsRecords = True
	qdf.SQL = strSQL
	Set rst = qdf.OpenRecordset(dbOpenSnapshot)
	' Examine the first element in the recordset's Fields collection.
	' If it is 0, the stored procedure was successful.
	If rst(0) = 0 Then 
		intChangeRoyalty = 0 
	Else
		intChangeRoyalty = -1
	End If
	Exit Function

ErrorHandler:
	MsgBox "Error " & Err & ": " & Err.Description
	intChangeRoyalty = -1
	Exit Function
End Function

Directly Accessing the Server

The third way to connect to a server is to access it directly in Visual Basic by using the OpenDatabase method with an ODBC connection string. This approach has several disadvantages, however. Although it’s possible to directly open snapshots and dynasets on a remote database, this is always much less efficient than using linked tables. When you link remote tables, Microsoft Jet caches locally a great deal of information about these tables, including field information, index information, and server capabilities. In contrast, when you access the remote tables directly, Microsoft Access has to ask the server for this information every time you run a query.

Note   You’ll want to access the server directly when you preconnect. For information on preconnecting, see “Preconnecting” later in this chapter.