En este apartado dedicado al MySQL (MariaDb) pretendo 'arrejuntar' algunos tips de los charcos que nos encontramos los acceseros al trabajar con MySQL, no pretende ser un estudio exhaustivo de Access vs MySQL.

En mi caso, estoy convirtiendo actualmente una aplicación que funciona en entorno local Office-2016 64 bits, tanto front-end como back-end, a un entorno Office365-Pro 64 bits el front-end y los datos en un servidor MySql situado en 'la nube'.

El primer paso es conseguir que la aplicación siga trabajando con la tablas vinculadas a través de un ODBC, para después, en un segundo paso, ir cambiando (poco a poco) los accesos de los formularios / informes / etc mediante objetos Connection y así eliminar las tablas vinculadas y los ODBC creados.

Las entradas van a ser desordenadas, según me vaya acordando y tenga tiempo y ganas.

Un saludo.

Una vez instalado el driver adecuado a nuestro Office 32 ó 64 bits (el que sea 32 ó 64 depende del Office que usemos no del S.O.) procederemos a crear el DSN en nuestra colección de ODBC.


Incluyo imágenes de cómo lo he configurado. Datos principales y pestaña Connection:




y en la pestaña Cursors/Results:



- es aconsejable marcar el valor:
   Allow big result sets

- e imprescindible hacerlo con:
   Return matched rows instead of affected rows

si no marcamos este último nos aparecerá el error:





Si necesitásemos automatizar la creación del DSN lo podemos hacer mediante VBA. En un módulo independiente:

La descripción de los atributos y valores la podemos encontrar en la siguiente dirección, así como todo lo relativo a MySQL:
     Site MySQL

Mis agradecimientos a la comunidad de acceseros de AUGE en general y a Xavi en particular por el código, bueno lo que me pasó era mucho más completo y extenso, yo le he dejado en lo imprescindible.

Ya tenemos la aplicación funcionando con tablas vinculadas, ahora viene el segundo paso, el de verdad, crear un objeto Conection y referir todos los accesos a la BD a través del mismo, eliminando las tablas vinculadas.


En mi caso uso el driver 8.0. Como veis en mi rutina de conexión los pasos son:
- primero compruebo el estado de la conexión, si es correcta compruebo si puede acceder a una tabla de control (es posible que la conexión este abierta pero no sea valida)
- si puede realizar la consulta del campo el valor será TRUE y salimos - en caso de que no sea OK, los pasos siguientes son:
   - asegurarnos de que esté cerrada
   - abrir la conexión

El comando "SET lc_time_names = 'es_ES'" es importante para el tratamiento de fechas como veremos en otra entrada

- No existen las funciones First ni Last, hay que sustituirlas por un Min / Max segun corresponda (y se pueda):

     SELECT Codigo, First(Campo) AS C1 FROM Tabla GROUP BY Codigo -> SELECT Codigo, Min(Campo) AS C1 FROM Tabla ...


- La cláusula TOP cambia de formato:

     SELECT TOP 10 Codigo, Nombre FROM Tabla -> SELECT Codigo, Nombre FROM Tabla LIMIT 10


- La función IIF pasa a llamarse IF, poquita cosa xD:

     SELECT IIF(Codigo > 0, 'S', 'N') AS Valor ... -> SELECT IF(Codigo > 0, 'S', 'N') AS Valor ...


- No existen IsDate IsNumeric IsNull, hay que utilizar Is Null o Not .. Is Null

     WHERE IsDate(MiFecha) AND IsNull(Campo) -> WHERE Not MiFecha Is Null AND Campo Is Null

            la verdad es que esto deberíamos hacerlo en VBA vs Tablas Access, las SQL son más rápidas de ejecución


- Concatenar campos se hace con la función Concat:

     SELECT Campo1 & '-' & Campo2 AS Valor -> SELECT Concat(Campo1, '-', Campo2) AS Valor


- El símbolo comodín cambia de * a % (al igual que una BD SQL):

     WHERE Campo1 Like '*abc*' -> WHERE Campo1 Like '%abc%'


- Los corchetes [] se cambian por ':

     SELECT [Un Campo] AS UnCampo FROM [Esta Tabla] -> SELECT 'Un Campo' AS UnCampo FROM 'Esta Tabla'

- No reconoce TRUE / FALSE, hay que poner los valores 0 y -1

     ... WHERE Campo = True AND Valor = False -> ... WHERE Campo = -1 AND Valor = 0

             esto trae muchos quebraderos de cabeza, porque la instrucción SQL no funciona, pero no casca, con lo que cuesta acordarse


- Esta me gusta, no hacen falta los paréntesis para agrupar las JOIN, con lo que según necesitas las agrupaciones vas copiando y pegando:

     " From siniestros" & _
     " LEFT JOIN circunstancias ON siniestros.Si_circunstancias = circunstancias.CIR_Codigo" & _
     " LEFT JOIN situacion ON siniestros.Si_situacion = situacion.SIT_Id" & _
     " LEFT JOIN seccion ON siniestros.Si_seccion = seccion.SEC_Codigo" & _
     " LEFT JOIN centros ON siniestros.Si_centro = centros.CE_id" & _
     " WHERE Id > 1000"


- en el código anterior ya se ve esta diferencia, según esté definida la BD no reconoce mayúsculas en los nombres de las tablas, te dice que no existe, en cambio en los nombres de los campos y las funciones se puede poner lo que se quiera, se lo traga todo:


- para borrar mas de una columna con SQL hay que incluir la instrucción DROP COLUMN por cada una de ellas:

     ALTER TABLE Mitabla DROP COLUMN Campo1, DROP COLUMN Campo2


- para crear una tabla con una consulta de selección no vale el SELECT INTO, el formato es el siguiente:

     CREATE TABLE Mitabla SELECT Campo1, Campo2 FROM OtraTabla ....


- si queremos crear campos a partir de valores, hay que tener cuidado porque crea el campo a la medida de lo escrito:

     CREATE TABLE Mitabla SELECT Campo1, Campo2, 'X' AS C1, 'XXX' AS C2, 0 AS N1, 00 AS N2, 00.00 AS N3, 000000.00 AS N4 FROM ...

             C1 -> varchar(1)
             C2 -> varchar(3)
             N1 -> int(1)
             N2 -> Int(2)
             N3 -> decimal(4,2)
             N4 -> decimal(8,2)


- cuando queremos cambiar registros (o borrarlos) en una tabla que no tiene índice se queja, para que no nos de error:

     SET SQL_SAFE_UDATES = 0
     UPDATE Mitabla SET Campo = 0
     SET SQL_SAFE_UDATES = 1


- La instrucción de borrado de registros va sin el *:

     DELETE * FROM Mitabla -> DELETE FROM Mitabla

- El formato de las instrucciones UPDATE con más de una tabla es muy distinto de como estamos acostumbrados en VBA:

· por un lado hay que poner un calificador a cada una de las tablas
· por otro no hay que poner campos en la SELECT, simplemente basta con relacionar las tablas
· defininir las modificaciones a realizar en la SET
· por último poner la cláusula WHERE

Es decir:

UPDATE (SELECT CampoTabla1, CampoTabla2 FROM Tabla1 LEFT JOIN Tabla2 ON Tabla1.Id = Tabla2.Id WHERE Tabla1.Id = 4)
             SET CampoTabla1 = CampoTabla2

quedaría:

UPDATE Tabla1 AS T1 LEFT JOIN Tabla2 AS T2 ON T1.Id = T2.Id
             SET CampoTabla1 = CampoTabla2
             WHERE T1.Id = 4

A mi me parece más fácil ....



- la fecha actual se obtiene como curdate() y la hora actual como curtime(), todo junto ni me lo he mirado porque yo siempre pongo por separado fechas y hora, juntos son una fuente de incordios:

       INSERT INTO Tabla1 (Fecha, Hora) VALUES (curdate(), curtime())


- por cierto la fecha hay que pasarla como: 'aaaa-mm-dd'
  y la hora: 'hh:mm' o 'hh:mm:ss'

       INSERT INTO Tabla1 (Fecha, Hora) VALUES ('2020-09-10', '21:45')


- Caracteres especiales, secuencias de escape. Al igual que en VBA la comilla simple y la doble dan guerra a la hora de actualizar datos de campos, en MySQL ocurre lo mismo, el que más incordia es el backslah (\) ya que cualquier ruta que vayamos a almacenar lo lleva. Hay que cambiar esos caracteres por una secuencia de escape, las detallo a continuación:

· Escape Sequence Character Represented by Sequence:
       \0 An ASCII NUL (X'00') character
       \' A single quote (') character
       \" A double quote (") character
       \b A backspace character
       \n A newline (linefeed) character
       \r A carriage return character
       \t A tab character
       \Z ASCII 26 (Control+Z); see note following the table
       \\ A backslash (\) character
       \% A % character; see note following the table
       \_ A _ character; see note following the table

Más información sobre este tema en: https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

• comprobar si existe un registro
• si existe actualizamos un valor
• si no existe tenemos que crear el registro

INSERT INTO tparametros (Codigo_tpar, Descripcion_tpar, Valor_tpar)

VALUES (Codigo1, 'Descripcion del registro', ValorInicial)

ON DUPLICATE KEY UPDATE Valor_tpar = NuevoValor

Vamos con el último grupo de charcos de esta carrera de obstáculos: los Recordsets.

- Reconozco que me siento muy cómodo con ellos ... sólo cuándo los tiempos de ejecución lo aconsejan uso SQL puro:
     Conection.Execute "UPDATE ..."

- Hay que cambiar de usar DAO a ADO y aunque parezca un trabalenguas las diferencias son notables ... personalmente me gusta mas DAO, pero es lo que hay.

- Para ahorrarme tecleos a la hora de abrirlos y de recordar las distintas opciones que hay que emplear me he creado tres rutinas que me permiten abrir el 99 % de los que voy a emplear:

Function RT_AbrirRs(SQL As String, Optional TipoCursor As ADODB.CursorTypeEnum = adOpenForwardOnly, _      Optional LockRs As ADODB.LockTypeEnum = adLockReadOnly, _      Optional LadoCursor As ADODB.CursorLocationEnum = adUseServer) As ADODB.Recordset      Set RT_AbrirRs = New ADODB.Recordset      RT_AbrirRs.CursorLocation = LadoCursor      RT_AbrirRs.Open Lcase$(SQL), CnRemota, TipoCursor, LockRs End Function

Con esta cubro un amplio espectro de posibilidades, ojo a seleccionar bien el tipo de cursor, el bloqueo y el lado cursor (éste último siempre en lado Server, excepto en dónde no hay más remedio que usarlo en lado Client.

Function RT_AbrirRsLista(SQL As String) As ADODB.Recordset      Set RT_AbrirRsLista = New ADODB.Recordset      RT_AbrirRsLista.CursorLocation = adUseClient      RT_AbrirRsLista.Open Lcase$(SQL), CnRemota, adOpenStatic, adLockReadOnly End Function

Esta segunda la uso para abrir cuadros de lista (y combos), como nota decir que deben ser en lado cliente y al no modificar registros el tipo de cursor es adOpenStatic con bloqueo adLockReadOnly. También sería el que habría que usar si asignáramos el RecordSet de un formulario en sólo lectura.

En el caso de un formulario con escritura de registros tendrá que ser adOpenDynamic - adLockOptimistic. Aclarar que no uso formularios a los que ponga un origen de registros, siempre uso listas, por tema de bloqueos que repercute en la velocidad y fiabilidad de la aplicación. Como no los uso no he creado rutina para abrirlos.

Para dar valores a una lista (llamada Usuario) haríamos lo siguiente:

   Set Me.Usuario.Recordset = RT_AbrirRsLista("SELECT Id, Usuario FROM Usuarios WHERE Activo = -1 ORDER BY Usuario")

Function RT_AbrirRsAdd(ByVal Tabla As String, ByVal CampoId As String, _      Optional TipoCursor As ADODB.CursorTypeEnum = adOpenKeyset, _      Optional LockRs As ADODB.LockTypeEnum = adLockOptimistic, _      Optional LadoCursor As ADODB.CursorLocationEnum = adUseServer, _      Optional IdAsString As Boolean = False) As ADODB.Recordset           Set RT_AbrirRsAdd = New ADODB.Recordset      RT_AbrirRsAdd.CursorLocation = LadoCursor      RT_AbrirRsAdd.Open LCase$("SELECT * FROM " & Tabla & " WHERE " & CampoId & _                         IIf(IdAsString, " = 'ZZZZZZZZZZZZZ'", " = 0")), CnRemota, TipoCursor, LockRs End Function

Este tercer caso es muy atípico y reconozco que no lo debería usar, pero me gusta mucho más dar de alta un registro con un recordset que con una instrucción SQL, sobre todo cuando tiene un montón de campos, no hay nada que me fastidie más que me de un error de esos crípticos en una consulta de inserción de 30 campos y ... hala, ponte a averiguar cuál de ellos es el culpable, si falta una coma, si sobra un campo, si falta.

La cuestión es que la apertura de un recordset en dbAppendOnly en ADO no existe ... y se echa de menos porque si abres un recordset con 100.000 registros sin una cláusula WHERE hace que tengas que ver como pasan segundos hasta que ejecuta la apertura (se carga la tabla completa), el truco que he empleado es realizar la apertura como una WHERE por su índice con un valor inexistente con lo que la apertura es inmediata y ya se puede ejecutar a continuación el Rs.addNew, ... Rs.Update.

Por cierto esto vale para un registro, si intentáis insertar más de uno, la segunda vez que ejecuta el Rs.Update casca miserablemente. Así que en caso de inserción masiva hay que utilizar SQL, pero con una variante, en vez de realizar una instrucción SQL por cada registro lo que hay que construir es una SQL del tipo:

     INSERT INTO Tabla (Campo1, Campo2) VALUES (Valor1, Valor2), (Valor12, Valor22), (Valor13, Valor23)

Esta forma de hacerlo me la indico Juan Carlos de Contab@t , gracias apañero.



- Una cuestión importante sobre el lado del cursor, cuando necesitemos usar el valor de RecordCount o conocer el Id del registro que se acaba de crear el cursor debe estar del lado cliente, sino los valores que ofrece no son reales, mis disgustos me ha costado esta tontería.

- Por cierto, el Id toma valor después de realizar el Update, no justo después del AddNew (¿OK @Xavi?)

Set Rs = RT_AbrirRsAdd("Usuarios", "Id")      Rs.AddNew          Rs!Usuario = "Juan"          Rs.Update          'Guardamos el id          User_Id = Rs!Id      Rs.Close

Otra tema aparte es dar formato a los literales de numeros, fechas y horas para poder mostrarlos en listas debidamente alineados. Para ello hay que hacer que los resultados tengan la misma longitud con los blancos necesarios por delante y el fuente debe ser de paso fijo (yo uso normalmente Consolas).

Como es MySQL el que debe crear los formatos hay que utilizar las funciones de éste, las rutinas que me he creado son las siguientes:

Function RT_FormHoraMySQL(ByVal Campo As String, Optional Longitud As Integer = 6) As String      RT_FormHoraMySQL = "LPAD(date_format(" & Campo & ", '%k:%i'), " & Longitud & ", ' ')" End Function Function RT_FormFechaMySQL(ByVal Campo As String, Optional Longitud As Integer = 10) As String      RT_FormFechaMySQL = "LPAD(date_format(" & Campo & ", '%e-%b-%y'), " & Longitud & ", ' ')" End Function Function RT_FormNumeroMySQL(ByVal Campo As String, Optional Longitud As Byte = 14, _          Optional NumeroDecimales As Byte = 2) As String      RT_FormNumeroMySQL = "LPAD(FORMAT(" & Campo & ", " & NumeroDecimales & ", 'de_DE')," & Longitud & ", ' ')" End Function

Para llamarlas:

Xs = "SELECT IDpagos, " & RT_FormFechaMySQL("fechaapunte") & " AS F1, " & _          RT_FormNumeroMySQL("importepagos", 14, 2) & " AS I1, " & _          " FROM Tabla …."      Set Me.Lista.Recordset = RT_AbrirRsLista(Xs)

Y el resultado:



En el caso de las fechas para que funcione es necesario haber ejecutado el comando SET lc_time_names al que nos referíamos en la entrada referente a la cadena de conexión ODBC ... si no las abreviaturas de la misma saldrían en Inglés.



- Un error muy genérico que me surgió y me dió muchos quebraderos de cabeza fué este:

     ** Error: La operación de múltiples pasos de OLE DB generó errores. Compruebe los valores de estado de OLE DB si es posible. No se realizó ningún trabajo.

El error lo daba en el update de una tabla, y al final resultó ser que por aquello de las pruebas estaba intentando dar de alta un registro cuyo Id estaba duplicado ... ¡Ya podía dar mensajes más descriptivos del error! .... bueno, es lo que hay.




Obviamente faltan muchas cosas, pero según me vaya acordando o surgiendo las iré posteando. Espero que estas entradas os eviten unos cuantos dolores de cabeza a la hora de afrontar como acceseros el uso de MySQL como BD.