Microsoft Office 2000/Visual Basic Programmer's Guide

Working with Access Objects in an Access Project

You can use most of the same design tools as those available for an Access database to work with forms, reports, data access pages, macros, and VBA modules in an Access project file. To create any of these objects, open the Database window, select the type of object you want to create, and then click New.

In addition to the Design views for creating forms and reports from scratch, Access projects provide the following wizards for creating forms and reports: the Form Wizard, the AutoForm wizards, the Report Wizard, the AutoReport wizards, and the Label Wizard. A form's or report's RecordSource property can be set to tables, views, and any stored procedures or SQL statements that return a single set of records. When you use a stored procedure as the record source for a form or report, you can use the form's InputParameters property to specify the source for the stored procedure's input arguments. For information about setting the InputParameters property, search the Microsoft Access Visual Basic Reference Help index for "InputParameters property."

Data access pages support the same design tools for both Access databases and SQL Server back-end databases: the Data access page Design view, the Page Wizard, and the Microsoft Script Editor. For more information about working with data access pages, see Chapter 5, "Working with Office Applications," Chapter 12, "Using Web Technologies," and the Microsoft Access Help index (search for "data access pages").

Macros provide additional actions and action arguments to allow you to create macros that work with SQL Server objects. The same Visual Basic Editor is used from Access projects to create VBA code.

Using the Connection Property of the CurrentProject Object in an Access Project

When you use the Connection property of the CurrentProject object in an Access database (.mdb), Access returns a connection that uses the Microsoft Jet 4.0 OLE DB Provider. However, when you use the Connection property of the CurrentProject object in an Access project (.adp), Access doesn’t return a direct connection to the SQL Server database. Instead it uses the Microsoft Data Shaping Service for OLE DB, which is an OLE DB service component that runs on top of a data provider. For Access project files, the data provider used in conjunction with the Microsoft Data Shaping Service for OLE DB is the Microsoft OLE DB Provider for SQL Server. This is required because updates to data in forms in an Access project require the Shaping Service. This also is a requirement if you use the Recordset property of a form in an Access project to programmatically specify the form’s data source. For more information about using the Recordset property of a form in an Access project, see "" later in this chapter.

The fact that the Connection property returns a Connection object that uses the Microsoft Data Shaping Service for OLE DB does have certain consequences:

Recordset objects always use the client-side cursor engine (CursorLocation=adUseClient). Even if you set the CursorLocation property of a Recordset object to adUseServer prior to creating a Recordset object by using the Execute method of a Connection object, the Execute method of a Command object, or the Open method of a Recordset object, you will always get a client-side cursor.
Note For more information about cursors and cursor engines, see Chapter 14, "Working with the Data Access Components of an Office Solution."
Recordset objects always use a Static cursor (CursorType=adOpenStatic), regardless of what you set the CursorType property to when opening the Recordset object.
You can set the LockType property of a Recordset object to the adLockOptimistic, adLockBatchOptimistic, or adLockReadOnly constants. If you set the LockType to adLockPessimistic, it gets converted silently to adLockOptimistic. As with Recordset objects created with other connections, if you don't specify the LockType property, it will be set to adLockReadOnly by default.
A Connection object established by using the Microsoft Data Shaping Service for OLE DB doesn't support all ADOX operations that a Connection object established by using a direct connection through the Microsoft OLE DB Provider for SQL Server would provide. In particular, operations on the Properties collection of the Column object are not supported.
Note The ADOX Views, Users, and Groups collections are not supported against SQL Server when you use connections through either the Microsoft Data Shaping Service for OLE DB or the Microsoft OLE DB Provider for SQL Server.
The Properties collection of a Connection object doesn't support the same properties as a direct connection. For example, the DBMS Version property is not supported.
If the CommandType property of a Command object isn't specified, the connection will not try again to open the Command object as a table if the CommandText property doesn't refer to a stored procedure. You can avoid this problem either by specifying an SQL statement to open the table (for example, "SELECT * FROM Authors"), or by explicitly specifying the CommandType as adCmdTable when opening the Command object, as shown in the following code fragment.
```
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

' Specify properties of the Command object.
With cmd
   .ActiveConnection = CurrentProject.Connection
   .CommandText="authors"
   .CommandType=adCmdTable
End With

' Open the Recordset object by using the Command object.
Set rst=cmd.Execute
```

You can also avoid this and any other limitations that you might encounter when using Microsoft Data Shaping Service for OLE DB by creating a separate connection that doesn't use the Shaping Service. You can do this by using the BaseConnectionString property of the CurrentProject object. The BaseConnectionString property returns a connection string that specifies the Microsoft OLE DB Provider for SQL Server (Provider=SQLOLEDB.1). The following code fragment shows how to open a Connection object by using the BaseConnectionString property.

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

cnn.ConnectionString = CurrentProject.BaseConnectionString
cnn.Open

Set rst = cn.Execute("authors")

For more information about the features of the Microsoft Data Shaping Service for OLE DB, search ADO Help for "data shaping."

The Form Recordset Property

In an Access project file you can use the Recordset property of an Access Form object to specify an ADO Recordset object that will be used as the form's record source. If you want the record source to be updatable, you must specify that the Connection object used to open the Recordset object is using the Microsoft Data Shaping Service for OLE DB. To do this, you must set the Provider property in the connection string that is used to open the Connection object to MSDataShape. Additionally, you need to specify that the Data Provider property in the connection string is using the Microsoft OLE DB Provider for SQL Server (Data Provider=SQLOLEDB), and other properties are set as described in the following table.

Connection string property	Description
Provider	This property must be set to MSDataShape to specify the Microsoft Data Shaping Service for OLE DB.
Data Provider	This property must be set to SQLOLEDB to specify the Microsoft OLE DB Provider for SQL Server. If you use any other OLE DB provider, it will return a read-only connection.
Data Source	This property specifies the server name, which is the same as the network name of the computer on which SQL Server is running. To view this name, open the Control Panel on the computer, click the Network icon, and read the Computer Name on the Identification tab.
Initial Catalog	This property specifies the name of the database to open.
User ID	If you are using SQL Server Authentication, use this property to specify the user account name (such as "sa", the default user account). Don't set this property if you are using Windows NT Authentication.
Password	If you are using SQL Server Authentication, use this property to specify the password for the user account specified with the User ID property. Don't set this property if you are using Windows NT Authentication.
Trusted_Connection	If you are using Windows NT Authentication instead of SQL Server Authentication, set this property to Yes.

For example, the following code fragment opens an ADO Recordset object directly by passing a connection string to the ActiveConnection argument of the Open method. It then assigns the Recordset property of the current form to that Recordset object.

Dim rst           As ADODB.Recordset
Dim strConnect    As String
Dim strSQL        As String

strConnect = "Provider=MSDataShape;Data Provider=SQLOLEDB;" _
   & "User ID=sa;Password="";Data Source=MyServer;" _
   & "Initial Catalog=NorthwindCS"

strSQL = "SELECT ContactName, CompanyName, Address, City _
   FROM Customers WHERE (Region = 'SP')

Set rst = New ADODB.Recordset
With rst
         .Open Source:=strSQL, _
         ActiveConnection:=strConnect, _
         LockType:=adLockOptimistic
End With

Set Me.Recordset = rst

As noted in "Using the Connection Property of the CurrentProject Object in an Access Project," a connection established through the Microsoft Data Shaping Service for OLE DB always uses the client-side cursor engine (CursorLocation=adUseClient) and returns a Static cursor (CursorType=adOpenStatic). Additionally, you can only set the LockType property to one of these constants: adLockOptimistic, adLockBatchOptimistic, and adLockReadOnly; setting the property to adLockPessimistic will return adLockOptimistic.

For more information about opening ADO Recordset objects, see Chapter 14, "Working with the Data Access Components of an Office Solution."

Limiting the Number of Records Returned for a Form

The navigation buttons on a form in an Access project include a Cancel Query button that a user can use to stop downloading data from the server to the Access project record cache if this operation is taking too long. There is also a Maximum Record Limit button that allows the user to specify the maximum number of records that will be cached. The default value is 10,000. You can change the default value used for all new forms and for datasheets by setting Default max records on the Advanced tab of the Options dialog box (Tools menu). You can set this value on a per-form basis by setting the MaxRecords property of the form in Design view. You can also limit the number of records displayed in a form by specifying a filter that uses the ServerFilter and ServerFilterByForm properties. For information about how to use the ServerFilter and ServerFilterByForm properties, search the Microsoft Access Visual Basic Reference Help index for "ServerFilter property" and "ServerFilterByForm property."