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:
Dim srv1 As SQLDMO.SQLServer Private Sub Form_Load() Dim dbs1 As SQLDMO.Database 'Login to local SQL Server with 'integrated security Set srv1 = New SQLDMO.SQLServer srv1.LoginSecure = True srv1.Connect 'Assign Value List to the RowSourceType 'property for the lstDatabases and 'lstTables controls Me.lstDatabases.RowSourceType = "Value List" Me.lstTables.RowSourceType = "Value List" 'List database names in lstDatabases control For Each dbs1 In srv1.Databases Me.lstDatabases.AddItem (dbs1.Name) Next dbs1 'Clear any entries in Me.lstTables ClearlstTables 'Format form Me.optLogin = 1 Me.txtPassword.InputMask = "Password" Me.RecordSelectors = False Me.NavigationButtons = False Me.DividingLines = False End Sub Sub ClearlstTables() 'Iterate through items in lstTables 'to remove them Do Until Me.lstTables.ListCount = 0 lstTables.RemoveItem (0) Loop End Sub
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:
Private Sub txtLogin_AfterUpdate() 'Validate new login optLogin_AfterUpdate End Sub Private Sub txtPassword_AfterUpdate() 'Validate new password optLogin_AfterUpdate End Sub Private Sub optLogin_AfterUpdate() On Error GoTo AfterLoginUpdateTrap 'Declare and instantiate SQLServer object Set srv1 = New SQLDMO.SQLServer 'Login with Windows integrated security 'or a SQL Server login If Me.optLogin = 1 Then srv1.LoginSecure = True srv1.Connect Else srv1.Connect "(local)", txtLogin.Value, _ txtPassword.Value Me.txtLogin.SetFocus End If NormalExit: Exit Sub AfterLoginUpdateTrap: 'Display an error message MsgBox Err.Description & " Resetting to " & _ "Windows login. You can enter a " & _ "new login or password and select " & _ "a SQL Server login type to " & _ "validate them.", vbCritical, _ "AfterLoginUpdate Error Trap" 'Recover from the error Me.optLogin = 1 srv1.LoginSecure = True srv1.Connect Resume NormalExit End Sub
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:
Private Sub lstDatabases_AfterUpdate() On Error GoTo AfterDBUpdateTrap Dim tbl1 As SQLDMO.Table 'Clear any prior entry from lstTables control ClearlstTables 'List table names in lstTables control For Each tbl1 In srv1.Databases _ (lstDatabases.Value).Tables If tbl1.SystemObject = False Then Me.lstTables.AddItem (tbl1.Name) End If Next tbl1 NormalExit: Exit Sub AfterDBUpdateTrap: 'Display an error message MsgBox Err.Description & " Try changing the " & _ "login, password, or database.", vbCritical, _ "AfterDBUpdate Error Trap" Resume NormalExit End Sub
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:
Private Sub lstTables_AfterUpdate() Dim txt1 As Access.TextBox Dim lbl1 As Access.Label Dim cnn1 As New ADODB.Connection Dim rst1 As New ADODB.Recordset Dim str1 As String Dim intLeft As Integer Dim intOffset As Integer Dim intWidth As Integer Dim intHeight As Integer Dim frm1 As New Access.Form Dim strSrcName As String 'Pass form control settings to 'project-level variables in Module1 If Me.optLogin = 1 Then Module1.LoginSecure = True Module1.LoginName = DBNull Else Module1.LoginSecure = False Module1.LoginName = Me.txtLogin Module1.PasswordString = Me.txtPassword End If Module1.DBName = Me.lstDatabases.Value Module1.TableName = Me.lstTables.Value
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:
'Set reference to frmShowATable and 'open the form in Design view DoCmd.OpenForm "frmShowATable", acDesign Set frm1 = Forms("frmShowATable") 'Delete all controls, except for three built-in 'Label and CommandButton controls; save count 'and go backwards to detect all controls intControls = frm1.Controls.Count For intx = intControls - 1 To 0 Step -1 If Not (frm1.Controls(intx).Name = _ "lblDBName" Or _ frm1.Controls(intx).Name = _ "lblTableName" Or _ frm1.Controls(intx).Name = _ "lblWindowsLogin" Or _ TypeOf frm1.Controls(intx) _ Is CommandButton) Then DeleteControl frm1.Name, frm1.Controls(intx).Name End If Next
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:
'Add brackets for an internal space in 'table name If InStr(1, Me.lstTables.Value, " ") > 0 Then strSrcName = "[" & Me.lstTables.Value & "]" Else strSrcName = Me.lstTables.Value End If 'create connection and recordset for 'selected table If Module1.LoginSecure = True Then str1 = "Provider=SQLOLEDB;" str1 = str1 & "Data Source=localhost;" str1 = str1 & "Initial Catalog=" & _ Me.lstDatabases.Value & ";" str1 = str1 & "Integrated Security=SSPI" cnn1.ConnectionString = str1 Else str1 = "Provider=SQLOLEDB;" str1 = str1 & "Data Source=localhost;" str1 = str1 & "Initial Catalog=" & _ Me.lstDatabases.Value & ";" str1 = str1 & "user id=" & Me.txtLogin.Value & ";" str1 = str1 & "password=" & Me.txtPassword.Value cnn1.ConnectionString = str1 End If cnn1.Open rst1.Open strSrcName, cnn1, _ adOpenKeyset, adLockOptimistic 'Save reference to the Recordset 'object in a project-level variable Set Module1.rstn = rst1
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:
'Set displacement from top of form 'for first added control intLastTop = 400 'Settings for control dimensions and 'displacements from one another intLeft = 1440 intOffset = 72 intWidth = 1440 + 720 intHeight = 288 'Loop through fields for recordset 'based on selected table For Each fld1 In rst1.Fields Set txt1 = CreateControl(frm1.Name, acTextBox) With txt1 .Left = intLeft .Top = intLastTop + intHeight + intOffset intLastTop = .Top .Width = intWidth .Height = intHeight .Name = "txt" & fld1.Name End With Set lbl1 = CreateControl(frm1.Name, acLabel) With lbl1 .Left = intOffset .Top = intLastTop + intOffset .Width = intWidth - 720 - intOffset .Height = intHeight .Name = "lbl" & fld1.Name .Caption = fld1.Name & ":" End With Next fld1 'Save design changes to frmShowATable then 'open it in default Form view DoCmd.Close acForm, frm1.Name, acSaveYes DoCmd.OpenForm "frmShowATable" End Sub
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:
Private Sub Form_Load() 'Constants for align text in labels Const lblAlignLeft = 1 Const lblAlignCenter = 2 Const lblAlignRight = 3 'Update contents of three top Label controls with 'project-level variables declared in Module1 Me.lblDBName.Caption = _ "Database Name: " & Module1.DBName Me.lblTableName.Caption = _ "Table Name: " & Module1.TableName If Module1.LoginSecure Then Me.lblWindowsLogin.Caption = _ "Windows Login: " & _ CStr(Module1.LoginSecure) Else Me.lblWindowsLogin.Caption = _ "Windows Login: " & _ CStr(Module1.LoginSecure) & " " & _ "Login: " & Module1.LoginName End If
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:
'Populate text box controls with first 'row from the recordset Me.PopulateTextBoxes 'Format form Me.RecordSelectors = False Me.NavigationButtons = False Me.DividingLines = False Me.lblDBName.TextAlign = lblAlignLeft Me.lblTableName.TextAlign = lblAlignRight Me.lblWindowsLogin.TextAlign = lblAlignCenter End Sub And here's the PopulateTextBoxes routine that loads the data onto the form: Sub PopulateTextBoxes() Dim fld1 As ADODB.Field Dim strCtlName As String 'Populate text box controls with contents 'current row from the recordset For Each fld1 In Module1.rstn.Fields strCtlName = "txt" & fld1.Name Me.Controls(strCtlName).Value = fld1.Value Next fld1 End Sub
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:
Private Sub cmdFirst_Click() 'Move to first row and copy its contents into the form Module1.rstn.MoveFirst PopulateTextBoxes End Sub Private Sub cmdNext_Click() 'Navigate forward one row and copy contents of new 'row to form unless it has an EOF marker Module1.rstn.MoveNext If Module1.rstn.EOF Then Module1.rstn.MovePrevious Else PopulateTextBoxes End If End Sub Private Sub cmdPrevious_Click() 'Navigate backward one row and copy contents of new 'row to form unless it has an BOF marker Module1.rstn.MovePrevious If Module1.rstn.BOF Then Module1.rstn.MoveNext Else PopulateTextBoxes End If End Sub Private Sub cmdLast_Click() 'Move to last row and copy its contents into the form Module1.rstn.MoveLast PopulateTextBoxes End Sub
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:
Private Sub cmdCommit_Click() On Error Resume Next Dim fld1 As ADODB.Field Dim int1 As Integer Dim int2 As Integer 'Iterate through recordset fields and update recordset 'with transform of text box value; sample shows 'selected common transform functions for going from 'text box value to SQL Server data type int2 = Module1.rstn.Fields.Count - 1 For int1 = 0 To int2 'Remove comment markers from the two 'Debug.Print statements to get diagnostics 'that help to transform more data types 'Debug.Print Me.Controls("txt" & _ ' Module1.rstn(int1).Name).Value, _ ' Module1.rstn(int1).Value 'Debug.Print Module1.rstn(int1).Type 'No conversion function for varchar type If Module1.rstn(int1).Type = _ adVarChar Then Module1.rstn(int1).Value = _ Me.Controls("txt" & _ Module1.rstn(int1).Name).Value 'Use CCur for money data type ElseIf Module1.rstn(int1).Type = _ adCurrency Then Module1.rstn(int1).Value = _ CCur(Me.Controls("txt" & _ Module1.rstn(int1).Name).Value) 'Use CInt for smallint data type ElseIf Module1.rstn(int1).Type = _ adSmallInt Then Module1.rstn(int1).Value = _ CInt(Me.Controls("txt" & _ Module1.rstn(int1).Name).Value) 'Use CLng for int data type ElseIf Module1.rstn(int1).Type = _ adInteger Then Module1.rstn(int1).Value = _ CLng(Me.Controls("txt" & _ Module1.rstn(int1).Name).Value) 'Use CBool for bit data type ElseIf Module1.rstn(int1).Type = _ adBoolean Then Module1.rstn(int1).Value = _ CBool(Me.Controls("txt" & _ Module1.rstn(int1).Name).Value) 'Use CDate for datetime data type ElseIf Module1.rstn(int1).Type = _ adDBTimeStamp Then Module1.rstn(int1).Value = _ CDate(Me.Controls("txt" & _ Module1.rstn(int1).Name).Value) 'Use CSng for real data type ElseIf Module1.rstn(int1).Type = _ adSingle Then Module1.rstn(int1).Value = _ CSng(Me.Controls("txt" & _ Module1.rstn(int1).Name).Value) 'Use CDbl for float data type ElseIf Module1.rstn(int1).Type = _ adDouble Then Module1.rstn(int1).Value = _ CDbl(Me.Controls("txt" & _ Module1.rstn(int1).Name).Value) End If Next int1 'Copy the edited recordset values back to 'the SQL Server database Module1.rstn.Update End Sub
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
Access Answers: Client/Server Issues
Client/Server Development with Access
Migrating Access (and Outlook Data) to SQL Server
Improving on VBA with Transact-SQL