An Access Explorer for SQL Server Tables

Rick Dobson follows up last month’s article with another tool for exploring SQL Server databases. Rick’s table explorer allows you to connect to any database, pull up any table in the database, and review or update the data in the table.

Last month, I demonstrated how to explore SQL Server instances, databases, and their objects with VBA and SQL-DMO (SQL Distributed Management Objects) from either Access database files or Access projects (see “A SQL Server Database and Object Explorer” in the July 2003 issue). As developers know, Access lets you build SQL Server applications, but it doesn’t include the tools that would allow you to manage your SQL Server database. This article not only shows you how to build a tool that will let you explore a table in any database at any time, but it will also show you how to program against SQL Server databases.

Last month’s article covered how to log into SQL Server instances using either Windows or SQL Server authentication. This article reinforces and extends those principles by focusing specifically on how to browse and edit data in SQL Server tables.

The accompanying Download file lets you view and edit column data from any table on the local default SQL Server instance without creating linked tables or ODBC settings. In addition, I’ll show you how to dynamically add controls to an Access form with the controls based on the contents of a SQL Server table. Just to prove that SQL-DMO code can be used from Access Data Projects and Access database files, the sample code and forms for this article reside in an Access database file (last month’s article used an Access project). For either Access Data Projects or Access databases, you need to add a reference to the SQL-DMO library.

Application design

The sample application for this article begins with the frmSelectATable form. It displays an Explorer window that lets a user select a database from a local SQL Server instance (such as the default installation of MSDE 2000 on the same computer). The application accesses the database by using Windows-integrated security, but users can override the default login setting by specifying a SQL Server login and its password. Whether connecting to the local default SQL Server instance using either Windows or SQL Server logins, the application connects using SQL-DMO and saves the login settings.

By selecting different databases from a list box in frmSelectATable, users can populate a second list box with the names of tables from the selected database. Clicking the name of a table displays a second form, frmShowATable, which initially exposes just the first row from the selected table. This second form allows a user to navigate through the rows in the selected table via a set of command buttons enabling four functions (first, next, previous, and last). Text boxes with corresponding labels in frmShowATable display column values for the currently selected row. The application generates the text boxes and labels at runtime for frmShowATable based on the table selected in frmSelectATable. A fifth button on the second form enables the updating of column values for the currently displayed row.

Accessing databases

Figure 1 shows the frmSelectATable form in design view, along with several entries that I’ll discuss. The form includes an option group with a pair of option buttons whose labels are Windows and SQL Server. These two buttons select the two different types of user authentication that the application can use. Users should populate the Login and Password text boxes when selecting the SQL Server control. Clicking the SQL Server option button automatically triggers an attempt to log in with the values specified in the two text boxes. Failing to enter valid Login and Password values generates an error message, which reminds users how to recover from the error. After clearing the error message, the application attempts to reconnect with Windows authentication. This will work, assuming that the local default SQL Server instance recognizes the Windows login of the current user.

Figure 1

After logging a user into the local default SQL Server instance, the application populates the first list box, lstDatabases, with the names of databases on the SQL Server instance. Figure 1 shows the NorthwindCS database selected. In order for a selection from lstDatabases to succeed, the SQL Server login must have access to the selected database. If you’re logging in as the administrator of the local default SQL Server instance via a Windows login, then you can select any database on the server. If you specify another SQL Server or Windows login, you may or may not have access to all the databases listed in lstDatabases. The ability to successfully select a database depends on whether the login has a user account for the database or the database has a guest account that grants access to users without a user account for the database. A valid choice populates the second list box, lstTables, with the names of tables in the selected database. An incorrect database choice generates a message with a reminder about how to recover from the error.

When a user selects a table from lstTables, the application prepares frmShowATable for display. There’s actually a form control builder in the AfterUpdate event procedure for lstTables. The builder adds labels and text boxes to frmShowATable based on the selected table in the lstTables control. The event procedure serves as more than just a form control builder, though. For example, the event procedure generates a recordset based on the selected table. Additionally, the event procedure copies login settings and a reference for the recordset to variables declared in Module1 with the Public keyword. Variables declared in this way are available to all procedures in all modules. Therefore, the code behind frmShowATable has access to those values originally generated in the code behind frmSelectATable.

Accessing a database

The code behind frmSelectATable breaks down into four units. The first block includes a module-level declaration for a SQLServer object that gets used in more than one procedure. It also includes the Form_Load event procedure that runs when a user opens frmSelectATable, and a utility procedure for clearing a list box. The load event procedure has two main objectives. First, it logs into the local default SQL Server instance using a Windows login (see last month’s article for an explanation of the syntax). The other main objective is to prepare the two list boxes. A loop populates the lstDatabases ListBox control, and a utility procedure (ClearlstTables) clears the lstTables ListBox control. By clearing the lstTables control in a utility procedure, the code is available for reuse by another procedure in the application:

The second block of code processes selections from the option group control, optLogin, and entries to the two text boxes, txtLogin and txtPassword. Changing the value in either text box calls the optLogin_AfterUpdate procedure. This procedure logs into the local default SQL Server instance with either a Windows login or a SQL Server login specified by the value in the two text boxes. After you have txtLogin and txtPassword initially populated, you can re-specify the contents of either text box to log into the server. The application throws an error whenever the login information isn’t valid:

Accessing a table

The third block of code is for the AfterUpdate event of the lstDatabases control. The lstDatabases_AfterUpdate event procedure performs three tasks. First, it clears the lstTables control from any prior table names that appear from previous selections. Second, the event procedure populates the lstTables control with the names of the tables from the database just selected in the lstDatabases control. The third task is optional, and it will only occur if the second task can’t succeed because the login specified in the Form_Load or optLogin_AfterUpdate procedure doesn’t have access to the database selected in the lstDatabases control. The message that appears after a failed attempt to open a database reminds the user to change the login, password, or database. In this way, the application can recover gracefully even after a user selects a database for which the user has no access rights:

The lstTables_AfterUpdate event procedure is the final code block in the module behind frmSelectATable. This code block is by far the busiest and most complex one in the module. The procedure begins by saving the login settings along with the selected database and table names in Module1. The variables holding the settings are declared with the Public keyword in Module1 so that they’re readily accessible from any module in the project:

Next, the procedure opens frmShowATable in design view to persist design changes to the form. The procedure starts modifying the form by removing all controls from frmShowATable, except for three label controls and any command button control. This step essentially removes labels and text boxes for a previously selected table. However, the step retains any controls not associated with the column names and values for a selected table:

The procedure then uses the settings on frmSelectATable to specify arguments for ADO connection and recordset objects that point at the selected database and table on frmSelectATable. The recordset, rst1, makes available the column names and values from the selected table. Before using rst1 in the current procedure, the application saves rst1 in a project-level variable (rstn) declared in Module1:

The variable rst1 is used in the current module to add a pair of label and text box controls to frmShowATable for each column in the selected table from lstTables. The CreateControl method for the Access Application object accomplishes these tasks within a For…Each loop, passing through each field within rst1. The procedure assigns a Name property for each label and text box control that corresponds to the name of the matching table column. The procedure additionally defines a caption for each created label that reflects its matching column name. The loop also spaces the control evenly down the form from a starting displacement from the top of the frmShowATable. After iterating through all the rst1 fields, the procedure saves the design changes and opens frmShowATable in form view:

Displaying a table

Clicking Shippers in the lstTables control shown in Figure 1 opens frmShowATable with data from the first row from the Shippers table displayed (see Figure 2). The three text boxes with their matching labels were created by the lstTables_AfterUpdate procedure in the module behind frmSelectATable. Other controls on frmShowATable, such as the three labels across the top and five command buttons on the right side of the form, are built into the layout for frmShowATable.

Figure 2

The contents of the labels across the top of frmShowATable change depending on the selections a user makes in frmSelectATable. The top left label displays the selected database, and the top right label shows the selected table. The middle label just below the top two labels provides feedback about the login settings. The data in Figure 2 reflects a Windows login, which is consistent with the settings in Figure 1. If the user clicked the SQL Server option button in Figure 1 before clicking Shippers in lstTables, then the middle label would show False for the Windows Login. It would also show the name of the SQL Server login (in this instance, sa0503).

The form in Figure 2 isn’t bound to a data source. Therefore, I have to supply the code to perform basic record navigation and update functions. Users can invoke these functions through the command buttons on the right side of the form. More generally, you can supply as much functionality to this type of explorer form as you wish. For example, this implementation shows just one row at a time, but you can program the display of all rows, or you can make buttons available for inserting and deleting rows.

The code behind frmShowATable divides into three blocks. The initial block is the Form_Load procedure and a utility procedure, PopulateTextBoxes, that I also use elsewhere in the application. The Form_Load procedure has two primary tasks, but it also performs some minor formatting chores for controlling the look of the form (such as whether to show record selectors in form view). The first main task is to populate the three labels across the top of frmShowATable. The procedure recovers values for the captions of these labels from the variables declared with the Public keyword in Module1. The lstTables_AfterUpdate procedure from the module behind frmSelectATable initially assigns values to these variables:

The second main task is to populate the text boxes with column values from the first row in the selected table. Recall that lstTables_AfterUpdate creates a recordset with the column values from the selected table and saves a variable (rstn in Module1) referencing the recordset. The PopulateTextBoxes procedure in the module behind frmShowATable iterates through the fields of the currently selected row in the recordset. When called from the Form_Load event procedure, the recordset’s current row will be its first row. I’ve also included the formatting code:

The second block of code manages navigation through the rstn recordset. This code relies on the click event procedures for the first four command buttons on frmShowATable. All of these event procedures perform two basic functions. First, they invoke one of the move methods for an ADO recordset object. For example, the cmdFirst_Click procedure starts by invoking the MoveFirst method for the rstn recordset variable in Module1. Then, the procedure calls the PopulateTextBoxes procedure to refill the text boxes with the new current record. The cmdNext_Click and cmdPrevious_Click procedures have a little extra logic that detects a move past the last record or before the first record and compensates accordingly. The cmdNext_Click procedure checks the EOF property of the current record after invoking the MoveNext method. If the property is True, then the procedure simply moves back one record. Otherwise, the procedure invokes the PopulateTextBoxes procedure to show the new current record:

Updating data

The final block of code for the application is the click event procedure for the Commit button, cmdCommit, on frmShowATable. This procedure transfers values from the text boxes on the form to the SQL Server table whose values appear on the form. The form contains only string data types in its text boxes. However, the data types for the values in a SQL Server database may have many different data types (for instance, money, smallint, and int). As a result, it’s necessary to convert the text values in the text boxes to their matching data types in the SQL Server table.

ADO maps SQL Server data types to corresponding ADO data types that it uses to represent values in a recordset. Since the data passes from the text box to the recordset and finally to the database, the application must apply an appropriate transformation to convert the string contents in a text box to an appropriate ADO data type that ADO will ultimately map to a SQL Server data type in the database. The correct transformation varies, depending on the target SQL Server data type. To convert a string value to a money data type, use the CCur function. The procedure applies the CCur function to a text box value only when the corresponding recordset field’s ADO data type is adCurrency. The sample application includes transformation functions for common ADO data types that align with SQL Server data types. A string data type corresponds to an ADO adVarChar data type. In this case, no transformation is necessary. After all, both the adVarChar ADO data type and the text box represent string data. The On Error Resume Next statement at the top of the procedure causes the application to ignore inappropriate text box entries, such as letter characters in the text box for a numeric field:

Some commented code at the top of the cmdCommit_Click procedure includes two Debug.Print statements that I found helpful in diagnosing data types and transformation. You can use these statements to add additional transformations that your extensions to this sample may require.

The last line in the click event procedure for cmdCommit invokes the Update method for the rstn recordset. This method transfers the updated values in the local recordset to the SQL Server database. If your application allows multiple users to concurrently change the same record, you may require additional logic to process concurrency conflicts.

In summary

This article demonstrated how to construct a simple explorer for SQL Server tables from Access database files or Access projects. The explorer utilizes two forms. The first form enables a user to connect to the local default SQL Server instance and to select a table from any database on the server. The second form allows a user to browse and edit the column values for any row in the selected table. The sample application reveals how to tightly integrate SQL-DMO, ADO, and Access form design techniques.

Your download file is called  308dobson.ZIP in the folder 2003-08

Other Pages That You Might Want To Read

Client-Server Utilities

Access Answers: Client/Server Issues

Client/Server Development with Access

Migrating Access (and Outlook Data) to SQL Server

Improving on VBA with Transact-SQL



This entry was posted in Old Material. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.