O banco de dados Access é um sistema de gerenciamento de banco de dados relacional que efetivamente salva uma grande quantidade de dados de maneira organizada. Onde o Excel é uma ferramenta poderosa para transformar dados em informações significativas. No entanto, o Excel não pode armazenar muitos dados. Mas quando usamos o Excel e o Access juntos, o poder dessas ferramentas aumenta exponencialmente. Portanto, vamos aprender como conectar o banco de dados Access como uma fonte de dados ao Excel por meio do VBA.
Conectando o banco de dados do Access como fonte de dados do Excel
1: Adicionar referência ao objeto de dados AcitveX
Estaremos usando ADO para se conectar e acessar o banco de dados. Portanto, primeiro precisamos adicionar a referência ao objeto ADO.
Adicione um módulo ao seu projeto VBA e clique nas ferramentas. Clique aqui nas referências.
Agora procure o Microsoft ActiveX Data Object Library. Verifique a versão mais recente que você possui. Eu tenho 6,1. Clique no botão OK e pronto. Agora estamos prontos para criar um link para o banco de dados Access.
2. Escreva um código VBA para estabelecer uma conexão com o banco de dados Access
Para conectar o Excel a um banco de dados Access, você precisa ter um banco de dados Access. O nome do meu banco de dados é "Test Database.accdb ". É salvo em "C: \ Users \ Manish Singh \ Desktop" localização. Essas duas variáveis são importantes. Você precisará alterá-los de acordo com suas necessidades. O código de descanso pode ser mantido como está.
Copie o código abaixo para fazer seu módulo Excel VBA e faça as alterações de acordo com sua necessidade. Eu expliquei cada linha do código abaixo:
Sub ADO_Connection () 'Criando objetos de conexão e conjunto de registros Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, query As String 'Declarando o nome totalmente qualificado do banco de dados. Altere-o com a localização e o nome do seu banco de dados. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" 'Este é o provedor de conexão. Lembre-se disso em sua entrevista. PRVD = "Microsoft.ace.OLEDB.12.0;" 'Esta é a string de conexão que você exigirá ao abrir a conexão. connString = "Provedor =" & PRVD & "Fonte de dados =" & DBPATH 'abrindo a conexão conn.Open connString 'a consulta que desejo executar no banco de dados. query = "SELECT * from customerT;" 'executando a consulta na conexão aberta. Ele obterá todos os dados no gravando objeto. rec.Open query, conn 'limpar o conteúdo das células Cells.ClearContents 'obter dados do conjunto de registros, se houver, e imprimi-los na coluna A da planilha do Excel. If (rec.RecordCount 0) Then Do While Not rec.EOF Range ("A" & Cells (Rows.Count, 1) .End (xlUp) .Row) .Offset (1, 0) .Value2 = _ rec.Fields (1) .Value rec.MoveNext Loop End If 'fechando as conexões rec.Close conn.Close End Sub
Copie o código acima ou baixe o arquivo abaixo e faça as alterações no arquivo de acordo com seus requisitos.
Baixar arquivo: VBA Database LearningQuando você executa este código VBA, o Excel estabelece uma conexão com o banco de dados. Posteriormente, ele executará a consulta projetada. Irá limpar qualquer conteúdo antigo da planilha e preencherá a coluna A com os valores do Campo 1 (segundo campo) do banco de dados.
Como funciona essa conexão de banco de dados do VBA Access?
Dim conn como nova conexão, rec como novo conjunto de registros
Na linha acima, não estamos apenas declarando as variáveis de conexão e conjunto de registros, mas inicializando-as diretamente usando a palavra-chave New.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Essas duas linhas são concorrentes. O DBPATH mudará apenas com seu banco de dados. PRVD está se conectando ao provedor OLE DB.
conn.Open connString
Esta linha abre a conexão com o banco de dados. Abrir é a função do objeto de conexão que leva vários argumentos. O primeiro e necessário argumento é ConnectingString. Esta string contém o provedor OLE DB (aqui PRVD) e a fonte de dados (aqui DBPATH). Ele também pode usar admin e senha como argumentos opcionais para bancos de dados protegidos.
A sintaxe de Connection.Open é:
connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long = -1])
Como não tenho nenhum ID e senha em meu banco de dados, uso apenas ConnectionString. O formato de ConnectionString é "Provider =provedor_que deseja usar; Fonte de dados =nome totalmente qualificado do banco de dados". Fizemos e salvamos esta string emconnString variável.
query = "SELECT * from customerT;"
Esta é a consulta que desejo executar no banco de dados. Você pode ter qualquer dúvida que quiser.
rec.Open query, conn
Esta instrução executa a consulta definida na conexão definida. Aqui, estamos usando o método Open do objeto do conjunto de registros. Toda a saída é salva no objeto do conjunto de registrosgravando. Você pode recuperar, manipular ou excluir valores do objeto do conjunto de registros.
Cells.ClearContents
Esta linha limpa o conteúdo da planilha. Em outras palavras, exclui tudo das células da planilha.
If (rec.RecordCount 0) Then Do While Not rec.EOF Range ("A" & Cells (Rows.Count, 1) .End (xlUp) .Row) .Offset (1, 0) .Value2 = _ rec.Fields (1) .Value rec.MoveNext Loop End If
O conjunto de linhas acima verifica se o conjunto de registros está vazio ou não. Se o conjunto de registros não estiver vazio (significa que a consulta retornou alguns registros), o loop começa e começa a imprimir cada valor do campo 1 (segundo campo, primeiro nome neste caso) na última célula não utilizada da coluna.
(Isso é usado apenas para explicar. Você pode não ter essas linhas. Se você apenas deseja abrir uma conexão com o banco de dados, o código VBA acima dessas linhas é suficiente.)
Usamos rec.EOF para executar o loop até o final do conjunto de registros. O rec.MoveNext é usado para avançar para o próximo conjunto de registros. rec.Fields (1) é usado para obter valores do campo 1 (que é o segundo, já que sua indexação de campo começa em 0. Em meu banco de dados, o segundo campo é o Nome do cliente).
rec.Close conn.Close
Finalmente, quando todo o trabalho que queríamos do rec e da conexão estiver concluído, nós os fechamos.
Você pode ter essas linhas em uma sub-rotina separada se quiser abrir e fechar conexões específicas separadamente.
Então, sim pessoal, é assim que você estabelece uma conexão com o banco de dados ACCESS usando ADO. Existem outros métodos também, mas esta é a maneira mais fácil de se conectar a uma fonte de dados de acesso por meio do VBA. Eu expliquei o mais detalhadamente que pude. Deixe-me saber se isso foi útil na seção de comentários abaixo.
Artigos relacionados:
Use uma pasta de trabalho fechada como banco de dados (DAO) usando VBA no Microsoft Excel | Para usar uma pasta de trabalho fechada como banco de dados com conexão DAO, use este trecho de VBA no Excel.
Use uma pasta de trabalho fechada como um banco de dados (ADO) usando VBA no Microsoft Excel | Para usar uma pasta de trabalho fechada como um banco de dados com conexão ADO, use este trecho de VBA no Excel.
Introdução aos Formulários de Usuário do Excel VBA | Para inserir dados no banco de dados, usamos formulários. Os UserForms do Excel são úteis para obter informações do usuário. Aqui está como você deve começar com formulários de usuário do VBA.
Altere o valor / conteúdo de vários controles de formulário de usuário usando VBA no Excel | Para alterar o conteúdo dos controles do formulário do usuário, use este snippet VBA simples.
Evita que um formulário do usuário feche quando o usuário clica no botão x usando o VBA no Excel | Para evitar que o formulário do usuário feche quando o usuário clicar no botão x do formulário, usamos o evento UserForm_QueryClose.
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
A função VLOOKUP no Excel | Esta é uma das funções mais usadas e populares do Excel, que é usada para pesquisar valores em diferentes intervalos e planilhas.
CONT.SE no Excel 2016 | Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar um valor específico. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel | Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.