An Access Explorer for SQL Server Tables

<< Click to Display Table of Contents >>

Navigation:  Other Topics > SQL Server and Access >

An Access Explorer for SQL Server Tables

Rick Dobson        

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.

200308_rd1
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.

 

200308_rd2
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 file SA2003-08down.zip

This can be purchased with all the other downloads on this page

 

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