Excel + SQL Server + Azure | Azure SQL Database


Esta vez veremos una alternativa más de cómo podemos gestionar nuestros datos, y es creando una base de datos con Azure SQL Database.
Estas bases de datos no se gestionan dentro de una máquina virtual en particular, sino que son administradas directamente en el portal de Azure, y podremos acceder remotamente a ella desde nuestro proyecto cliente de Excel, mediante un servidor SQL que crearemos en el proceso.

Para este ejercicio, seguiremos utilizando los procedimientos almacenados que hemos visto en los capítulos anteriores, y además, he creado otros procedimientos almacenados que nos pueden servir de base para construir un sistema de facturación.

Aquí les dejo el query que contiene el Script completo que nos permitirá manejar los registros en el Servidor SQL de Azure.

create table productos
(
id_Cod int identity primary key,
cod_prod varchar(4) not null,
descripcion varchar(50)not null,
existencia int not null,
)
GO
insert into productos values('A001','MEMORIA USB 32GB',175);
insert into productos values('A002','DISCO DURO 2TB',15);
insert into productos values('A003','AIRE COMPRIMIDO',250);
insert into productos values('A004','ESPUMA LIMPIADORA',300);
insert into productos values('A005','FUNDA MONITOR',500);
insert into productos values('A006','FUNDA TECLADO',700);
insert into productos values('A007','SILLA ERGONÓMICA',11);
insert into productos values('A008','ALFOMBRILLA PARA SILLA',25);
insert into productos values('A009','LÁMPARA ESCRITORIO',7);
insert into productos values('A010','MONITOR LED 18 PULGADAS',45);
insert into productos values('A011','LIBRERO',20);
GO

CREATE PROCEDURE SP_RegistrarProducto
@CodProd varchar(4),
@Descripcion varchar(50),
@Existencia int
AS
begin
insert into productos  values(@CodProd, @Descripcion, @Existencia)
end
GO

CREATE PROCEDURE SP_ModificarProducto
@CodProd varchar(4), 
@Descripcion varchar(50), 
@Existencia int
AS
update productos  set cod_prod =@CodProd, descripcion=@Descripcion, existencia=@Existencia
where
cod_prod=@CodProd
GO

CREATE PROC SP_EliminarProducto
@CodProd as varchar(4)
AS
delete from productos WHERE cod_prod=@CodProd 
GO

CREATE PROC SP_RestarExistencia
@CodProd as varchar(4),
@Cantidad as int
AS
UPDATE productos SET existencia=existencia-@Cantidad 
WHERE cod_prod=@CodProd 
GO

CREATE PROC SP_SumarExistencia
@CodProd as varchar(4),
@Cantidad as int
AS
UPDATE productos SET existencia=existencia+@Cantidad 
WHERE cod_prod=@CodProd 
GO

CREATE PROCEDURE SP_MostrarDatos
AS
SELECT * FROM productos



Por otra parte, el proyecto de Excel que nos sirve de cliente de conexión, lo he modificado de tal forma que podamos realizar consultas SQL y recuperar los datos directamente en una hoja de cálculo.
El siguiente código hace posible llamar los datos al servidor, desde Excel...


Sub ConsultaSQL()
Dim Fila As Long
Dim i As Long
Dim sql As String

On Error GoTo ErrorHandler

Hoja1.Range("A:D").Clear

    sql = [miConsulta].Text
    
ErrorHandler:
    If Err < 0 Then
        MsgBox Err.Description, vbCritical
        Exit Sub
    End If

     If Err = 3704 Then
         sql = "select * from productos order by cod_prod"
     End If
 
    Set Rst = New ADODB.Recordset
    Rst.Source = sql
    Rst.ActiveConnection = Conn
    Rst.Open sql, Conn

    Fila = 0
        
    For i = 0 To Rst.Fields.Count - 1
        Hoja1.Cells(Fila + 1, i + 1).Value = Rst.Fields(i).Name
    Next i

    Fila = 1
    
    Do While Not Rst.EOF
        For i = 0 To Rst.Fields.Count - 1
            Hoja1.Cells(Fila + 1, i + 1).Value = Rst.Fields(i)
        Next i
        Fila = Fila + 1
        Rst.MoveNext
    Loop
   
End Sub

Te invito a ver el vídeo en YouTube y descubrirás lo simple que es hacer este tipo de implementaciones.


Ver vídeo

Descarga el Proyecto

Excel + SQL Server + Azure | Managing Data in SQL Server on Ubuntu Serve

Lista de Reproducción en YouTube

En este capítulo he modificado los proyectos de Excel que vimos anteriormente, agregándole un ListBox para dar seguimiento a los datos que enviemos al servidor Linux. Además, he estoy reciclando los botones de comando agregados al UserForm, dándole la funcionalidad de varios botones y de esa forma ahorrar espacio en el formulario.



Seguimos usando los Procedimientos Almacenados que vimos en los capítulos anteriores, y ahora con el ListBox, le doy un plus a este proyecto, lo cual nos permitirá ver los registros actualizados en tiempo real.


El siguiente código de programación es la rutina que permite hacer la consulta SQL hacia el servidor, lo cual nos devuelve los datos que se mostrarán en el ListBox.


Private Sub MostrarDatos()
Dim Fila As Integer
Dim SQL As String

ListBox1.Clear
    ' Consulta en SQL
    SQL = "select cod_prod, descripcion, existencia from productos order by cod_prod"
   
    Set Rst = New ADODB.Recordset
    Rst.Source = SQL
    Rst.ActiveConnection = Conn
    Rst.Open SQL, Conn

    Fila = 0

    Do While Not Rst.EOF
            ListBox1.AddItem Rst.Fields("cod_prod")
            ListBox1.List(Fila, 1) = Rst.Fields("descripcion")
            ListBox1.List(Fila, 2) = Rst.Fields("existencia")

        Fila = Fila + 1
        Rst.MoveNext
    Loop
End Sub

Te invito a ver el capítulo completo en YouTube y descubre lo simple que es obtener estos resultados.


Ver vídeo

Descarga el Proyecto

How to Install SQL Server on Ubuntu Server VM

Lista de Reproducción en YouTube.excelymas.com

En esta ocasión crearé un servidor Linux desde una imagen disponible en el Marketplace de Microsoft Azure.
Este ejercicio lo realizaré instalando un Ubuntu Server y al cual posteriormente le instalaré SQL Server para poder acceder desde un proyecto de Excel que servirá de cliente de conexión.









La instalación paso a paso del sistema operativo y SQL Server, la pueden ver en mi canal de YouTube en el siguiente enlace...


Ver vídeo en YouTube





Sin embargo en la instalación, opté por autenticarnos en el servidor, mediante una contraseña, y la forma recomendada y tener una mejor seguridad en nuestro servidor, es hacerlo mediante una clave pública SSH (Secure Shell)


Aquí les muestro paso a paso cómo generar claves públicas SSH y acceder de esa forma a nuestro servidor linux.


Paso 1

La conexión la realizaré desde Windows hacia Linux, y lo haré con el cliente de conexión llamado PuTTY, claro esto solo servirá para conectarnos, ya que antes debemos generar las claves públicas con las siguientes herramientas: git para Wndows y PuTTYgen para Windows

Esto es lo que necesitas descargar




Paso 2


una vez hayas instalado Git, procede a ejecutar Git Bash como administrador y escribe el siguiente comando: openssl.exe req -x509 -nodes -days 365 -newkey rsa:2048 -keyout myPrivateKey.key -out myCert.pem



Le das Enter y vas proporcionando los datos que te vaya solicitando.

Ejecuta el siguiente comando para convertir el Archivo myPrivate.Key en una clave privada RSA

openssl rsa -in ./myPrivateKey.key -out myPrivateKey_rsa





Paso 3


Ejecuta puttygen.exe y das click en File >  Load a Private Key







y busca la clave privada a la que denominamos anteriormente myPrivateKey_rsa





Lo abres y te aparecerá un mensaje parecido al siguiente...


Click en Ok y te aparecerá la ventana de la siguiente manera...




En esta ventana das click en el botón Save private key y posteriormente en Save public key



y te pedirá que guardes un archivo txt que contiene la clave pública que te solicitará Azure en el momento que estés creando tu máquina virtual.







Paso 4

Una vez hayas instalado la máquina virtual, puedes proceder a ingresar al servidor, ejecutando el cliente de conexión PuTTY.exe e ingresas la IP Públcia o el nombre DNS del servidor...





Luego haz clic en la pestaña Connection > SSH > Auth para elegir la clave privada, como se muestra en la siguiente imagen...





Le das click en Open, para conectar con la máquina virtual...



Te aparecerá la advertencia de seguridad, das click en sí, y ya estarás conectado con tu máquina virtual...



Partiendo de aquí, ya puedes instalar SQL Server, según los pasos que sigues en el video publicado en YouTube.

Pues esta es la forma de como poner seguridad a nuestro servidor Linux, mediante claves públicas SSH.

El proyecto de Excel que les mencioné al principio, lo veremos en la próxima entrega, en donde analizaremos su código de programación.

Si el contenido te gustó, compártelo con tus amigos en las diferentes redes sociales, y además te invito a que te suscribas a mi canal en Youtube.excelymas.com







Excel + SQL Server + Azure | Execute Stored Procedures from Excel VBA

Click en la imagen para ver la Lista de Reproducción en YouTube

En este capítulo veremos cómo ejecutar Procedimientos Almacenados desde Excel, mediante código de programación VBA.  Nuestra base de datos la tenemos alojada en una máquina virtual creada en Microsoft Azure, en la cual tenemos instalado SQL Server. Por lo que nuestro proyecto de Excel, viene siendo un cliente de conexión para gestionar los registros almacenados en la nube.

Estos son los Procedimientos Almacenados los cuales utilizaremos para gestionar los registros en la nube.


CREATE PROCEDURE SP_RegistrarProducto
@CodProd varchar(4),
@Descripcion varchar(50),
@Existencia int
AS
begin
insert into productos  values(@CodProd, @Descripcion, @Existencia)
end
GO

CREATE PROCEDURE SP_ModificarProducto
@CodProd varchar(4), 
@Descripcion varchar(50), 
@Existencia int
AS
update productos  set cod_prod =@CodProd, descripcion=@Descripcion, existencia=@Existencia
where
cod_prod=@CodProd
GO

CREATE PROC SP_EliminarProducto
@CodProd as varchar(4)
AS
delete from productos WHERE cod_prod=@CodProd 


Si no tienes conocimientos sobre SQL Server, te invito a ver el curso completo AQUÍ



E proyecto que he diseñado como cliente de conexión, cuenta con los siguientes UserForms...






Cada uno de estos formularios, disparan los Procedimientos Almacenados, arriba mencionados.
Cabe mencionar que dichos procedimientos se encuentran en SQL Server almacenados en Microsoft Azure. Desde estos formularios únicamente se hace la llamada para dispararlos.

Te invito a ver el capítulo completo, para que tengas una mejor comprensión de cómo trabaja este proyecto.



Ver vídeo

Descarga el Proyecto Base


Excel + SQL Server + Azure | Sending Data from Excel to SQL Server on Azure VM



En esta entrega veremos cómo conectar a la máquina virtual que creamos en Azure en el capítulo anterior.

He diseñado un UserForm, que nos permitirá realizar la conexión al servidor y hacer el registro de los datos con una sentencia SQL desde Excel, haciendo uso de código de programación VBA.

Aquí pueden ver la apariencia que tiene dicho formulario, y lo práctico que resulta conectar a una máquina virtual en la nube y gestionar la base de datos alojada en SQL Server.



Una vez conectemos al servidor, podremos enviar registros fácilmente al gestor de base de datos.

Observa lo sencillo que es crear un procedimiento para conectar a SQL Server.



Sub Conectar_SQLserver(servidor As String, usuario As String, pass As String, base As String)
On Error GoTo Salir
    
    Set Conn = New ADODB.Connection
                     
        Conn.ConnectionString = "Data Source=" & servidor & ";" & _
                                "Initial Catalog=" & base & ";" & _
                                "Persist Security Info=True;" & _
                                "User ID=" & usuario & ";" & _
                                "Password=" & pass & ";" & _
                                "provider=SqlOLEDB.1"
    

    Conn.Open
    
    MsgBox "Conexión establecida satisfactoriamente", vbInformation
    Conectado = True
    
Salir:
    If Err <> 0 Then
        MsgBox Err.Description, vbCritical
        Conectado = False
    End If
End Sub


Te invito a que veas el vídeo para que tengas la explicación completa...
Y si ya lo viste, y vienes desde el canal de YouTube, abajo encontrarás el proyecto para que lo puedas descargar y hacer las pruebas correspondientes.



Ver vídeo


Descarga el Proyecto