Access Cookbook - Ken Getz [180]
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strEmployees As String
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB.1;" _
& "Data Source=(local);Initial Catalog=Northwind;" _
& "User ID=username;Password=secretpwd"
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open _
Source:="SELECT EmployeeID," _
& " LastName, FirstName" _
& " FROM Employees" _
& " WHERE EmployeeID = 5", _
ActiveConnection:=cnn, _
CursorType:=adOpenStatic, _
Options:=adCmdText
Set rst.ActiveConnection = Nothing
cnn.Close
Set cnn = Nothing
Debug.Print rst("FirstName")
rst.Close
Set rst = Nothing
Discussion
Understanding how client/server applications differ from single-user and file-server applications is crucial to optimizing their performance. The key is in deciding when to let Access do the work and when to let the server do the work. With a few exceptions, you want the server to perform queries and Access to perform user-interface operations. Concentrate on minimizing the traffic across the network by reducing the data retrieved from and written to the server. To work with server data programmatically, use ADO rather than DAO.
TIP
Access includes a wizard called the Performance Analyzer. You should use this wizard to analyze the performance of all your forms (and other database objects). Although it is somewhat limited in the suggestions it can make, it's a nice way to check if you've missed any obvious problems. For example, when running the Analyzer against the queries in 08-04.MDB, it will suggest adding several indexes.
Chapter 9. User Interface
No matter how much you do behind the scenes to create a solid and robust application, the users of your application see only your user interface. Certainly, perfecting the database and application design is crucial—but once that's done, it pays to devote considerable time to designing a user interface that is workable, aesthetically pleasing, and helps the users get their work done. By implementing the ideas and techniques in this chapter, you'll be on your way to creating an interface that has ease of use and productivity written all over it.
You'll learn how to take full advantage of special keystrokes to help users navigate through a complex application. You'll also learn how to create forms that have no menus or toolbars and how to create a map-based interface that lets users navigate by pointing to and clicking on various parts of a map.
Next, you'll learn how to ease data-entry pain with forms that let users mark their place while they peruse other records, and how to add shortcut menus to forms. You'll also see how you can create forms that carry data forward from record to record, how to hide complexity from your users with a dialog that expands on request to reveal complex options, and how to use a combo box not just to select from a list, but also to maintain that list with new entries as they are needed.
Finally, you'll learn how to create and use two generic, reusable components: a pop-up calendar form for entering dates that makes use of an ActiveX control, and a custom-built status meter form complete with an optional Cancel button.
WARNING
Several of the examples in this chapter take advantage of the DAO type library, rather than the default ADO library used by Access 2002 and Access 2003. Even though it's less "modern," DAO provides greater functionality, and generally better performance. In addition, using DAO makes it possible for these demonstrations to work in earlier versions of Access. If you want to try these techniques in your own applications, make sure you add the DAO reference to your project using the Tools → References menu item from within VBA—it won't be added by default.
9.1. Create Context-Sensitive Keyboard Shortcuts
Problem
You've used Access's AutoKeys macro to create keyboard shortcuts for your