Tablas Relacionadas y Valores Poco Explícitos

Solución Aplicando Clases de Reconocimiento de Datos y Algo Más

Por Harvey Triana, Mayo 1999

«Tablas Relacionadas y Valores Poco Explícitos», es el titulo en la documentación de Visual Basic para referirse a las listas relacionadas, que algunos llaman Picklists (antiguo término de DBASE). En todo caso este es un viejo problema de las Bases de Datos Relacionales y que aún los programadores Visual Basic de debaten por una solución eficaz. En años pasados, programe un sistema basado en controles ActiveX, que hoy día usan algunas de mis aplicaciones de gestión escritas con Visual Basic 5. Omití el uso de los controles Data Bound List (DBCombo y DBList) de Visual Basic 5, ya que estos controles requieren de un control Data adicional para trabajar, y en resumen, el consumo de recursos es muy alto (formularios con 20 o más listas enlazadas a controles DBCombo son demasiado cargados). Mi técnica con controles ActiveX se baso en las propiedades de los objetos Field y de los DataBindings de controles estándar. Si bien esta técnica es más eficiente que usar controles Data Bound List y sus controles Data, aun es lenta (en formularios grandes), y peor aun, es terriblemente complicado dar mantenimiento (por ejemplo alguna modificación en la estructura o relaciones de la base de datos es un lío). Visual Basic 6.0 entrega una herramienta realmente eficaz, las clases de reconocimientos de datos. En este articulo expongo la manera de aplicar la tecnología ADO para una solución optima a este problema.

Perspectiva Actual

Visual Basic alcanzo una gran evolución en el tratamiento de los datos desde Visual Basic 5.0 hasta el 6.0, especialmente marcada por el advenimiento de ADO. Sin embargo no todo para hay, Visual Basic 6.0 trae un arsenal impresionante de facilidades para programar contra datos a un nivel muy elevado. Por ejemplo los objetos DataEnvironment permiten crear una solución sencilla casi de inmediato y con mucha flexibilidad. No obstante los DataEnvironment, o alguno de los asistentes de Visual Basic no resuelven el viejo problema de las tablas relacionadas y claves externas. Si bien un novato, o especialista en manejar asistentes, podría decepcionarse al llegar a este punto.

La solución para un formulario de datos es relativamente sencilla. Puede encontrar una descripción en la librería MSDN bajo el titulo: «Vinculación de dos tablas mediante los controles DataCombo y DataList». Retomaré este escrito e innovaré en lo siguiente: (1) Dar un origen de datos a las listas con Clases de Reconocimiento de Datos y, (2)  Daré una solución potente para una grilla de datos (esta es la parte complicada). Primero que todo retomaré la definición del problema tal y como la describen en la documentación de Visual Basic.

Descripción del Problema de las Listas Relacionadas

Esto es textual de la librería MSDN bajo el titulo: «Vinculación de dos tablas mediante los controles DataCombo y DataList»:

Tablas relacionales y valores "poco explícitos"

En una base de datos relacional, la información que se utiliza repetidamente no se almacena en su totalidad en múltiples lugares, sino que el grueso de la información se almacena en un conjunto de registros compuesto por muchos campos; entre estos campos está el campo Id. que identifica de manera individualizada a cada registro. Por ejemplo, la base de datos Biblio, que se suministra con Visual Basic almacena los nombres de varias empresas editoriales en una tabla llamada "Publishers". La tabla contiene muchos campos, como dirección, ciudad, código postal y número de teléfono. Pero para hacerlo más sencillo, considere los campos Name y PubID como los dos campos esenciales de la tabla. El campo Name almacena el nombre de un editor, mientras que el campo PubID almacena un valor comparativamente "poco explícitos", como un número de código. Pero este valor "poco explícitos" es más importante ya que identifica individualmente al editor, y sirve de vínculo para todo el conjunto de registros. Y es ese valor el que está almacenado en múltiples conjuntos de registros de una segunda tabla.

La segunda tabla se llama "Titles", y cada conjunto de registros contiene información, como título, año de publicación e ISBN. Incluido entre los campos, hay uno llamado "PubID". Este campo tiene exactamente el mismo nombre que su campo correspondiente en la tabla Publishers ya que almacena el valor que vincula el título a un editor determinado.

Este eficaz esquema presenta un pequeño problema: Dada una aplicación para bases de datos que permita a los usuarios insertar nuevos títulos, el usuario debe, de algún modo, introducir valores enteros que identifiquen al editor (Publisher). Esto está bien si el usuario ha memorizado el Id. único de cada editor, pero para la gente sería más fácil ver el nombre del editor y hacer que la aplicación almacene el valor asociado en la base de datos. Los controles DataList y DataCombo resuelven este problema fácilmente.

En la gráfica anterior vemos los dos orígenes y los tres campos. Aquí, la tabla Titles (Libros) es el origen a actualizar, la tabla Publishers (Editoriales) es el origen que suministra los valores explícitos para actualizar. El campo PubID de la tabla Titles es el campo a actualizar a partir de la lista y comúnmente se denomina Clave Externa. El campo PubID de la tabla Publishers es la clave en la relación. El tercer campo, viene a ser cualquier campo, o combinación, de la tabla Publishers y es el que que se mostrará al usuario, por ejemplo es claro que mostraríamos el campo Name (nombre de la Editorial).

NOTA. Existe una relación en estas dos tablas. Un registro de Publishers tiene varios en Titles, en otras palabras, una Editorial representa a varios Libros. En general, esta relación no es un requisito obligado para implementar listas relacionadas. Un objeto Relation de Access (tal y como se muestra en la gráfica) puede afectar en cierto modo la construcción de consultas de varias tablas. En general la consulta resultante debe tener como origen principal de datos la tabla que editaremos y no la tabla de la lista.

Los controles DataCombo y DataList tienen la capacidad de acceso a dos tablas diferentes y vincular datos de la primera tabla a un campo de la segunda. Esto se lleva a cabo mediante dos orígenes de datos, ya sea un control de datos ADO,  un entorno de datos, o una clase de reconocimiento de datos.

El problema con un control DataCombo se solucionaría asi desde la ventana siseño:

Propiedad	Valor
DataSource	Origen a la Tabla Titles (un ADODC o un objeto de un DataEnvironment)
DataField	PubID
RowSource	Origen a la Tabla Publishers (un ADODC o un objeto de un DataEnvironment)
BoundColumn	PubID
ListField	Name

Si empleamos una clase de reconocimiento de datos como origen de datos para el DataCombo en vez de un control de datos ADO, usaremos una cantidad menor de recursos y una lectura más rápida. Sin embargo esto se hace con código.

Finalmente, el formulario del ejemplo expuesto anteriormente puede lucir así:

Cuando el usuario selecciona un ítem de la lista Publishers, se actualiza el campo PubID de la tabla Titles (clave externa). - Cabe mencionar que esto es solo un ejemplo, en una aplicación real mostraríamos todos los campos (exceptuando claves) de la tabla Titles, y cada lista relacionada que requiera.

Este diseño esta bien para una gestión sencilla. Ahora, la solución empleando una clase de reconocimiento de datos facilitará esta implementación de manera sorprendente. Además, será reutilizable para cada formulario que vaya a crear. El objetivo es poder crear tantas listas como se requieran en una linea de código por cada lista, y usar el minimo posible de recursos. Además, y esto es de gran importancia, podemos facilitar un componente eficaz para que haga parte de la capa intermedia en soluciones para el Web.

Solución para un Formulario Usando Clases de Reconocimientos de Datos

Primero que todo, no necesitará escribir una Clase para cada Lista que vaya a emplear. Basta una clase y una colección que gestione las instancias.

Crearemos una Jerarquía de Clases en donde disponemos una clase padre: RowSourceCollection y las sub-clases RowSource. Puede que Ud. sea un experto, pero guiare la solución para cualquier nivel de programador.

(1) Un proyecto EXE Estándar (posteriormente podemos aislar las partes y crear un componente DLL).

Seleccionar Referencias del menú proyecto para agregar una referencia a la Biblioteca Microsoft ActiveX Data Objetos 2.0 (no emplee la versión 2.1 si va utilizar el control ADODC en la solución, ya que hay algunas incompatibilidades). Agregamos referencias a los controles Microsoft DataList Control 6.0 y Microsoft ADO Data Control (OLEDB).

(2) Una Jerarquía de Clases. Empezamos por el menú Complementos, opción Utilidad Generador de Clases (si no tiene registrado el complemento, ubíquelo en Administrador de Complementos). Botón: Agregar Nueva Colección, Nombre: RowSourceCollection, Luego, usamos el Frame: Colección de nueva clase, Nombre de Nueva Clase: RowSource. Finalmente Aceptar y Salir. Acepte actualizar el Proyecto. La siguiente imagen muestra como se debe ver una jerarquía de clases (resaltado con una línea azul):

(3) Bien, ya esta creada la Jerarquía de clase. Ahora adicionaremos el código. Antes, vamos al módulo de clase: RowSource para fijar esta propiedad: DataSourceBehavior: vbDataSource (esto convierte la clase en origen de datos). Reemplace todo el código de este módulo por el siguiente:

 
'//ROWSOURCE
'//Harvey T., 1999
'//Clase origen de datos para listas enlazadas
Option Explicit
 
Private rs As ADODB.Recordset
 
Private Sub Class_GetDataMember(DataMember As String, Data As Object)
    Set Data = rs
End Sub
 
Public Sub Settings( _
       sActiveConnection As String, _
       sSource As String, _
       sDataMember As String _
    )
    DataMembers.Add sDataMember
 
    Set rs = New ADODB.Recordset
    With rs
       .ActiveConnection = sActiveConnection
       .Source = sSource
       .CursorType = adOpenForwardOnly
       .CursorLocation = adUseClient
       .Open
    End With
End Sub
 
Public Property Get RecordCount() As Long
    RecordCount = rs.RecordCount
End Property
 
Private Sub Class_Terminate()
    Set rs = Nothing
End Sub

Módulo de clase: RowSourceCollection. Reemplace todo el código por el siguiente:

 
'//ROWSOURCECOLLECTION
'//Gestiona las instancias de objetos RowSource
'//Harvey T., 1999
Option Explicit
 
Private m_Col As Collection
 
Public Function Add( _
       ctlList As Control, _
       sActiveConnection As String, _
       sBoundColumn As String, _
       sListField As String, _
       sRowTable As String _
     ) As RowSource
 
    Dim rws As RowSource
    Dim SQL As String
 
    On Error GoTo SubErr
 
 '//Crea la consulta mínima de la lista
 SQL = "SELECT [" & sBoundColumn & "],[" & sListField & "] " & _
          "FROM [" & sRowTable & "] " & _
          "ORDER BY [" & sListField & "];"
 
    Set rws = New RowSource
    '//sDataMember será sBoundColumn
    rws.Settings sActiveConnection, SQL, sBoundColumn
 '//sDataMember es la clave del ítem
 m_Col.Add rws, sBoundColumn
 
    With ctlList
       .RowMember = sBoundColumn
       .BoundColumn = sBoundColumn
       .ListField = sListField
       .Tag = CStr(rws.RecordCount)
        Set .RowSource = rws
    End With
 
    Set Add = rws
    Set rws = Nothing
    Exit Function
 
SubErr:
    MsgBox "Cannot create RowSource object for " & sBoundColumn & _
            vbCrLf & vbCrLf & Err.Description
End Function
 
Public Property Get Item(vntIndexKey As Variant) As RowSource
    Set Item = m_Col(vntIndexKey)
End Property
 
Public Property Get Count() As Long
    Count = m_Col.Count
End Property
 
Public Sub Remove(vntIndexKey As Variant)
    m_Col.Remove vntIndexKey
End Sub
 
Public Property Get NewEnum() As IUnknown
    Set NewEnum = m_Col.[_NewEnum]
End Property
 
Private Sub Class_Initialize()
    Set m_Col = New Collection
End Sub
 
Private Sub Class_Terminate()
    Set m_Col = Nothing
End Sub

(4) Dibujamos y configuramos el formulario. El formulario de la primara figura de este articulo sirve de guía. Para usar el ADODC le sugiero los siguientes pasos (palabras entre corchetes cuadrados significan botones):

• Clic-Derecho sobre el control ADODC
  Propiedades
  Usar Cadena de Conexión
  [Generar]
  OLE DB Provider(s) = MS Jet 3.51OLE DB Provider;
  (NOTA. borre manualmente la línea: Persist Security Info=False; Esta línea me ha dado problemas con las consultas, más no con los Queries. - Debe tratarse de algún Bug)
  [Next]
  Select database name: [...], buscar la ubicación de Biblio.MDB
  [Test Connection]
  [Aceptar]
  Ficha: Origen de Datos
  adCommandText
  SQL:
  SELECT * FROM Titles
  [Aceptar].
  Ventana Propiedades, señalar el control ADODC:
  Nombre=adcTitles
  Align=2-vbAlignBottom
  Caption=Titles
  Mode=3-adModeReadWrite

Ya esta configurado el control de datos ADO. Ahora basta colocar los controles que muestra la gráfica y dar sus propiedades de datos (similar a lo corriente con el control Data de DAO). TextBox: Name=txt, Index=0, DataSource=adcTitles, DataField=Title. Control DataCombo: Name=acb, Index=0, DataSource=adcTitles, DataField=PubID, Style=2-dbcDropdownList.

La propiedad Estilo del DataCombo se fija a lista de solo lectura (dbcDropdownList), dado que la lista Publisher se muestra como una vista de la clave externa en nombres explicitos y no es editable. Si se permite editar la lista se producen errores o se modifica la tabla de referencia afectando los datos ya ingresados en la misma. Si desea que se editen o agreguen ítems a la lista lo haremos desde un comando externo a un formulario destinado para esto. El caso lo mostraré más adelante.

(5) Por último copiamos el código del formulario. Pegue esta sección:

'//LISTAS ENLAZADAS
'//Ejemplo del Articulo:
'//Tablas Relacionadas y Valores Poco Explícitos
'//FormADOPickList
'//Harvey T., 1999
Option Explicit
 
Private rsc As RowSourceCollection
 
Private Sub Form_Load()
    Dim s As String
 
    '//Instancia del objeto RowSourceCollection
    Set rsc = New RowSourceCollection
 
    '//Adicionando una Lista
    s = adcTitles.Recordset.ActiveConnection
    Call rsc.Add(acb(0), s, "PubID", "Name", "Publishers")
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
    Set rsc = Nothing
End Sub
 
Private Sub adcTitles_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
 '//Muestra Record i de n
 With adcTitles.Recordset
        If Not (.EOF Or .BOF) Then
           adcTitles.Caption = "Title " & .AbsolutePosition & " of  " & .RecordCount
        End If
    End With
End Sub

Como puede observar, el código que gestiona la(s) lista es muy poco. El evento MoveComplete se escribió para mostrar la posición del registro y el número de registros en la vista de datos.

Alcances de la Solución

Este simple ejemplo muestra una solución muy flexible al problema. Puede anexar tantas listas como lo requiera el formulario, simplemente agregando ítems a objeto RowSourceCollection de la siguiente línea:

rsc.Add NombreDeDataCombo, ActivateConnnetion, ClaveExterna, CampoEnLista, OrigenDeLista

El ejemplo también enmarca un camino a soluciones más complejas, por ejemplo generación de formularios de datos en tiempo de ejecución. En este caso el problema principal es obtener los datos para los parámetros del método Add de objeto RowSourceCollection. En particular, Access suministra una Ficha "Búsqueda" cuando diseñamos la estructura de una tabla. En la ficha búsqueda se fijan unos parámetros que hacen la implementación de listas relacionas automática en cualquier vista de datos con Access. Hacer esto con Visual Basic es viable dado lo expuesto en este articulo, solo tendríamos que leer esas propiedades del la interfaz requerida suministrada por el Proveedor. Sin embargo, las propiedades de objetos Field obtenida con ADO para BDs Access no suministran la misma interfaz Field de Access, y esto si que es un problema. Se podría intentar algo obteniendo Esquemas con ADO, pero esto complica lo que era una solución sencilla. Yo opte por generar una tabla virtual que contiene la información de todas las propiedades de listas enlazadas. Esta técnica me permite universalizar la solución, sin importar el proveedor de datos (en estos momentos la aplica una solución contra Oracle y otra contra Access). Esta extensión de la solución se aleja de los propósitos de este articulo.

Solución para un DataGrid usando Clases de Reconocimientos de Datos

Para completar la aplicabilidad de la solución solo falta ponerla a trabajar para un grilla de datos. La siguiente gráfica muestra la solución en producción:

Este imagen muestra la aplicabilidad usando una Base de Datos que no es Biblio. Los botones de la barra tienen las siguiente funciones: (1) Actualizar la celda con el ítem seleccionado de la lista, (2) Editar la lista, y (3) Actualizar la lista desde su origen (Refresh).

El principal problema al utilizar una grilla de datos como DataGrid, en contraste con formularios simples, es que se debe mostrar el campo explícito de la lista relacionada en una columna, mientras que debemos ocultar la clave externa. Para mostrar las columnas con los valores explícitos de la lista se debe construir una Consulta que contenga las tablas involucradas, una consulta que usa la cláusula JOINT. Por supuesto, esto reviste ciertos conocimientos de SQL, y la consulta suele variar; Por ejemplo cuando se usan relaciones con objetos Relation de Access. La consulta para el ejemplo Titles/Publishers de Biblio es la siguiente:

SELECT Titles.Title, Titles.PubID, Publishers.Name
FROM Titles LEFT JOIN Publishers ON Titles.PubID = Publishers.PubID;

La cláusula LEFT JOIN impone a la tabla Titles como origen de datos. En general, para construir este tipo de consultas es conveniente usar las QBE (Query By Example) de Access o de cualquier DBMS. Visual Studio 6.0 también trae un constructor de consultas aparte.

Por favor, mire la imagen anterior. La lista no es una lista convencional colgada a la celda de la grilla sino un formulario. La razón por la solución Lista-Formulario tiene notables ventajas:

· Generalmente se desea editar la lista para arreglar o agregar ítems. Se dispone un comando para que un usuario con los permisos pertinentes pueda hacerlo.

· Es deseable un comando para actualizar la lista. Esto es importante cuando se trabaja en Red. Las listas por rendimiento se conservan en memoria estática, es decir, se usa un cursor estático de solo lectura para leerla y desplegarla. Así, si otro usuario hace modificaciones a la lista, esta se puede actualizar sin tener que cerrar la instancia de carga de datos.

· Permite una mayor visibilidad y navegación por los ítems. Además, es más estética (apreciación subjetiva).

· Puedo agregar más comandos al formulario de lista. Todos los formularios de datos se verán beneficiados de los cambios. Esta solución empaqueta su código en un componente.

· Los controles necesarios para gestionar la lista son independientes del formulario que contiene la grilla.

· Puedo usar la lista en otro contexto que no sea una grilla de datos.

· En teoría, se podrían hacer reutilizables las instancias de las listas, es decir los usuarios A, B y C ven la misma lista. Para esto requiere un componente fuera de proceso, clases basadas en conectores, cursores del lado del servidor, y una buena dosis de código. Esto sería deseable en una solución para el Web (el ejemplo que suministro no considera esto).

Al aislar la implementación de listas, deja en libertad al programador de hacer muchas variantes sin afectar la interfaz del usuario. En virtud de esto, en la solución aplicada a grillas no use un DataList, sino un ListBox estándar. El control ListBox estándar es más ligero que DataList y, de acuerdo a la capacidad programada, hará el mismo trabajo. El componente también optimiza para que la cargue de la lista a petición, es decir la primera vez que un usuario da clic en el botón de la celda, la lista se llena y despliega, subsecuentes llamadas solo despliegan la lista. Esto permite una carga más ágil de formulario. Tambien gestione la reutilización del objeto Connection de ADO al pasarlo por referencia.

El código de ejemplo también incluye la clase DataGridFormat. En esta clase empaqueta código necesario para dar algo de formato a la grilla. En realidad el control DataGrid (y su antecesor DBGrid) son pobres en presentación y requieren de mucho código para dar una interfaz mejorada al usuario.

Existen casos en donde la lista no actualiza una clave externa, es decir, la lista solo facilita al usuario la elección de datos. En este caso, pase el parámetro BoundColumn de RowSourceCollection.Add como sarta vacía (vbNullString).

El código de listas enlazadas para la grilla no lo expondré en este articulo, pues va más allá de la simple implementación de listas y es algo extenso de explicar. Sin embargo los principios son los mismos aplicados a partir de la técnica que expuse. En la descarga de archivos encontrará el código aplicado al ejemplo Titles / Publishers.

En realidad estos módulos no representan una solución cien por ciento completa. Es un buen avance y cumple con lo expuesto en este articulo. A veces tenemos que adaptar los componentes a los requerimientos de un aplicativo empresarial lo que los hará complejos y poco didácticos. Por esta razón es conveniente simplificar para que otros se puedan beneficiar de las ideas.

---

GridPickLists.zip (11 kb). Abrir el grupo de proyectos grpDataGridBrowser.vbg. Luego abrir el módulo del formulario frmBiblioSample.frm, para especificar la trayectoria de la base de datos Biblio.mdb en su PC, en la línea:

cnn.Open "Provider=Microsoft.Jet.OLEDB.3.51;" + _
"Data Source=SuTrayectoria/Biblio.mdb"