Banco de Dados com VBA – Integração com Access

Aulas Disponíveis

• Parte1 – Integração com Access
• Parte2 – Integração com Access

Parte1 – Integração com Access

Na aula de hoje: banco de dados com VBA parte1. Vou te mostrar a integração do VBA com o banco de dados Access.

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:

Fala, Impressionadores! Hoje vamos te mostrar como fazer a integração de banco de dados com VBA e a utilização de um formulário no VBA para atualizar as informações!

Eu quero te mostrar como conectar Excel com Access (que é um banco de dados da Microsoft) e como vamos cadastrar, editar e excluir informações no banco de dados.

A ideia de conectar Excel com banco de dados é que o banco de dados é feito para o armazenamento de dados, então ele é mais seguro, faz isso de uma forma mais otimizada e consegue armazenar uma quantidade absurda de informações.

O Excel mesmo consegue utilizar 1 milhão de linhas, mas isso já deixaria o programa bem lento, então não seria ideal utilizá-lo como banco de dados.

Por isso vamos fazer a integração do Access com VBA para fazer essas operações de forma mais rápida e vamos utilizar o formulário no VBA para deixar essa interação mais fácil!

Banco de Dados – Exemplo

Temos um arquivo que armazena os dados de cadastro de funcionários de uma empresa.

Ele foi disponibilizado para download junto com os arquivos de aula. Veja abaixo ele aberto:

Inserindo um módulo

Devemos utilizar um código específico para utilizar/invocar as funções/códigos necessários para a comunicação com o banco de dados (Access). Para tal, precisaremos ativar uma referência específica no VBA.

Então, você precisará:

• Abrir o VBA (Alt(+Fn)+F11)
• Ir na guia Ferramentas
• Referências…
• Marcar a caixinha correspondente ao “Microsoft ActiveX Data Objects 6.1” (como na foto abaixo), ou a versão mais recente
• OK

Para criar um código dentro de um módulo, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.

Como não temos nenhum módulo destinado ao tema da aula de hoje, e por questão de organização, vamos criar um módulo à parte.

Vamos na guia Inserir > Módulo, e uma caixa branca se abrirá.

Mudaremos a sua propriedade name para “modConectarAccess” e colocaremos a seguinte function dentro dele:

Function conectarAccess(conexaoBanco As ADODB.Connection)

Dim textoConexao As String, caminhoBanco As String
 
caminhoBanco = ThisWorkbook.Path & "BancoDeDadosVBA.accdb"
'caminhoBanco = "C:UsersdamorOneDriveÁrea de TrabalhoBancoDeDadosVBA.accdb"

textoConexao = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & caminhoBanco & ";" & _
                "Persist Security Info=False;"

conexaoBanco.Open (textoConexao)

End Function

Explicação do código:

• Vamos utilizar de uma function com um argumento específico: uma variável, batizada de “conexaoBanco”, que vai representar uma conexão da biblioteca ADODB (abreviação para AdobeX Data Objects Database)
• Dimensionar:
  • a variável “textoConexao” como uma String (texto)
  • a variável “caminhoBanco” como uma String (texto)
• A variável “caminhoBanco” vai receber o resultado da combinação do comando Path (que vai retornar o caminho da pasta da planilha que contém o código) e do nome do arquivo do banco de dados (extensão .accdb – Access database). Portanto, ela armazenará o caminho completo do banco de dados
• A variável “textoConexao” vai receber a junção dos textos:
  • “Provider=Microsoft.ACE.OLEDB.12.0;”Data Source=”
  • O texto resultante da variável “caminhoBanco”
  • “;Persist Security Info=False;”
• No final da function vai ser invocada de fato a conexão do VBA/Excel com o banco de dados presente no Access mencionado no caminho no meio do código
• Se encerra a function

Atualização dos dados

Já criamos a function que é responsável pela conexão com o nosso banco de dados. Precisamos agora, criar um outro código que vai de fato buscar e pegar os dados do banco de dados.

Ele vai usar a function criada anteriormente como auxiliar.

Então, para criar esse outro código precisaremos inserir mais um módulo, como mostrado nas etapas anteriores.

Mudaremos a propriedade name do novo módulo inserido para “modInteracaoAccess” e colocaremos a seguinte sub dentro dele:

Sub atualizarPlanilha()


Dim conexaoBanco As ADODB.Connection
Dim entradaBanco As ADODB.Recordset

Set conexaoBanco = New ADODB.Connection
Set entradaBanco = New ADODB.Recordset

conectarAccess conexaoBanco

entradaBanco.Open "CadastroFuncionarios", conexaoBanco, adOpenKeyset, adLockOptimistic

Range("A2:F1000000").ClearContents
Range("A2").CopyFromRecordset entradaBanco

conexaoBanco.Close

End Sub

Explicação do código:

• Dimensionar:
  • a variável “conexaoBanco” como uma conexão da biblioteca ADODB (abreviação para AdobeX Data Objects Database)
  • a variável “entradaBanco” como um conjunto de registros da biblioteca ADODB (abreviação para AdobeX Data Objects Database)
• Vamos configurar a “conexaoBanco” para representar uma nova conexão da biblioteca ADODB, através do comando Set VBA
• Vamos configurar a “entradaBanco” para representar um conjunto de registros, que é um elemento da biblioteca ADODB também, através do comando Set VBA
• Chamar a function “conectarAccess” criada anteriormente, passando como argumento pra ela a variável “conexaoBanco”. Rodando a function, ela vai entender que teremos que entrar no banco de dados passado pra ela, no caminho especificado
• Após isso, vamos de fato abrir o banco de dados, considerando os argumentos:
  • de nome “CadastroFuncionarios”, dentro do arquivo que contém o banco de dados (extensão .accdb), que vai ser a nossa fonte de informações
  • que estará dentro da conexaoBanco, que é a nossa conexão, de fato
  • da forma adOpenKeyset, que vai ser a forma de pegar informações, sem ver uma possível exclusão por outra pessoa que manipula o banco de dados
  • e adLockOptimistic, que é o tipo de bloqueio da nossa entrada (para edições simultâneas) que vai assumir que ninguém vai estar atualizando o banco de dados no momento, assumindo que poucas pessoas ou só você o modifica
• Vamos, em seguida, excluir as informações da planilha, considerando o intervalo entre A2 e F1000000
• Pegar/copiar tudo que está no conjunto de registros (Recordset) vindo de “entradaBanco”, e vai colocar na célula A2 da planilha
• Fechar/desativar a conexão com o banco de dados (desativar a variável “conexaoBanco”)
• Se encerra o código

Disparar macro ao abrir a planilha: evento de abertura

Para fazer com que a nossa macro rode automaticamente ao abrir a planilha, deveremos utilizar de um evento no VBA, mais especificamente falando de um evento de abertura da planilha.

Um evento nada mais é do que um acontecimento que vai fazer alguma(s) ação(ões) disparar: o nosso código.

Com o VBA aberto, teremos que programar um evento Workbook_Open da forma mostrada abaixo, clicando duas vezes no menu à esquerda em “EstaPastaDeTrabalho”, e colocando o seguinte código:

Private Sub Workbook_Open()

Call atualizarPlanilha

End Sub

Explicação do código acima:

• O código acima diz respeito ao evento de abertura da planilha. Quando ela fora aberta, esse código irá ser disparado
• Vamos chamar a macro criada anteriormente, a atualizarPlanilha
• Se encerra o código

Nessa aula você aprendeu a lidar com:

• Macros no VBA
• Tipos de dados no VBA
• Integração de banco de dados com VBA
• Conectar Excel com banco de dados
• Evento de abertura

Voltar ao índice

2 – Integração com Access

Na aula de hoje: banco de dados com VBA parte 2. Vamos finalizar a integração com o VBA e fazer os testes com o formulário!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:

Fala, Impressionadores! Hoje nós vamos dar continuidade a aula de integração de banco de dados com VBA (integração do Access com VBA).

Nessa aula nós vamos criar algumas macros VBA para poder cadastrar, editar e excluir informações no banco de dados.

Na aula anterior nós já fizemos a conexão com banco de dados, mas para essas ações nós temos que fazer essa conexão para cada uma delas.Então vamos criar uma macro para cada uma das ações e vamos atribuir essas macros ao formulário no VBA.

Lembrando que não vamos ensinar como criar esse formulário, pois já ensinamos aqui no canal e na descrição temos um minicurso de formulários caso queira aprender!

Agora bora para aula finalizar a integração com banco de dados e conseguir fazer as 3 ações de excluir, editar e cadastrar informações no banco de dados?

Banco de Dados – Exemplo

Temos um arquivo que armazena os dados de cadastro de funcionários de uma empresa.Ele foi disponibilizado para download junto com os arquivos de aula. Veja abaixo ele aberto:

Acessando os módulos

Para criar um código dentro de um módulo, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.

Já criamos na aula passada (Banco de Dados com VBA Parte 1 – Integração com Access) módulo destinado ao tema da aula de hoje. Portanto, vamos apenas acessá-lo. Veja abaixo os módulos já criados:

Vamos lidar nessa aula com o módulo “modInteracaoAccess”, adicionando novas “subs”, mostradas abaixo. Acompanhe cada sub e sua respectiva explicação.

Sub incluirBanco()

Dim conexaoBanco As ADODB.Connection
Dim entradaBanco As ADODB.Recordset

Set conexaoBanco = New ADODB.Connection
Set entradaBanco = New ADODB.Recordset

conectarAccess conexaoBanco

entradaBanco.Open "CadastroFuncionarios", conexaoBanco, adOpenKeyset, adLockOptimistic
 
entradaBanco.AddNew

entradaBanco.Fields("Nome").Value = userform_cadastro.caixatexto_nome.Value

If userform_cadastro.botaoopcao_feminino.Value = True Then
    entradaBanco.Fields("Gênero").Value = "Feminino"

Else
    entradaBanco.Fields("Gênero").Value = "Masculino"

End If

entradaBanco.Fields("Área").Value = userform_cadastro.caixacomb_area.Value

entradaBanco.Fields("CPF").Value = Format(userform_cadastro.caixatexto_cpf.Value, "000"".""000"".""000-00")

entradaBanco.Fields("Salário").Value = userform_cadastro.caixatexto_salario.Value

entradaBanco.Update

conexaoBanco.Close

Call atualizarPlanilha

End Sub

  Explicação do código acima:

• Dimensionar:
  • a variável “conexaoBanco” como uma conexão da biblioteca ADODB (abreviação para AdobeX Data Objects Database)
  • a variável “entradaBanco” como um conjunto de registros da biblioteca ADODB (abreviação para AdobeX Data Objects Database)
• Vamos configurar a “conexaoBanco” para representar uma nova conexão da biblioteca ADODB, através do comando Set VBA
• Vamos configurar a “entradaBanco” para representar um conjunto de registros, que é um elemento da biblioteca ADODB também, através do comando Set VBA
• Chamar a function “conectarAccess” criada na aula passada, passando como argumento para ela a variável “conexaoBanco”. Rodando a function, ela vai entender que teremos que entrar no banco de dados passado para ela, no caminho especificado
• Após isso, vamos de fato abrir o banco de dados, considerando os argumentos:
  • de nome “CadastroFuncionarios”, dentro do arquivo que contém o banco de dados (extensão .accdb), que vai ser a nossa fonte de informações
  • que estará dentro da conexaoBanco, que é a nossa conexão, de fato
  • da forma adOpenKeyset, que vai ser a forma de pegar informações, sem ver uma possível exclusão por outra pessoa que manipula o banco de dados
  • e adLockOptimistic, que é o tipo de bloqueio da nossa entrada (para edições simultâneas) que vai assumir que ninguém vai estar atualizando o banco de dados no momento, assumindo que poucas pessoas ou só você o modifica
• Criar uma nova entrada no banco de dados (célula/linha)
• Preencher o campo “Nome” com o valor que está na “caixatexto” do nosso “userform_cadastro”
• Preencher o campo “Gênero” com o valor “Feminino” se o “botaoopcao_feminino” estiver selecionado (valor = “True”). Se não estiver selecionado (Valor = “False”), o campo vai ser preenchido com “Masculino”
• Preencher o campo “Área” com o valor da “caixacomb_area”
• Preencher o campo “CPF” com o valor da “caixatexto_cpf” formatado para o formato de CPF através da função Format
• Preencher o campo “Salário” com o valor da “caixatexto_salario”
• Atualizar a entradaBanco (conjunto de registros)
• Fechar/desativar a conexão com o banco de dados (desativar a variável “conexaoBanco”)
• Vamos chamar a macro criada na aula passada, a atualizarPlanilha, que vai atualizar a planilha de Excel com os dados presentes no banco de dados
• Se encerra o código

Sub editarBanco()

Dim conexaoBanco As ADODB.Connection
Dim entradaBanco As ADODB.Recordset
Dim textoTabela As String

Set conexaoBanco = New ADODB.Connection
Set entradaBanco = New ADODB.Recordset

conectarAccess conexaoBanco

textoTabela = "Select * From CadastroFuncionarios Where ID = " & CLng(userform_cadastro.caixatexto_id.Value)

entradaBanco.Open textoTabela, conexaoBanco, adOpenKeyset, adLockOptimistic

If userform_cadastro.caixatexto_nome.Value <> "" Then entradaBanco.Fields("Nome").Value = userform_cadastro.caixatexto_nome.Value

If userform_cadastro.botaoopcao_feminino.Value = True Then
    entradaBanco.Fields("Gênero").Value = "Feminino"

ElseIf userform_cadastro.botaoopcao_masculino.Value = True Then
    entradaBanco.Fields("Gênero").Value = "Masculino"

End If

If userform_cadastro.caixacomb_area.Value <> "" Then entradaBanco.Fields("Área").Value = userform_cadastro.caixacomb_area.Value

If userform_cadastro.caixatexto_cpf.Value <> "" Then entradaBanco.Fields("CPF").Value = Format(userform_cadastro.caixatexto_cpf.Value, "000"".""000"".""000-00")

If userform_cadastro.caixatexto_salario.Value <> "" Then entradaBanco.Fields("Salário").Value = userform_cadastro.caixatexto_salario.Value

entradaBanco.Update

conexaoBanco.Close

Call atualizarPlanilha

End Sub

Explicação do código acima:

 Dimensionar:

• a variável “conexaoBanco” como uma conexão da biblioteca ADODB (abreviação para AdobeX Data Objects Database)
• a variável “entradaBanco” como um conjunto de registros da biblioteca ADODB (abreviação para AdobeX Data Objects Database)
• a variável “textoTabela” como uma String (texto)

Sub excluirBanco()
 
Dim conexaoBanco As ADODB.Connection
Dim entradaBanco As ADODB.Recordset
Dim textoTabela As String

Set conexaoBanco = New ADODB.Connection
Set entradaBanco = New ADODB.Recordset

conectarAccess conexaoBanco

textoTabela = "Select * From CadastroFuncionarios Where ID = " & CLng(userform_cadastro.Value)

entradaBanco.Open textoTabela, conexaoBanco, adOpenKeyset, adLockOptimistic

entradaBanco.Delete
entradaBanco.Update
conexaoBanco.Close

Call atualizarPlanilha

End Sub

Explicação do código acima:

• Dimensionar:
  • a variável “conexaoBanco” como uma conexão da biblioteca ADODB (abreviação para AdobeX Data Objects Database)
  • a variável “entradaBanco” como um conjunto de registros da biblioteca ADODB (abreviação para AdobeX Data Objects Database)
  • a variável “textoTabela” como uma String (texto)
• Vamos configurar a “conexaoBanco” para representar uma nova conexão da biblioteca ADODB, através do comando Set VBA
• Vamos configurar a “entradaBanco” para representar um conjunto de registros, que é um elemento da biblioteca ADODB também, através do comando Set VBA
• A variável “textoTabela” vai receber o seguinte comando de linguagem SQL: selecionar todas as colunas da tabela “CadastroFuncionarios” em que o ID for igual ao que tiver na “caixatexto_id” do “userform_cadastro”
• Após isso, vamos de fato abrir o banco de dados, considerando os argumentos:
  • de nome “textoTabela”, dentro do arquivo que contém o banco de dados (extensão .accdb), que vai ser a nossa fonte de informações, mas apenas com a linha de ID desejada (configurada anteriormente)
  • que estará dentro da conexaoBanco, que é a nossa conexão, de fato
  • da forma adOpenKeyset, que vai ser a forma de pegar informações, sem ver uma possível exclusão por outra pessoa que manipula o banco de dados
  • e adLockOptimistic, que é o tipo de bloqueio da nossa entrada (para edições simultâneas) que vai assumir que ninguém vai estar atualizando o banco de dados no momento, assumindo que poucas pessoas ou só você o modifica
• Em seguida vamos deletar (Delete, em inglês) essa linha (registro) do banco de dados (conjunto de registros)
• Atualizar a entradaBanco (conjunto de registros)
• Fechar/desativar a conexão com o banco de dados (desativar a variável “conexaoBanco”)
• Vamos chamar a macro criada na aula passada, a atualizarPlanilha, que vai atualizar a planilha de Excel com os dados presentes no banco de dados
• Se encerra o código

Conclusão

Nessa aula eu te mostrei como integrar o banco de dados com VBA. Para isso nós utilizamos o VBA para fazer a integração com o Access, que é um banco de dados da Microsoft.

Isso é útil, pois o Excel em alguns casos não é o melhor local para o armazenamento de informações, principalmente quando temos muitas informações.

Então conseguir integrar o VBA com o banco de dados é algo incrível que pode facilitar muito o seu trabalho e deixar suas análises mais rápidas, pois nem sempre vai precisar estar com todos aqueles dados deixando seu processamento mais lento.

Nessa aula você aprendeu a lidar com:

• Macros no VBA
• Tipos de dados no VBA
• Integração de banco de dados com VBA
• Conectar Excel com banco de dados

Valeu, Impressionadores! Fique ligado no nosso canal do YouTube para mais conteúdo de VBA toda semana! Um abraço,

Voltar ao índice

Hashtag Treinamentos

Para acessar outras publicações de VBA, clique aqui!

Guilherme Restum

Expert em VBA e Power BI da Hashtag Treinamentos. Auxilia no suporte aos alunos da Hashtag Treinamentos e na criação de conteúdos para os que acompanham nossos canais.