Tips de Bases de Datos con ADO -------------------------------------------------------------------------------- #8. Conectarse a una base de datos Access en paso simple Posiblemente se te olvide la sintaxis de la cadena de coneccion a una base de datos Access. Yo he solucionado esto con una función generalizada (sirve para versiones superiores a Access-95): Public Sub UseAccessDatabase( _ DatabaseName As String, _ Optional Password As String = "") Dim s As String s = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & DatabaseName & ";" If Len(Password) Then s = s & "Jet OLEDB:Database Password=" & Password & ";" End If m_ConnectionString = s End Sub Como vez no necesitas saber nada de la sintaxis ADO de conexión para conectarte a una base de datos Access, aun si esta tiene contraseña. NOTA. Este método se escribio para una clase, la cual tiene una Property llamada ConnectionString, con la que se obtiene la cadena de conexión. -------------------------------------------------------------------------------- #8. Cambiar la localización de una BD de un DataEnvironment en tiempo de ejecusión Puede que al distribuir su aplicación nota que debe modificar la ruta de la base de datos. Solucione de la siguinete manera: 1. Obtenga la cadena de conexión que dio en tiempo de diseño, bastara con un : Debug.Print DE.Connections(1).ConnectionString (DE es el nombre del DataEnvironment en el ejemplo), 2. En el inicio de su programa escriba algo similar al siguiente corte de código, preservando su cadena de conexión (modifica solo la localización de la base de datos, en este caso yo use la variable ArchivoBaseDeDatos) Private Sub Form_Initialize() Dim s As String s = "Provider=MSDataShape.1;" & _ "Persist Security Info=False;" & _ "Data Source=" & ArchivoBaseDeDatos & ";" & _ "Data Provider=MICROSOFT.JET.OLEDB.4.0" DE.Connections(1).ConnectionString = s End Sub -------------------------------------------------------------------------------- #8. Consultas Parametrizadas El código para escribir obtener un Recordset parametrizado es algo tedioso. Funciones como la que presento a continuación simplifican la tarea definitivamente. La siguiente función entrega un Recordset de tipo Static. Note que la cadena de conexión es un miembro de Clase, puede especificarla la misma como parámetro si lo desea (cuando escriba componentes de acceso a datos verá que es muy cómodo trabajar la cadena de conexión como una propiedad de la Clase) Public Function StaticRecordset( _ QueryName As String, _ ParamArray Param() As Variant _ ) As ADODB.Recordset Dim rs As ADODB.Recordset Dim cmd As ADODB.Command Dim pmt As ADODB.Parameter Dim i As Long On Error GoTo ErrHandler Set cmd = New ADODB.Command Set rs = New ADODB.Recordset With cmd .ActiveConnection = m_ConnectionString .CommandType = adCmdTable .CommandText = "[" & QueryName & "]" For Each pmt In .Parameters pmt.Value = Param(i) i = i + 1 Next End With With rs .CursorLocation = adUseClient .Open cmd, , adOpenStatic Set cmd.ActiveConnection = Nothing Set cmd = Nothing Set .ActiveConnection = Nothing End With Set StaticRecordset = rs Exit Function ErrHandler: Set StaticRecordset = Nothing Call ErrHandlerAction(Err) End Function Se pueden escribir funciones similares para diferentes tipos de Recordsets EJEMPLO: Dim rs As ADODB.Recordset '//Get data Set rs = gData.StaticRecordset(u.QueryName, lggCur.IdWell) '//new page Rows = rs.RecordCount If Rows Then ... -------------------------------------------------------------------------------- #7. SQL Parametrizados La escritura de un SQL con parametros, incrustando variables, generalmente produce lineas de codigo largas y dificiles de mantener. La siguiente funcion permite un codigo limpio: Public Function SQLParams(sql As String, ParamArray Params() As Variant) As String Dim ptr As Long Dim rtn As String Dim Param As Variant rtn = sql ptr = 1 For Each Param In Params() ptr = InStr(ptr, rtn, "?") If ptr Then rtn = Left$(rtn, ptr - 1) & Param & Mid$(rtn, 1 + ptr) End If Next SQLParams = rtn End Function Los parametros en la cadena SQL se escriben con un "?", teniendo en cuenta que las cadenas iran entre comillas simples. Ejemplo sql=SQLParams("SELECT * FROM [Logs Bank] WHERE enum_log=? AND id_graph='?';", EnumLog, IdGraph) -------------------------------------------------------------------------------- #6. Mostrar Registro n de Tantos La siguiende función entrega una cadena con el texto: titulo n de tantos, Private Function RecordLocation( _ adoRs As ADODB.Recordset, _ Optional Title As String = vbNullString _ ) On Error GoTo ErrHandler With adoRs If Not (.EOF Or .BOF) Then RecordLocation = Title & .AbsolutePosition & " de " & .RecordCount Else RecordLocation = vbNullString End If End With Exit Function ErrHandler: RecordLocation = "VOID" End Function Aplica a cualquier modo de Recordset ADO. Por ejemplo para un ADODC de nombre adcTitles, enlazado a una tabla Titles; invocamos en el evento: Private Sub adcTitles_MoveComplete( _ ByVal adReason As ADODB.EventReasonEnum, _ ByVal pError As ADODB.Error, _ adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset _ ) '//Muestra: Titulo i de n adcTitles.Caption = RecordLocation(adcTitles.Recordset, "Titulo: ") End Sub -------------------------------------------------------------------------------- #5. Filtros subsecuentes Mayo 19 de 1999 Aplicar Filtros con ADO es una de las novedades que me gusto. No es necesario reconstruir un Recordset para filtrar varias veces. P.e Tengo un Recorset con 12 registros, deseo aquellos registros donde el campo Field1 = "Value1" (String), digamos que son 4 registros: rs.Filter = "[Field1] = 'Value1'" '// rs.RecordCound retornará 4 Luego deseo obtener aquellos que son direrentes a Value1: rs.Filter = "[Field1] <> 'Value1'" '// rs.RecordCound retornará 8 Es decir, no es necesario ejecutar un rs.Open o Requery para filtrar el Recordset. Para eliminar el filtro, se ejecuta rs.Filter = adFilterNone -------------------------------------------------------------------------------- #4. Un ejemplo simple de Acceso a datos en un Servidor Web con ASP y ADO Abril 10 de 1999 Cree y ejecute el ejemplo usando Personal Web Server 4.0 y una BD Access. El DSN se crea desde el panel de Control, OBDC32, usando Access97 como proveedor de datos. El ejemplo aplica a otros orígenes de datos. Sample1-ADO-Access

Sample1-ADO-Access

Crea una lista con el primer campo del Query (siempre entre corchetes cuadrados). Podría haber usado sSQL="SELECT ....". NOTAS. - Para depuración local, Es requerido Personal Web Server versión 4.0, el cual se encuentra gratis en los Downloads de la página de FrontPage (o viene con VB6 o VS6). Es ideal porque permite depurar los desarrollos para Internet localmente. - Realmente el ejemplo corresponde a Secuencia de Comandos ASP. Las verdaderas aplicaciones Visual Basic del lado del servidor son las Aplicaciones IIS (clases Web), sin embargo se tienen la mismas bases. - ASP acepta bases de datos de archivos compartidos (Microsoft(r) Access o Microsoft(r) FoxPro) como orígenes válidos de datos, sin embargo se recomienda utilizar estos tipos de motores de bases de datos únicamente con fines de desarrollo o en situaciones de despliegue limitado. Para aplicaciones Web de producción se deben usar BDs Cliente-Servidor.. -------------------------------------------------------------------------------- #3. Ejemplo de acceso a datos con ADOR Mar 31 de 1999 ActiveX Data Objects Recordset es un componente de acceso a datos de solo lectura, complemento funcional de ADO. Quizás es el más compacto y ligero modo de acceso a datos, y es ideal para el Web. Publico este ejemplo para mostrar como se usaría ADOR desde Visual Basic (en general se usa en el páginas Web con Scrips). Para este ejemplo cree un DSN de sistema (TEST0) desde el panel de control, OBDC(32 Bits), con acceso a una DB Access97 local (Colleción.MDB). Dim adorRs As ADOR.Recordset Set adorRs = New ADOR.Recordset With adorRs .ActiveConnection = "Data Source=TEST0;" '//DSN .Source = "SELECT [TituloGrabación] FROM [Grabaciones]" .Open Do Until .EOF Debug.Print .Fields(0) .MoveNext Loop End With NOTA. LA biblioteca de referenca de ADOR es Microsoft ActiveX Data Objects Recordset 2.0. -------------------------------------------------------------------------------- #2. ADO: Usar una BD Jet protegida bajo el sistema de cuentas Mar 1 de 1999 En este ejemplo cree explícitamente la cadena ActiveConnection (variable SCR) para ilustración. Es más flexible e inteligente usar un archivo OLEDB (archivos de extensión UDL), el cual contine toda la información de conexión a la BD. Las variables sPws y sUsr fuerón definidas previamente. Note la especificación de la base de datos del sistema. Private Sub JetTest() Dim SRC As String Dim SQL As String Dim rs As New Recordset Dim cmd As New Command SRC = "Provider=Microsoft.Jet.OLEDB.3.51;" + _ "Password=" + sPwd + ";" + _ "Persist Security Info=True;" + _ "User ID=" + sUsr + ";" + _ "Data Source=C:\ProyPer99\PerDatos99.mdb;" + _ "Jet OLEDB:System database=C:\ProyPer99\GrupoPSoft.mdw" SQL = "SELECT [Nombre] " + _ "FROM [Banco de Empresas] " + _ "WHERE [Ciudad]='Bogotá';" '//Activa la conexión ADO With cmd .ActiveConnection = SRC .CommandText = SQL Set rs = .Execute() End With '//Lleno un ListBox con datos: lstBogota.Clear With rs Do Until rs.EOF lstBogota.AddItem .Fields(0) .MoveNext Loop .Close End With End Sub NOTA. Declaré los objetos ADO sin el nombre de la librería, sin embargo normalmente ésta se especifica. P.e. Dim rs As New ADODB.Recordset -------------------------------------------------------------------------------- #1. ADO: Una consulta con parámetros Feb 27 de 1999 En el siguiente ejemplo establezco una conexión OLEDB a la BD del ejemplo Nwind.MDB. Esto se hace a través de un archivo UDL Este tipo de conexión es el recomendado (en especial para BDs Jet, donde una conexión DSD (OBDC) no suele ser eficiente). Este ejemplo es bastante plano. Tiene mejor nivel escribir un procedimiento similar a OpenQueryParams del tip #11. Quizá más adelante lo publique así. Private Sub LitleTest() Dim rs As New adodb.Recordset Dim cmd As New adodb.Command With cmd .ActiveConnection = "FILE NAME=D:\Archivos de programa\Microsoft Visual Studio\VB98\Nwind.UDL" .CommandType = adCmdTable .CommandText = "ProductsByID"'//Nombre del Query .Parameters(0) = 3 '//Valor ejemplo del parámetro Set rs = .Execute() End With Debug.Print rs(0), rs(1), rs(2) '//Obteniendo datos rs.Close End Sub NOTA1. Ejecutar una consulta con parámetros usando una conexión DSN es diferente, para más información ver el articulo Q181782 (HOWTO: Work w/ Microsoft Access QueryDef Parameter Using VB) del KB de MS. NOTA2. Cuando el nombre del Query tiene espacios, debe encerrarse en corchetes cuadrados. -------------------------------------------------------------------------------- Harvey Triana Derechos Reservados. Autorización solo para programación Tips escritos por Harvey, en caso contrario se hará referencia en la nota Ultima Actualización: Sábado 09 de Marzo de 2002