Varios de nuestros visistantes nos consultan sobre Visual Basic y el uso de Base de Datos, por lo que hemos pensado ir desarrollando algunos ejercicios que muestren distintas formas de abrir, navegar y modificar una Base existente, diseñar y crear una nueva y establecer consultas a través de SQL.

Para comenzar les sugiero leer el siguiente Documento, donde se define una Base de datos.

If Data1.Recordset.EOF Then ' Si es el último muestra un mensaje

MsgBox "Este es el Último Registro.", , "Registros"

Data1.Recordset.MoveLast ' Se ubica en el último.

End If

EndPrivate Sub Command4_Click()

Data1.Recordset.MoveLast ' Se ubica en el último.

End Sub

Las propiedades BOF y EOF indican el comienzo del archivo: begin of file y el final del archivo: end of file.

El objeto Recordset que sigue al objeto data identifica al objeto: conjunto de registros.El archivo comprimido puede bajarse de Aquí.

En este ejercicio vamos a seguir avanzando en cuanto a las posibilidades de manipular los contenidos de la Base de datos. Vamos a agregar y eliminar registros y hacer búsquedas.

El formulario contiene los siguientes controles:

Private Sub Command4_Click()

Data1.Recordset.FindNext "City=" & "'" & buscar & "'"

End Sub

El método AddNew nos permite agregar un nuevo registro, y la actualización la podemos hacer a través de botón grabar o moviendo el puntero al pasar al siguiente registro. Para eliminar un registro lo hacemos con el Método Delete, previa confirmación a través de un MsgBox de la misma. Para que la base se mantenga actualizada en el procedimiento Activate del formulario incluimos:

Private Sub Form_Activate()

Data1.UpdateRecord ' esto actualiza la data

End Sub

Y para el procedimiento Buscar abrimos un InputBox que guarde la cadena a buscar, en este caso una ciudad por ejemplo: New York o Buenos Aires y con el método FindFirst (Buscar primero) y el campo establecido en este caso City (Ciudad) establece los criterios de la búsqueda. Luego con FindNext establece el criterio de la segunda búsqueda. El ejercicio completo puede bajarse de Aquí.

Le decimos que seleccione de la tabla Authors todos los registros por eso usamos el asterisco (*) y que ordene el campo Author en forma ascendente, esto podría ser DESC o sea descendente.

Muchos amigos nos preguntan sobre Crystal Reports. Esta es una aplicación generadora de Informes. Estos informes se diseñan a partir de una Base de datos y la aplicación nos permite añadir un encabezado, fechas, textos, pie de páginas. etc. O hacer en tiempo de ejecución a partir de un programa de Visual Basic una consulta SQL y luego usando el control Crystal Report crear un Informe y darle salida por pantalla o impresora. Como vemos son dos cosas distintas aunque en el fondo cumplen la misma función que es generar un Informe. tenemos la aplicación Crystal Reports y el control Crystal Report que debemos traer de componentes, para incluirlo en la Caja de herramientas.

Primero veremos la aplicación Crystal Reports, vamos a Inicio, programas y dentro de Microsoft Visual Basic selecionamos Crystal reports, una vez dentro de la aplicación, elegimos File o Archivo, nuevo report o nuevo Informe y cuando nos pregunte qué Base de datos vamos a utilizar le decimos Biblio.mdb. Entonces veremos una pantalla similar a esta:

La pantalla en tiempo de diseño la tenemos dividida en el encabezado, los detalles donde insertamos los campos de la tabla con la que vamos a trabajar y el pie de página. Investiguen y prueben las distintas opciones haciendo uso de la Ayuda. Luego que seleccionamos los campos Au_ID y Author grabamos el archivo y cerramos la aplicación.

El código para actualizar la grilla es :

Private Sub Command1_Click()

Data1.RecordSource = "Select * from authors Where Au_ID = " & Text1.Text

Data1.Refresh

DBGrid1.Refresh

End Sub

Y para generar el Informe:

Private Sub Command2_Click()

CrystalReport1.Action = 1

End Sub

El valor 1 de Action le da salida por pantalla luego, haciendo click en el icono de la impresora podemos darle salida por la misma.

El código completo pueden bajarlo de Aquí.

Private Sub Form_Load()

'La grilla debe estar conectada a la data 1 Data1.DatabaseName = App.Path & "\DBase.mdb" Data1.RecordSource = "Select * from DBorder " Data2.DatabaseName = App.Path & "\DBase.mdb" Data2.RecordSource = "SELECT sum(OrderAmt) As total from DBOrder "

Data2.Refresh Text1.Text = Data2.Recordset!total

End Sub

Una vez cargado el programa ejecuta las consultas SQL y nos muestra la suma total del precio de los productos. Suma todos los campos de la tabla DBOrder y los carga en total, luego le damos salida a este valor en un control TextBox.

El codigo completo puede bajarse de Aquí.

Cada caja de texto de comunica con el control Data1 y cada Datafield se refiere a : CountofAutor, MinofYear, Maxofyear y AvgofYear. Y la consulta SQL esta hecha directamente en la propiedad RecordSource del control Data1, cuya sintáxis es:

SELECT Count(*) AS CountofAuthor, Avg([Year Born]) AS [AvgogYear Born], Min ([Year Born])AS [MinofYear], Max ([Year Born]) AS [Maxofyear] FROM Authors

Esto selecciona todos los registros, volcando el valor a CountofAuthor, luego la media de los años de nacimiento de los autores, guardándola en el alias AvgogAuthor como variable y así con los otros valores.

No olviden cambiar el camino o Path del ejercicio original al cargarlo en sus máquinas. La base usada es Biblio.mdb.

El codigo completo puede bajarse de Aquí.

Y los controles de texto: Text1 (Título) en su propiedad DataSource con la data1, Datafield con Title; el Text2 (Editor) con la Data2 y el Datafield: Name; el Text3 (Editor) con la Data1 y el datafield: Year publisher; el Text4 (Autor) con la Data4 y el datafield Author; el Text5 con la Data1 y el datafield: Subject y el Text6 (Comentarios) con la Data1 y el datafield Comments. El codigo correspondiente es:

Option Explicit

'El RecordType es Table

Private Sub Data1_Reposition()

'Con Seek busca los campos correspondientes.Si estos campos están vaciós aparecerá el asterisco.

Data2.Recordset.Seek "=", Data1.Recordset.Fields("PubID")

If Data2.Recordset.NoMatch Then Text4.Text = "***"

Data3.Recordset.Seek "=", Data1.Recordset.Fields("ISBN")

If Data3.Recordset.NoMatch Then Text2.Text = "***"

Exit Sub

Data4.Recordset.Seek "=", Data3.Recordset.Fields("AU_ID")

End Sub

Private Sub Form_Load()

'refresca las tablas o sea las actualiza

Data2.Refresh

Data3.Refresh

Data4.Refresh

'establece cuales son las llaves para indexarlas.

Data2.Recordset.Index = "PrimaryKey"

Data3.Recordset.Index = "ISBN"

Data4.Recordset.Index = "PrimaryKey"

End Sub

El archivo completo del ejercicio puede bajarse de Aquí.

El código es el siguiente:

Option Explicit

Private Sub Command1_Click()

On Error GoTo nobase

CommonDialog1.CancelError = True

CommonDialog1.Filter = "Database|*.mdb"

CommonDialog1.ShowOpen

Data1.DatabaseName = CommonDialog1.FileNameaqui se enlaza a una 'Base de Datos.

Data1.Refresh

If Err = 0 Then

Text1.Text = CommonDialog1.FileName 'aqui se muestra el camino de la 'base de datos.

Else

MsgBox Err.Description

End If

nobase: On Error GoTo 0

End Sub

Private Sub

Command2_Click()

On Error GoTo sqlerror

Data1.RecordSource = Text2 'aqui se vuelca la consulta SQL

Data1.Refresh

Exit Sub

sqlerror: MsgBox Err.Description

End Sub

Como vemos se incluyen alguna rutinas para controlar posibles erroes.

El código completo puede bajarse de AQUÍ.

Ahora me gustaría mostrarles algunos ejemplos posibles de consultas SQL usando la declaración: SELECT, y que ustedes prueben estas consultas en el Visor de SQL. Por lo general a Select la usamos con Where que nos permite establecer una condición en la selección y también en esta condición usamos cierta cláusulas como:

= Igual

> Mayor que

< Menor que

<> Distinto de

y otros. Supongamos que tenemos una Tabla con información sobre los empleados de una companía, cuyos campos son: Nombre, Apellido, ID, Ciudad, Edad y País. La tabla pueden bajarla de AQUÍ o hacerla ustedes mismos.Ejemplo:

Select Nombre, Apellido from EmpInfo where Nombre = "Eduardo";

Select Nombre, Apellido, Ciudad from EmpInfo;

Select Apellido, Ciudad, Edad from EmpInfo where Edad > 30;

Select Nombre, Apellido, Ciudad from EmpInfo where Ciudad <> 'Buenos Aires' ;

Como veremos luego de entrar la consulta a mano al apretar el boton Ejecutar SQL se despliegan los resultados de la consulta en la grilla.

Para ejecutar estas consultas SQL, pueden usar el visor del ejercicio anterior, o el Administrador Visual de datos de Visual basic, abrir la Base Books6.mdb y tipear las consultas en la ventana de código SQL y ejecutarlas. La base de datos Books6 pueden bajarla de Aquí.

El SQL del JetDataManager soporta el uso de operadores como: BETWEEN, IN y LIKE. Ejemplos:

SELECT PubID, Name, StateProv, City FROM Publishers WHERE PubID BETWEEN 10 AND 15

Con IN puedo hacer una lista de condiciones:

SELECT PubID, Name, City, StateProv FROM Publishers WHERE StateProv IN ('NY' , 'CA' , 'RI')

Puedo usar LIKE para obtener una vista cuyos registros cumplan con un patrón específico. Ejemplo: aquellos registros cuya columna StateProv tenga una letra I en su comienzo, o el PubID sea un valor determinado.

SELECT PubID, Name, City, StateProvFROM Publichers WHERE StateProv LIKE ' I '

SELECT PubID, Name, City, StateProv FROM Publichers WHERE PubID LIKE 10

Puedo usar WHERE para vincular tablas en un solo conjunto de Datos. la instrucción crea una vista que muestra el título de un libro y el nombre de la editorial. esto se logra con WHERE al solicitar que genere una vista donde los valores de la columna PubID de ambas confluyen.

SELECT Titles.Title, Publishers.Name FROM Publishers.Title WHERE Publishers.PubID = Titles.PubID

Se pueden combinar las versiones de WHERE para combinar tablas y limitar registros seleccionados en una sola instrucción SELECT.

SELECT Titles.PubID, Titles.Title, Publishers.Name FROM Titles, Publishers WHERE Titles.PubID = Publishers.PubID AND Publishers.PubID BETWEEN 5 AND 10

Podemos modificar el nombre que SQL le asigna a las columnas con la claúsula AS.

SELECT Titles.PubID AS CodigoPub, Titles.Title AS TituloLibro, Publishers.Name AS NombrePub, Authors.Name AS NombreAutor FROM Titles, Publishers, Authors WHERE Titles.PubID = Publishers.PubID AND Titles.AUID = Authors.AUID

Funciones SQL: Es posible usar WHERE y las funciones agregadas en un mismo SELECT, para limitar los registros a incluir en los cálculos que realizan las funciones.SELECT COUNT(UNits) AS RecuentoUnidades, AVG(Units) AS PromedioUnidades, SUM(Units) AS SumatoriaUnidades, MIN(Units) AS UnidadMinima, MAX(Units) AS UnidadMaxima FROM BookSales WHERE Qtr = 1

Uso de las funciones de Visual basic en una instrucción SELECT: Al cargar cualquier base de datos mediante el Jet en un aaplicación Visual Basic, es posible utilizar cualquier función propia del entorno como parte de una instrucción SQL. Ejemplo 1: Vista con una columna que muestra los tres primeros caracteres de una columna de la tabla base usando la función Left.

SELECT Left(Name,3), Name FROM Authors

Ejemplo 2: Combinar varias columnas en una sola dentro de una vista:

SELECT Name AS Nombre, City & ' , ' & StateProv & ' ' & Zip AS Domicilio FROM Publishers

Ejemplo 3: También pueden usarse las funciones de Visual basic como parte de la claúsula WHERE, este ejemplo devuelve aquellos registros que tienen como segundo caracter de la columna Name la letra 'a':

SELECT Name FROM Publishers WHERE Mid$(Name,2,1) = "a"

Las desventajas son la incompatibilidad con otras bases de datos como SQL Server, que no ejecuta las funciones de Visual Basic y que la velocidad del acceso al ejecutarse la consulta disminuye. Si queremos ganar en Portabilidad y Velocidad mejor no usarlas.

incompatibilidCualquier programa que accede a tablas utiliza objetos de acceso a datos, aún si lo hace a través de controles enlazados sin codificación, utiliza estos objetos de acceso a datos. El Modelo de Objetos de Acceso a datos puede ser:

DAO 3.51 (versión actual de VB6)

DAO 2.5/3.51(compatible con versiones anteriores)

Y otros Modelos como: RDO (Remote data Object) y ADO (Objetos Activos de Acceso a Datos).

El objeto fundamental en los programas basados en DAO es el Recorset. este es el objeto que contiene la colección de registros que utilizan los programas. Existen tres tipo diferentes de Recordset: Dynaset, table y Snapshot. Otro objeto es el Database (o base de datos) que convoca a la Base.

Las Bases tradicionales como FoxPro, DBase, Paradox se orientan a Registros, las operaciones que tienen que ver con la base se realizan registro por registro (bucle). En cambio Access y otras que soportan SQL están orientadas a conjuntos de datos, se hacen con sonsultas SQL. Las Bases de Datos que trabajan con SQL pueden ejecutar operaciones en tablas enteras con una sola instrucción.

El objeto Recordset de tipo Table es adecuado para ejecutar procesamientos orientados a registros. Los tipos Dynaset y Snapshot lo son para procesamientos orientados a conjuntos de datos. El primero es dinámico, permite lectura y escritura, el segundo sólo lectura.

Al crear el Dynaset por código en lugar de un control data debemos crear dos objetos: el DataBase y el RecordSet. Para esto nada mejor que un ejercicio.

Option Explicit Private Sub Form_Load()

'Lo primero que debo hacer es incorporar la referencia Microsoft DAO 3.51.

'creación de los objetos Database y recordset para manejar

'un conjunto de datos de tipo Dynaset

Dim db As Database 'este es el objeto Database

Dim rs As Recordset ' este es el objeto recordset

'creamos dos variables locales

Dim strNombreDB As String

Dim strNombreRS As String

'creamos una variable para contar los registros

Dim intRegs As Integer

'inicializamos las variables

strNombreDB = App.Path & "\books6.mdb"

strNombreRS = "Titles"

'crea los objetos

'abre la base de datos y hace que el objeto db apunte a ella

Set db = DBEngine.OpenDatabase(strNombreDB)

'genera el Dynaset a partir de la tabla Titles

Set rs = db.OpenRecordset(strNombreRS, dbOpenDynaset)

'cuenta los registros en el conjunto de datos

With rs

.MoveLast ' se mueve al final del registro

intRegs = .RecordCount ' obtiene la cantidad de registros

End With

'los vuelca en un msgbox

MsgBox strNombreRS & ": " & intRegs, vbInformation, "Total de Registros en el conjunto de Datos"

'los imprimo en el formulario

Print strNombreRS & ": " & intRegs & " Registros"

Print "____________________________________"

'Uso de la propiedad Filter y el Método OpenRecordset

'para obtener un segundo conjunto de datos.

Dim strFiltro As String

Dim rs2 As Recordset

strFiltro = "YearPub > 1990" 'crea el conjunto filtrado.

'Pruebe a cambiar el año a 1980 o 1993.

rs.Filter = strFiltro

Set rs2 = rs.OpenRecordset

With rs2 .MoveLast ' moverse al final del conjunto de datos

intRegs = .RecordCount 'obtenga el total

End With

MsgBox strFiltro & ": " & intRegs, vbInformation, "Total de registros en el segundo conjunto de datos"

'lo imprime en el formulario

Print strNombreRS & ": " & strFiltro & ": " & intRegs & " Registros"

End Sub

El codigo del ejercicio puede bajarse de AQUÍ.

Private Sub Text1_KeyPress(KeyAscii As Integer)

Dim strvalido As String

strvalido = "0123456789" 'habilita solo números

If InStr(strvalido, Chr(KeyAscii)) = 0 Then

KeyAscii = 0 ' sino es válido lo vuelve nulo

End If

End Sub

Private Sub Text2_KeyPress(KeyAscii As Integer)

KeyAscii = Asc(UCase(Chr(KeyAscii))) ' cambia a mayúsculas.

End Sub

Private Sub Text3_KeyPress(KeyAscii As Integer)

Dim strvalido As String

strvalido = "0123456789ABCDEFGHIJKLMNÑOPQRSTUVWXYZÁÉÍÓÚ"

KeyAscii = Asc(UCase(Chr(KeyAscii)))

If KeyAscii > 26 Then ' si no es un codigo de control

If InStr(strvalido, Chr(KeyAscii)) = 0 Then

KeyAscii = 0

End If

End If

End Sub

En la propiedad Mask: le determinamos un formato para número telefónico: (##) ### - ####. Y en la propiedad Format seleccionamos para Importe: $#,##0.00;($#.##0.00).

El código es:

Private Sub Form_Load() 'Carga la lista desplegable

With Combo1

.AddItem "Minorista"

.AddItem "Mayorista"

.AddItem "Distribuidor"

.AddItem "Otro"

.ListIndex = 0 ' estalece de forma predeterminada el primer elemento.

End With

End Sub

Aquí vamos a validar por código un campo requerido y un rango de mayor y menor, pero vamos a hacer la validación a través de el Click de un Botón.O sea vamos a hacer la validación a nivel de formulario y no de campos. Para esto vamos a crear una función validoform que cuando chequee que está todo correcto devuelva un valor adecuado, aceptando la información del formulario, de lo contrario devuelva un mensaje de error.

El código es:

Option Explicit

Private Sub Command1_Click()

Dim strMsj As String

strMsj = validoform() If strMsj = "" Then

Unload Me

Else

MsgBox strMsj, vbExclamation, "Error de validación"

End If

End Sub

Private Sub Form_Load() 'Carga la lista desplegable

With Combocliente

.AddItem "Minorista"

.AddItem "Mayorista"

.AddItem "Distribuidor"

.AddItem "Otro"

.ListIndex = 0 ' estalece de forma predeterminada el primer elemento.

End With

End Sub

Public Function validoform() As String 'valida el formulario y devuelve un mensaje de error si no es válido

'variables para la validación del rango

Dim intSup As Integer

Dim intInf As Integer

Dim strMsjSupInf As String 'variables para la validación de la longitud

Dim intMinLong As Integer

Dim intMaxLong As Integer

Dim strMsjMinMax As String

Dim strEnviarMsj As String 'valores del rango

intSup = 100 intInf = 0

strMsjSupInf = "El campo Superior/Inferior debe contener un valor entre " & CStr(intInf) & " y " & CStr(intSup) & "." 'valores de la longitud

intMinLong = 3

intMaxLong = 10

strMsjMinMax = "El campo Mayúsculas debe tener entre " & CStr(intMinLong) & " y " & CStr(intMaxLong) & " caracteres."

strEnviarMsj = "" 'verifique el campo Superior/Inferior

With txtsupeinf

If Val(.Text) < intInf Or Val(.Text) > intSup Then

strEnviarMsj = strEnviarMsj & vbCrLf & strMsjSupInf & vbCrLf

.SetFocus

End If

End With

'verifique el campo mayúsculas

With txtletrasmayusculas

If Len(.Text) < intMinLong Or Len(.Text) > intMaxLong Then

strEnviarMsj = strEnviarMsj & vbCrLf & strMsjMinMax & vbCrLf

.SetFocus

End If

End With

validoform = strEnviarMsj 'valido el campo dinero

If MaskEddinero.Text <> "" Then 'Tiene datos?

If Not IsNumeric(MaskEddinero.Text) Then ' son numericos?

strEnviarMsj = strEnviarMsj & vbCrLf & "El campo dinero debe tener un valor numérico." & vbCrLf MaskEddinero

.SetFocus

End If

End If 'verifico el campo combinado

With txtcombinado If Len(Trim(.Text)) = 0 Then

strEnviarMsj = strEnviarMsj & vbCrLf & "El campo combinado es requerido." & vbCrLf

.SetFocus

End If

End With

validoform = strEnviarMsj

End Function

Private Sub txtcombinado_KeyPress(KeyAscii As Integer)

Dim strvalido As String

strvalido = "0123456789ABCDEFGHIJKLMNÑOPQRSTUVWXYZÁÉÍÓÚ"

KeyAscii = Asc(UCase(Chr(KeyAscii)))

If KeyAscii > 26 Then ' si no es un codigo de control

If InStr(strvalido, Chr(KeyAscii)) = 0 Then

KeyAscii = 0

End If

End If

End Sub

Private Sub txtletrasmayusculas_KeyPress(KeyAscii As Integer)

KeyAscii = Asc(UCase(Chr(KeyAscii))) ' cambia a mayúsculas.

End Sub

Private Sub txtnumeros_KeyPress(KeyAscii As Integer)

Dim strvalido As String

strvalido = "0123456789" 'habilita solo números

If InStr(strvalido, Chr(KeyAscii)) = 0 Then

KeyAscii = 0 ' sino es válido lo vuelve nulo

End If

End Sub

Elcódigo completo de los ejercicios puede bajarse de AQUÍ.

Volver al Menú Principal.