Baixe o app para aproveitar ainda mais
Prévia do material em texto
Access 2016 - Módulo II Access 2016 - Módulo II Créditos Access 2016 - Módulo II Coordenação Geral Marcia M. Rosa Coordenação Editorial Henrique Thomaz Bruscagin Autoria Luciana Silva Souza Este material constitui uma nova obra e é uma derivação da seguinte obra original, produzida por TechnoEdition Editora Ltda., em Nov/2013: Access – Módulo II Autoria: Luciana Silva Souza Revisão Ortográfica e Gramatical Fernanda Monteiro Laneri Diagramação Bruno de Oliveira Santos Edição nº 1 | 1778/0_WEB Março/ 2016 Copyright © MONTE EVEREST PARTICIPAÇÕES E EMPREENDIMENTOS LTDA. Todos os direitos autorais reservados. Este manual não pode ser copiado, fotocopiado, reproduzido, traduzido ou convertido em qualquer forma eletrônica, ou legível por qualquer meio, em parte ou no todo, sem a aprovação prévia, por escrito, da Monte Everest Participações e Empreendimentos Ltda., estando o contrafator sujeito a responder por crime de Violação de Direito Autoral, conforme o art.184 do Código Penal Brasileiro, além de responder por Perdas e Danos. Todos os logotipos e marcas utilizados neste material pertencem às suas respectivas empresas. “As marcas registradas e os nomes comerciais citados nesta obra, mesmo que não sejam assim identificados, pertencem aos seus respectivos proprietários nos termos das leis, convenções e diretrizes nacionais e internacionais.” Sumário Informações sobre o treinamento ....................................................................................... 09 Capítulo 1 - Consultas ............................................................................................................. 11 1.1. Consultas SQL em Access ................................................................12 1.2. Scripts em SQL ................................................................................14 1.2.1. Operadores lógicos: AND ou OR ......................................................17 1.2.2. Operadores para facilitar o script da consulta .................................17 1.3. Consultas União ..............................................................................19 1.4. Subconsultas...................................................................................21 1.5. Consultas Definição de dados .........................................................23 1.6. Consultas Passagem .......................................................................25 Pontos principais ................................................................................................34 Teste seus conhecimentos .................................................................................................... 35 Mãos à obra! .................................................................................................................................. 39 Capítulo 2 - Programação ........................................................................................................ 45 2.1. Programa ........................................................................................46 2.2. Linguagem ......................................................................................47 2.2.1. Principais linguagens de programação ............................................47 2.3. Visual Basic e VBA ...........................................................................48 2.4. Macros e VBA ..................................................................................49 2.4.1. Procedimentos ................................................................................50 2.4.2. Conversão de macros em VBA .........................................................52 2.5. Módulos ..........................................................................................54 2.5.1. Módulo padrão ................................................................................54 2.5.2. Módulo de classe ............................................................................54 2.5.3. Sintaxe do Access VBA ....................................................................55 2.6. Regras de nomenclatura do Visual Basic ..........................................56 2.7. Ambiente IDE ..................................................................................57 2.7.1. Principais janelas do IDE .................................................................58 2.7.2. Menu Depurar .................................................................................59 2.8. Operadores .....................................................................................60 Pontos principais ................................................................................................62 Teste seus conhecimentos .................................................................................................... 63 Capítulo 3 - Variáveis e constantes ................................................................................... 67 3.1. Variáveis .........................................................................................68 3.1.1. Nome ..............................................................................................70 3.1.2. Natureza .........................................................................................70 3.1.3. Tipos de variáveis ...........................................................................71 3.1.4. Escopo ............................................................................................79 3.2. Constantes ......................................................................................81 3.2.1. Constantes simbólicas ....................................................................82 3.2.2. Constantes intrínsecas ....................................................................82 3.2.3. Constantes definidas pelo sistema ..................................................83 Pontos principais ................................................................................................84 Teste seus conhecimentos .................................................................................................... 85 Mãos à obra! .................................................................................................................................. 89 6 Access 2016 - Módulo II Sumário Capítulo 4 - Estruturas de controle .................................................................................... 91 4.1. Estruturas de controle – Decisão .....................................................92 4.1.1. Lógica booleana ..............................................................................92 4.1.2. Instrução IF .....................................................................................94 4.1.3. Instrução SELECT ............................................................................98 4.2. Estruturas de controle – Repetição ..................................................101 4.2.1. Instrução DO...LOOP .......................................................................102 4.2.2. Instrução FOR...NEXT ......................................................................104 4.2.3. Instrução FOR EACH...NEXT .............................................................106 4.2.4. Instrução WITH…END WITH .............................................................107 Pontos principais ................................................................................................109 Teste seus conhecimentos .................................................................................................... 111 Mãos à obra! .................................................................................................................................. 115 Capítulo 5 - Matrizes ................................................................................................................ 123 5.1. Introdução ......................................................................................1245.2. Declaração de matrizes ...................................................................124 5.2.1. Declaração de uma matriz dinâmica ................................................127 5.3. Inicialização de matrizes .................................................................128 5.4. Leitura de matrizes .........................................................................129 Pontos principais ................................................................................................131 Teste seus conhecimentos .................................................................................................... 133 Mãos à obra! .................................................................................................................................. 137 Capítulo 6 - Funções internas e funções de usuário .................................................. 139 6.1. Introdução ......................................................................................140 6.2. Funções de comunicação ................................................................140 6.2.1. Função InputBox .............................................................................140 6.2.2. Função MsgBox ...............................................................................143 6.3. Funções de domínio agregado ........................................................146 6.4. Funções de conversão de tipos........................................................149 6.5. Funções de verificação ....................................................................150 6.6. Funções de texto ............................................................................152 6.6.1. Função Left .....................................................................................152 6.6.2. Função Right ...................................................................................152 6.6.3. Função Mid .....................................................................................153 6.6.4. Função Len .....................................................................................153 6.6.5. Função Str .......................................................................................154 6.6.6. Funções LTrim, RTrim e Trim ..........................................................154 6.7. Funções de data ..............................................................................155 6.7.1. Função Now ....................................................................................155 6.7.2. Função Date ....................................................................................155 6.7.3. Função Day .....................................................................................155 7 Access 2016 - Módulo II Sumário w 6.7.4. Função Month .................................................................................156 6.7.5. Função Year ....................................................................................156 6.7.6. Função Weekday .............................................................................156 6.7.7. Função WeekdayName .....................................................................157 6.8. Funções de hora .............................................................................158 6.8.1. Função Time ...................................................................................158 6.8.2. Função Second ................................................................................158 6.8.3. Função Minute ................................................................................158 6.8.4. Função Hour ...................................................................................159 6.8.5. Função Timer ..................................................................................159 6.8.6. Função DateDiff ..............................................................................159 6.9. Funções financeiras ........................................................................161 6.9.1. Função Pmt .....................................................................................161 6.9.2. Função Rate ....................................................................................162 6.9.3. Função NPer ....................................................................................162 6.9.4. Função PV .......................................................................................162 6.9.5. Função FV .......................................................................................163 6.10. Outras funções ...............................................................................163 6.10.1. Função Error ...................................................................................163 6.10.2. Função VarType ..............................................................................163 6.10.3. Função Shell ....................................................................................165 6.10.4. Função FileLen ................................................................................166 6.10.5. Função Format ................................................................................167 6.10.6. Funções Int e Fix .............................................................................168 6.10.7. Função QBColor ..............................................................................168 6.10.8. Função RGB .....................................................................................169 6.11. Funções do usuário – personalizadas ..............................................171 Pontos principais ................................................................................................173 Teste seus conhecimentos .................................................................................................... 175 Mãos à obra! .................................................................................................................................. 179 Capítulo 7 - Programação por eventos ............................................................................. 183 7.1. Introdução ......................................................................................184 7.2. Estrutura do Access VBA .................................................................184 7.3. Eventos ...........................................................................................188 7.4. Propriedades ...................................................................................194 7.4.1. Formulários e relatórios ..................................................................194 7.5. Métodos ..........................................................................................200 7.5.1. Métodos associados aos formulários ...............................................200 7.5.2. Método associado a relatórios .........................................................203 7.5.3. Métodos associados à coleção DoCmd ............................................205 Pontos principais ................................................................................................224 Teste seus conhecimentos .................................................................................................... 225 Mãos à obra! .................................................................................................................................. 231 8 Access 2016 - Módulo II Informações sobre este treinamento Capítulo 8 - Técnicas ADO ..................................................................................................... 239 8.1. Técnicas de acesso a banco de dados .............................................240 8.2. ADO, ODBC e OLEDB .......................................................................241 8.2.1. Conexão ADO .................................................................................2428.2.2. Objeto Field ....................................................................................249 8.2.3. Métodos de navegação ....................................................................249 8.2.4. Métodos de procura ........................................................................250 8.2.5. Edição de dados ..............................................................................252 Pontos principais ................................................................................................256 Teste seus conhecimentos .................................................................................................... 257 Mãos à obra! .................................................................................................................................. 261 Capítulo 9 - Recursos adicionais ......................................................................................... 265 9.1. Divisão de banco de dados ..............................................................266 9.2. Senha do arquivo e criptografia .......................................................268 9.3. Arquiv os somente para execução ...................................................270 9.4. Extensões de arquivo ......................................................................272 9.5. Especificações do Access ................................................................273 9.5.1. Bancos de dados – Gerais ................................................................273 9.5.2. Bancos de dados – Tabela ...............................................................274 9.5.3. Bancos de dados – Consulta ............................................................275 9.5.4. Bancos de dados – Formulário e relatório ........................................276 9.5.5. Bancos de dados – Macro ................................................................277 9.5.6. Projetos – Formulário e relatório .....................................................277 9.5.7. Projetos – Macro .............................................................................278 9.6. Atalhos na programação .................................................................278 9.7. O que mudou nessa nova versão do Access ....................................279 Pontos principais ................................................................................................280 Teste seus conhecimentos .................................................................................................... 281 9 Access 2016 - Módulo II Informações sobre este treinamento Para o melhor aproveitamento do curso Access 2016 – Módulo II, é imprescindível ter participado do curso Access 2016 – Módulo I, ou possuir conhecimentos equivalentes. 1 9 Consultas SQL em Access; 9 Scripts em SQL; 9 Consultas União; 9 Subconsultas; 9 Consultas Definição de dados; 9 Consultas Passagem. Consultas 12 Access 2016 - Módulo II 1.1. Consultas SQL em Access SQL (Structured Query Language – Linguagem de Consulta Estruturada) é uma linguagem padronizada (ANSI) para consulta, atualização e gerenciamento de bancos de dados relacionais. A linguagem SQL pode ser usada para recuperar, classificar e filtrar dados específicos a serem extraídos do banco de dados. É possível usar as instruções SQL nos locais em que um nome de tabela, de consulta ou de campo possa ser aceito. Em meados de 1970, o pesquisador E. F. Codd, da IBM, elaborou uma teoria sobre a estruturação e a manipulação de informações em bancos compartilhados, que posteriormente se tornou a base para a criação de uma linguagem padrão para consulta aos bancos de dados. Em 1979, a Oracle Corporation (então Relational Software, Inc.) lançou a versão comercial dessa linguagem, sendo seguida por outras empresas da área. O SQL não é considerado uma linguagem de programação como o Visual Basic ou o Pascal por não possuir recursos para manuseio de tela, input e output para o operador e estruturas elaboradas para controle do fluxo. O principal objetivo do SQL é permitir o acesso às bases de dados por meio de consultas (query) interativas. A linguagem SQL pode ser dividida em três sublinguagens: as DDLs (Data Definition Language), que efetuam a definição das variáveis, as DMLs (Data Manipulation Language), que efetuam a manipulação de dados com comandos específicos, e as DCLs (Data Control Language), que estabelecem o controle de acesso às bases de dados e à sua manipulação. O uso de comandos SQL é fundamental quando se trabalha com bancos de dados e, neste capítulo, veremos os tópicos principais dessa linguagem. 13 Consultas 1 Uma consulta SQL é criada com instruções SQL. Exemplos de consultas específicas em SQL são as consultas União, Definição de dados e Subconsulta. A consulta Passagem também é escrita em SQL, porém, requer a ligação com um banco de dados externo. Tipos de consulta Instruções SQL Acréscimo INSERT INTO Exclusão DELETE Criar-Tabela SELECT...INTO Atualização UPDATE Criar uma tabela CREATE TABLE Alterar uma tabela ALTER TABLE Excluir uma tabela DROP TABLE Criar um índice CREATE INDEX Excluir um índice DROP INDEX Vejamos as definições dos exemplos de consulta: • Consulta União: Este tipo de consulta combina campos (colunas) de uma ou mais tabelas ou consultas em um campo ou coluna nos resultados da consulta; • Consulta Definição de dados: Este tipo de consulta cria ou altera objetos de bancos de dados, tais como tabelas do SQL Server ou do Access; • Subconsulta: Este tipo de consulta consiste em uma instrução SQL SELECT dentro de outra consulta Seleção ou Ação. As subconsultas podem ser utilizadas para: • Testar a existência de algum resultado da subconsulta (utilizando as palavras reservadas EXISTS ou NOT); • Localizar qualquer valor na consulta principal que seja igual a, maior ou menor que os valores retornados pela subconsulta (utilizando as palavras reservadas ANY, IN ou ALL); • Criar subconsultas dentro de subconsultas (subconsultas aninhadas). 14 Access 2016 - Módulo II • Consulta Passagem: Este tipo de consulta envia comandos diretamente para bancos de dados ODBC, como SQL Server, utilizando comandos que são aceitos pelo servidor. Tipicamente, o banco corporativo contém a totalidade das informações, mas o operador necessita de apenas uma informação resumida, ou uma seleção com poucas linhas. A consulta Passagem remete a query ao banco externo, que processa os cálculos e devolve a informação ou a listagem solicitada. Na rede trafegam apenas a consulta e o set de resultados. 1.2. Scripts em SQL Vamos criar, agora, uma consulta via SQL. Usaremos sempre o arquivo Formularios_Treinamento_VBA_Base.accdb. 1. Na guia Criar, escolha a opção Design da Consulta e feche a janela Mostrar tabela. Na guia Design, em Modo de Exibição, escolha Modo SQL: Você também pode utilizar teclas de atalho na consulta SQL. Para aparecer a folha de dados, use CTRL + , (vírgula). Para aparecer o editor SQL, use CTRL + . (ponto). 2. Digite o seguinte: SELECT CodigoDoCliente, NomeDoContato, NomeDaEmpresa, Pais FROM tbl_Clientes; Algumas siglas de consulta e arquivamento foram convencionadas pelos especialistas em SQL, por exemplo: tbl para tabelas, qry para consultas etc. 15 Consultas 1 A seguir, apresentamos alguns comandos usados em consultas: • SELECT: Instrui o mecanismo de banco de dados Microsoft Jet a retornar as informações do banco de dados como um conjunto de registros; • * (asterisco): Indica a utilização de todos os campos da tabela; • ALL: Adotado quando não é incluído um dos atributos. O mecanismo de banco de dados Microsoft Jet seleciona todos os registros que atendam às condições na instrução SQL; • DISTINCT: Omite registros que contêm dados duplicados nos campos selecionados; • DISTINCTROW: Omite dados com base em registros duplicados completos e não somente em campos duplicados; • FROM: Especifica as tabelasou consultas que contêm os campos listados na instrução SELECT; • ORDER BY: Indica o critério de classificação, podendo conter um ou mais campos e ser crescente (ASC) ou decrescente (DESC); • TOP n PERCENT: Retorna certo percentual (sobre a quantidade total) de registros que devem ser retornados. A consulta deve estar classificada como ASC ou DESC; • TOP n: Indica certo número de registros que devem ser retornados. A consulta deve estar classificada como ASC ou DESC. Vamos ver um exemplo de consulta para exibir todas as colunas: SELECT * FROM tbl_Produtos; Se a consulta vier ordenada pela chave primária, mas você quiser ordenar por outro campo, utilize o comando ORDER BY seguido do nome do campo. Veja: SELECT * FROM tbl_Produtos ORDER BY NomeDoProduto ASC; A ordem pode ser ascendente (ASC) ou descendente (DESC). 16 Access 2016 - Módulo II Para ordenar mais de um campo, fazemos o seguinte: SELECT Pais, Cidade, NomeDoContato, Telefone FROM tbl_Clientes ORDER BY Pais, Cidade DESC, NomeDoContato; Para salvar a consulta, use a tecla de atalho F12. Agora veja um exemplo de consulta pela posição de colunas: SELECT Pais, Cidade, NomeDoContato, Telefone FROM tbl_Clientes ORDER BY 1, 2 DESC, 3; Se você quiser filtrar informações específicas, pode fazer uma consulta pelos cinco produtos mais caros da tabela Produtos, por exemplo: SELECT TOP 5 * FROM tbl_Produtos ORDER BY PrecoUnitario DESC; Agora vamos usar SELECT para filtrar 10% dos produtos mais caros (na tabela tem 78 skus = ~7 ou 8): SELECT TOP 10 PERCENT * FROM tbl_Produtos ORDER BY PrecoUnitario DESC; Você também pode usar SELECT para filtrar os produtos com preço maior do que 50,00 classificando pela coluna preço de forma descendente. Para isso, utilize WHERE: SELECT * FROM tbl_Produtos WHERE PrecoUnitario > 50 ORDER BY PrecoUnitario DESC; Vamos ver como usar SELECT utilizando menor ou igual. Suponha que você quer saber quais itens possuem até 21 unidades em estoque: SELECT * FROM tbl_Produtos WHERE UnidadesEmEstoque <= 21 ORDER BY UnidadesEmEstoque; Também é possível usar SELECT para filtrar todos os clientes, exceto, por exemplo, Espanha: SELECT * FROM tbl_Clientes WHERE Pais <> “Espanha” ORDER BY Pais; 17 Consultas 1 1.2.1. Operadores lógicos: AND ou OR Utilizamos um destes operadores quando é necessário especificar mais do que um critério, dessa forma ligamos um critério ao outro obedecendo a uma lógica: • AND: É utilizado quando todos os critérios devem ser obedecidos; • OR: É utilizado quando um ou mais critérios devem ser obedecidos. Suponha que você queira consultar, na tbl_Clientes, os registros dos países Brasil, Alemanha e Espanha: SELECT * FROM tbl_Clientes WHERE Pais=”Brasil” OR Pais=”Alemanha” OR Pais=”Espanha” ORDER BY Pais; Também é possível filtrar, na tbl_Pedidos, os pedidos com data entre 01/01/1997 e 31/01/1997: SELECT * FROM tbl_Pedidos WHERE DataDoPedido >= #01/01/1997# AND DataDoPedido <= #01/31/1997#; Agora, crie uma consulta exibindo os produtos com códigos da categoria 1 e 3 e com preço entre R$ 10,00 e R$ 50,00, usando a tabela tbl_Produtos: SELECT * FROM tbl_Produtos WHERE (CodigoDaCategoria=1 OR Codigo DaCategoria=3) AND PrecoUnitario>=10 AND PrecoUnitario<=50 ORDER BY CodigoDaCategoria, PrecoUnitario; Lembre-se de utilizar os parênteses para isolar a operação. 1.2.2. Operadores para facilitar o script da consulta Vamos conhecer, agora, os operadores que facilitam o script da consulta: • Operador IN: É utilizado para filtrar critérios com a mesma lógica do operador OR: SELECT * FROM tbl_Clientes WHERE Pais IN (“Brasil”, “EUA”, “Espanha”, “Alemanha”) ORDER BY Pais; 18 Access 2016 - Módulo II • Operador BETWEEN: É utilizado para filtrar itens em um determinado intervalo: SELECT * FROM tbl_Pedidos WHERE DataDoPedido BETWEEN #01/01/1997# AND #12/31/1997#; SELECT * FROM tbl_Produtos WHERE UnidadesEmEstoque BETWEEN 10 AND 30 ORDER BY UnidadesEmEstoque; • Operador NOT: Para inverter a operação, podemos utilizar o NOT antes do operador. Por exemplo: NOT BETWEEN, NOT IN etc.; • Operador LIKE: Para opções que envolvam texto, é melhor usar o operador LIKE ou o NOT LIKE. Veja o exemplo: • Para filtrar tudo que começa com a letra A, use: LIKE “a*”; • Para filtrar tudo que termina com a letra A, use: LIKE “*a”; • Para filtrar tudo que contém a palavra ANA, use: LIKE “*ANA*”. Para substituir um caractere em uma string é necessário utilizar ? (interrogação): SELECT * FROM tbl_Clientes WHERE Cidade LIKE “S?o Paulo”; SELECT * FROM tbl_Clientes WHERE NomeDoContato LIKE “M?ri? *”; • Campo Virtual: Mais conhecido como campo calculado. Ele só existirá na seguinte consulta: SELECT CodigoDoFuncionario, Nome &” “&Sobrenome AS [Nome Completo], Cargo FROM tbl_Funcionarios; Para apelidar um campo, utilizamos o comando AS (Alias). É necessário colocar o nome do Alias entre colchetes caso ele seja composto ou contenha espaços: SELECT tbl_DetalhesDoPedido.*, (Quantidade*PrecoUnitario)*(1- Desconto) AS [Preço Total] FROM tbl_DetalhesDoPedido; 19 Consultas 1 1.3. Consultas União As consultas União combinam campos de duas ou mais tabelas ou consultas Seleção em uma só consulta. Diferente da consulta Acréscimo, que necessita que todos os campos a serem acrescentados tenham o mesmo tamanho e o mesmo tipo de dados, a consulta União não checa o tamanho dos campos e o tipo de dados, porém a quantidade de colunas deve ser igual para todos os SELECTS do script. Vamos ao exemplo: 1. Na guia Criar, clique no Design da Consulta e escolha, no grupo Tipo de Consulta, a opção União ( ). Essa cláusula diferencia uma consulta Seleção de uma consulta União. Ao criar uma consulta União, são combinados os resultados de duas ou mais consultas ou tabelas independentes. Por padrão, nenhum registro duplicado é retornado quando é usada uma operação UNION, entretanto, o predicado ALL assegura o retorno de todos os registros. Em uma operação UNION, todas as consultas precisam solicitar o mesmo número de campos, porém, os campos não precisam ter o mesmo tamanho nem o mesmo tipo de dados; 2. Agora vamos montar uma lista de convidados. Para isso, vamos montar uma lista de contatos contendo um campo chamado Convidados. Nesse campo, serão unidas as informações das tabelas tbl_Clientes, tbl_Funcionarios e tbl_ Fornecedores: SELECT NomedoContato AS Convidados, NomeDaEmpresa, Telefone, Pais FROM tbl_Clientes UNION SELECT Nome&” “&Sobrenome, “”, TelefoneResidencial, Pais FROM tbl_Funcionarios UNION SELECT NomeDoContato, NomeDaEmpresa, Telefone, Pais FROM tbl_ Fornecedores; 20 Access 2016 - Módulo II 3. Vamos facilitar a identificação dos registros informando quem é cliente, quem é funcionário e quem é fornecedor por meio de um campo virtual. Veja: SELECT “Cliente” AS Tipo, NomedoContato AS Convidados, NomeDaEmpresa, Telefone, Pais FROM tbl_Clientes UNION SELECT “Funcionarios”, Nome&” “&Sobrenome, “”, TelefoneResidencial, Pais FROM tbl_Funcionarios UNION SELECT “Fornecedores”, NomeDoContato, NomeDaEmpresa, Telefone, Pais FROM tbl_Fornecedores; 4. Agora vamos filtrar somente os clientes do Brasil e fornecedores dos EUA: SELECT “Cliente” AS Tipo, NomedoContato AS Convidados, NomeDaEmpresa, Telefone, Pais FROM tbl_Clientes WHERE Pais=”Brasil” UNION SELECT “Funcionarios”, Nome&” “&Sobrenome, “”, TelefoneResidencial, Pais FROM tbl_Funcionarios UNION SELECT “Fornecedores”, NomeDoContato, NomeDaEmpresa, Telefone, Pais FROM tbl_Fornecedores WHERE Pais=”EUA”; 5. Classifique a consulta de forma ascendente pelos campos Tipo, País e Nome do Contato: SELECT “Cliente” AS Tipo, NomedoContato AS Convidados, NomeDaEmpresa, Telefone, Pais FROM tbl_Clientes WHERE Pais=”Brasil” UNION SELECT “Funcionarios”, Nome&” “&Sobrenome, “Não Cadastrado”, TelefoneResidencial, Pais FROM tbl_Funcionarios UNION SELECT “Fornecedores”, NomeDoContato, NomeDaEmpresa, Telefone, Pais FROM tbl_Fornecedores WHERE Pais=”EUA” ORDER BY 1,5,2; 6. Você pode unirduas tabelas com dados duplicados: SELECT Nome & “ “ & Sobrenome AS [Nome Completo], CodigoDoFuncionario, Cargo, Pais FROM tbl_Funcionarios UNION SELECT Nome & “ “ & Sobrenome, CodigoDoFuncionario, Cargo, Pais FROM tbl_Colaboradores; 21 Consultas 1 7. Usando UNION ALL, vamos trazer todos os registros, mesmo se estiverem duplicados: SELECT Nome & “ “ & Sobrenome AS [Nome Completo], CodigoDoFuncionario, Cargo, Pais FROM tbl_Funcionarios UNION ALL SELECT Nome & “ “ & Sobrenome, CodigoDoFuncionario, Cargo, Pais FROM tbl_Colaboradores ORDER BY 1 DESC; 1.4. Subconsultas Uma Subconsulta é uma instrução SELECT aninhada em outra instrução SELECT ou SELECT...INTO ou INSERT...INTO ou DELETE ou UPDATE ou em outra subconsulta. Um campo comum a ambas as consultas permite ao Access selecionar aquelas que correspondem aos critérios impostos. Uma subconsulta não precisa utilizar duas tabelas vinculadas, podendo operar com tabelas de bancos diferentes (se vinculadas no banco em uso). Vamos conhecer algumas funções agregadas para auxiliar na criação da subconsulta: • SUM: Soma; • AVG: Média; • MAX: Maior valor; • MIN: Menor valor; • COUNT: Contagem de registros. Vejamos um exemplo com a função agregada AVG: SELECT AVG(PrecoUnitario) AS Preço_Médio FROM tbl_Produtos; O resultado é apenas um valor numérico. 22 Access 2016 - Módulo II Agora vamos ver um exemplo de uma subconsulta para listar os produtos que estão com preços acima do preço médio: SELECT * FROM tbl_Produtos WHERE PrecoUnitario > (SELECT AVG(PrecoUnitario) FROM tbl_Produtos) ORDER BY PrecoUnitario; Neste outro exemplo, vamos listar os produtos em que o campo UnidadesEmEstoque é igual à menor quantidade em estoque: SELECT * FROM tbl_Produtos WHERE UnidadesEmEstoque = (SELECT MIN(UnidadesEmEstoque) FROM tbl_Produtos); Agora, suponha que você queira encontrar, na tabela de clientes, todos os clientes que efetuaram pedidos em um determinado dia, por exemplo, 01/01/1997. Não podemos esquecer que a data do pedido não está na tabela de clientes e sim na tabela de pedidos, assim, uma maneira de resolver esse filtro é por meio da subconsulta: SELECT * FROM tbl_Clientes WHERE CodigoDoCliente IN (SELECT CodigoDoCliente FROM tbl_Pedidos WHERE DataDoPedido = #01/01/1997#); Em seguida vamos listar os clientes que não têm pedidos: SELECT * FROM tbl_Clientes WHERE CodigoDoCliente NOT IN (SELECT CodigoDoCliente FROM tbl_Pedidos); Agora vamos listar os produtos que foram vendidos no dia 01/01/1997. Lembre-se que a tabela de produtos não possui ligação com a tabela de pedidos, portanto devemos nos referenciar à tabela tbl_DetalhesDoPedido, assim será necessário criar uma subconsulta com dois níveis: SELECT * FROM tbl_Produtos WHERE CodigoDoProduto IN (SELECT Cod igoDoProduto FROM tbl_DetalhesDoPedido WHERE NumeroDoPedido IN (SELECT NumeroDoPedido FROM tbl_Pedidos WHERE DataDoPedido = #01/01/1997#)); 23 Consultas 1 A seguir, apresentamos um consulta Parâmetro, ou seja, uma consulta em que a data de busca é flexível: SELECT * FROM tbl_Produtos WHERE CodigoDoProduto IN (SELECT CodigoDoProduto FROM tbl_DetalhesDoPedido WHERE NumeroDoPedido IN (SELECT NumeroDoPedido FROM tbl_Pedidos WHERE DataDoPedido = [Digite uma data])); 1.5. Consultas Definição de dados As consultas Definição permitem criar e modificar tabelas, além de incluir registros. As palavras-chave utilizadas são as seguintes: • CREATE TABLE: Cria uma tabela com campos, índices e chave primária. Veja a sintaxe: CREATE TABLE tabela (campo1 tipo [(tamanho)] [NOT NULL] [índice1] [, campo2 tipo [(tamanho)] [NOT NULL] [índice2] [, ...]) Nessa sintaxe, temos: • NOT NULL: Indica preenchimento obrigatório; • Índice n: Indica que o campo foi indexado e o nome do índice é a palavra digitada. Exemplo 1: CREATE TABLE Nomes ([NomeCliente] TEXT (25) NOT NULL, [SobrenomeCliente] TEXT (10)) Exemplo 2: CREATE TABLE OutrosNomes ([NomeCliente] TEXT (25) NOT NULL, [SobrenomeCliente] TEXT (10), [RG] INTEGER NOT NULL) 24 Access 2016 - Módulo II • ALTER TABLE: Modifica a estrutura de uma tabela depois desta ter sido criada com a instrução CREATE TABLE. Veja a sintaxe: ALTER TABLE tabela {ADD {COLUMN campo tipo[(tamanho)] [NOT NULL [CONSTRAINT índice] |CONSTRAINT índicedemulticampos} |DROP {COLUMN campo I CONSTRAINT nomedoíndice}} Nessa sintaxe, temos: • ADD COLUMN NomeDoCampo: Acrescenta um campo, com a sua natureza e, eventualmente, tamanho; • COLUMN NomeDoCampo Novas características: Modifica as características originais; • DROP COLUMN: Exclui um campo de uma tabela. Exemplo 1: ALTER TABLE Funcionários ADD COLUMN Observações TEXT(25) Exemplo 2: ALTER TABLE Funcionários ADD COLUMN Salário CURRENCY Exemplo 3: ALTER TABLE Funcionários DROP COLUMN Salário • CREATE INDEX: Cria um índice para um campo ou grupo de campos em uma tabela já existente. Esta é a sintaxe: CREATE [ UNIQUE ] INDEX índice ON tabela (campo [ASC|DESC][, campo [ASC|DESC], ...]) Nessa sintaxe, temos: • UNIQUE: Representa indexação sem repetição; • Índice: É o nome do índice; • ASC/DESC: Classificação crescente ou decrescente. 25 Consultas 1 Exemplo 1: CREATE INDEX FoneRamal ON Funcionários (TelefoneResidencial ASC, Ramal ASC); Exemplo 2: CREATE UNIQUE INDEX CódCliente ON Clientes (CódigoDoCliente) • DROP: Utilizada sem a instrução ALTER TABLE, exclui uma tabela existente de um banco de dados ou um índice existente de uma tabela. Esta é a sintaxe: DROP {TABLE tabela | INDEX índice ON tabela} Exemplo 1: DROP INDEX CódCliente ON Clientes Exemplo 2: DROP TABLE Clientes 1.6. Consultas Passagem A consulta Passagem é específica do SQL, usada para enviar comandos diretamente para um servidor de banco de dados ODBC. Ao utilizar as consultas Passagem, são usadas diretamente as tabelas no servidor, em vez de o mecanismo de banco de dados Microsoft Jet processar os dados. Deve-se criar primeiramente a ligação com o banco de dados, para, em seguida, indicá-la na estrutura da consulta, na propriedade da conexão ODBC. As telas a seguir mostram os passos para a ligação entre o Access e um servidor SQL. Os nomes, paths, IDs e senhas são de uma sala de aula da Impacta. Se forem aplicados em outro local, esses dados devem ser revistos. Na janela de banco de dados do Access, devemos realizar o seguinte procedimento: 1. Clique na aba Criar; 2. Clique na subdivisão Consultas / Design da Consulta; 26 Access 2016 - Módulo II 3. Feche a janela Mostrar tabelas; 4. Clique na aba Design; 5. Clique na subdivisão Tipo de Consulta; 6. Clique no ícone de consulta Passagem: 7. Escolha, na Folha de Propriedades, o construtor da opção Sequência de conexão ODBC: 27 Consultas 1 8. Será exibida a seguinte tela: Essa primeira tela permite verificar se já existe uma conexão adequada e, se necessário, criar uma nova. Deve-se selecionar a divisão Fonte de dados de máquina. A lista com as conexões padrão e as criadas pelo usuário é mostrada. Para criar uma nova conexão, clique no botão Nova e a tela ilustrada a seguir será apresentada: 28 Access 2016 - Módulo II Escolha entre as opções para um possível trabalho em rede, com os dados obtidos. Dependendo do perfil do usuário, apenas uma das opções estará disponível. Neste contexto, o driver consiste em um programa que permite a tradução entre os dois bancos de dados utilizados. Observemos, na tela anterior, que há possibilidade de conexão com o Oracle, inclusive. Após selecionar a opção SQL Server, clique no botão Avançar. 29 Consultas 1 Na tela anterior, confirmamos o driver escolhido clicando em Concluir. Então, a seguinte tela será exibida: Os itens Nome e Descrição, exibidos da tela anterior, ficam a critério do operador, porém, o item Servidor deve corresponder ao que existe na instalação em uso. Esse item representa o nome na rede do computador em que está instalado o programa SQL Server. Após preencher as informações necessárias, clique em Avançar: 30 Access2016 - Módulo II Novamente, os itens apresentados nessa janela dependem das características específicas da instalação em uso pelo operador. Após realizar as escolhas necessárias, clique em Avançar e a seguinte tela será exibida: Nessa tela, o usuário deverá escolher o banco a ser utilizado na consulta. Em seguida, deve-se clicar em Avançar e a próxima tela será exibida: 31 Consultas 1 Nessa tela são mostrados os itens que dependem das necessidades de cada organização ou usuário. Ao clicar em Concluir, o usuário é direcionado à tela seguinte, que apresenta o resumo das especificações: Após conferir o resumo das especificações, você deve clicar no botão Testar fonte de dados. Dessa forma, a seguinte tela será exibida: 32 Access 2016 - Módulo II O insucesso no teste pode ocorrer em razão de problemas físicos na rede ou a informações incorretas sobre os nomes, senhas e locais. Se o teste for bem- sucedido, a conexão estará criada e nomeada. Vejamos, a seguir, a tela inicial da configuração, que está apresentando a nova conexão criada: Após clicar no botão OK da janela Selecionar fonte de dados, a consulta será executada pela primeira vez. Após breve pausa, o sistema apresentará uma pequena caixa de confirmação perguntando se o usuário deseja salvar a senha juntamente com a string de conexão (conjunto de comandos e argumentos utilizados para criá-la). Clique em Sim e observe que, na célula anteriormente vazia, serão escritos os elementos da conexão, conforme a imagem adiante: 33 Consultas 1 Vejamos como ficou a configuração na Sequência de conexão ODBC: Após a execução desses passos, a consulta utilizará automaticamente a conexão criada. Agora iremos digitar o script em SQL para poder verificar o resultado da consulta. Clique em Modo de Exibição de Folha de Dados ou em Executar: Veja o resultado: 34 Access 2016 - Módulo II Pontos principais Atente para os tópicos a seguir. Eles devem ser estudados com muita atenção, pois representam os pontos mais importantes do capítulo. • SQL é uma linguagem padronizada para consulta, atualização e gerenciamento de bancos de dados relacionais. A linguagem SQL pode ser usada para recuperar, classificar e filtrar dados específicos a serem extraídos do banco de dados; • O SQL não é considerado uma linguagem de programação como o Visual Basic ou o Pascal por não possuir recursos para manuseio de tela, input e output para o operador e estruturas elaboradas para controle do fluxo. O principal objetivo do SQL é permitir o acesso às bases de dados por meio de consultas (query) interativas; • Uma consulta SQL é criada com instruções SQL. Exemplos de consultas específicas em SQL são União, Definição de dados e Subconsulta. A consulta Passagem também é escrita em SQL, porém, requer a ligação com um banco de dados externo; • As consultas União combinam campos de duas ou mais tabelas ou consultas Seleção em uma só consulta; • As consultas Definição permitem criar e modificar tabelas, além de incluir registros; • Uma Subconsulta é uma instrução SELECT aninhada em outra instrução SELECT ou SELECT...INTO ou INSERT...INTO ou DELETE ou UPDATE ou em outra subconsulta; • A consulta Passagem é específica do SQL, usada para enviar comandos diretamente para um servidor de banco de dados ODBC. Ao utilizar as consultas Passagem, são utilizadas diretamente as tabelas no servidor, em vez de o mecanismo de banco de dados Microsoft Jet processar os dados. 1 Consultas Teste seus conhecimentos 36 Access 2016 - Módulo II 1. Qual das alternativas a seguir está correta com relação à instrução DROP? 2. Qual das alternativas a seguir está correta com relação à Subconsulta? ☐ a) Tem a função de excluir somente registros. ☐ b) Altera a estrutura da tabela. ☐ c) Exclui os registros que satisfaçam à cláusula WHERE. ☐ d) Exclui as propriedades modificadas, retornando-as para o padrão. ☐ e) Exclui o objeto tabela (propriedades e registros). ☐ a) Trata-se de uma consulta limitada por cláusulas WHERE. ☐ b) Trata-se de uma consulta que utiliza a instrução INTO. ☐ c) Trata-se de uma consulta que utiliza as instruções JOIN LEFT e JOIN RIGHT. ☐ d) Trata-se de uma instrução SELECT aninhada em uma instrução SELECT. ☐ e) Trata-se de uma instrução UPDATE aninhada em uma instrução SELECT. 37 Consultas 1 3. Qual das alternativas a seguir está correta com relação à consulta Passagem? 4. Qual das alternativas a seguir está correta com relação à linguagem SQL? ☐ a) Trata-se de uma consulta específica em SQL. ☐ b) Envia comandos para um banco de dados ODBC. ☐ c) Pesquisa as tabelas do servidor. ☐ d) Depende da conexão com o banco de dados. ☐ e) Todas as alternativas anteriores estão corretas. ☐ a) A linguagem SQL pode ser utilizada para a programação de interfaces com o operador. ☐ b) O principal objetivo da linguagem SQL é providenciar um método de acesso às bases de dados. ☐ c) Os comandos da linguagem SQL só podem ser utilizados em bancos de dados para microcomputadores. ☐ d) Os termos DDL, DML e DCL representam tipos de bancos de dados nos quais a linguagem SQL funciona. ☐ e) A linguagem SQL foi projetada em 1980 pela Burroughs International. 38 Access 2016 - Módulo II 5. Qual dos itens a seguir não pode ser utilizado com a instrução DROP? ☐ a) Uma tabela. ☐ b) Um index. ☐ c) Uma coluna. ☐ d) Registros filtrados pela cláusula WHERE. ☐ e) Nenhuma das alternativas anteriores está correta. 1 Consultas Mãos à obra! 40 Access 2016 - Módulo II Laboratório 1 A – Criando uma consulta usando um campo virtual 1. Selecione todos os campos da tbl_produtos e crie o campo virtual total que será PrecoUnitario*UnidadesEmEstoque; 2. Salve como qry_exercicio_01. B – Criando uma consulta e classificando alguns campos em ordem crescente 1. Selecione os campos NomeDaEmpresa, CodigoDoCliente, NomeDoContato, CargoDoContato, Cidade e Pais da tabela tbl_clientes e classifique por NomeDoContato, Cidade e Pais (todos em ordem crescente); 2. Salve como qry_exercicio_02. C – Criando uma consulta e filtrando resultados 1. Selecione todos os campos da tbl_clientes, filtre pelos clientes para que o campo CargoDoContato comece como representante e que o campo país seja Brasil; 2. Salve como qry_Exercicio_03. Laboratório 2 A – Criando consulta união simples 1. Crie uma consulta união com os campos CodigoDoFuncionario, Nome & “ “ & Sobrenome, Cargo, Supervisor das tabelas tbl_Funcionarios e tbl_ Colaboradores. Lembre-se de que os nomes dos campos das duas tabelas são iguais; 2. Salve como sql_Exercicio_Union. 41 Consultas 1 B – Criando consulta união utilizando WHERE 1. Crie uma consulta união com os campos NomeDoContato, NomeDaEmpresa, CargoDoContato, Cidade, Pais, Telefone das tabelas tbl_clientes e tbl_ Fornecedores; 2. Filtre somente os clientes do país Alemanha e os fornecedores do país EUA; 3. Salve como sql_Exercicio_Union_Where. Laboratório 3 A – Criando uma consulta do tipo Definição de dados 1. Crie a tabela tbl_compras com os campos cod_compra int (chave primária), cod_prod int not null, data_compra datetime not null, valor_compra currency not null; 2. Salve como qry_exerc_create_table. B – Adicionando campos à tabela 1. Adicione os campos quant_compra int not null e responsavel_compra text(80) na tbl_compras; 2. Salve como qry_exerc_add_column. C – Excluindo um campo da tabela 1. Exclua os campos data_compra e quant_compra da tbl_compras; 2. Salve como qry_exerc_drop_column. 42 Access 2016 - Módulo II Laboratório 4 A – Criando uma subconsulta com duas tabelas 1. Selecione todos os campos da tbl_funcionarios e filtre os funcionários que venderam no dia 01/01/1997 das tabelas tbl_funcionarios e tbl_pedidos. O campo em comum entre as tabelas é o codidoDoFuncionario; 2. Salve como sql_exercicio_subconsulta_01. B – Criando uma subconsulta utilizando cálculo 1. Selecione todos os campos da tbl_produtos e filtre os produtos que estãocom preços abaixo do preço médio utilizando o campo precoUnitario; 2. Salve como sql_exercicio_subconsulta_02. C – Criando subconsulta utilizando WHERE e BETWEEN 1. Selecione todos os campos da tbl_transportadoras e filtre as transportadoras que atenderam aos pedidos do mês de janeiro/1997 (campo dataDoPedido). Para isso utilize a tabela tbl_pedidos. Lembre-se de que os campos em comum são codigoDaTransportadora e via; 2. Salve como sql_exercicio_subconsulta_03. 43 Consultas 1 Laboratório 5 A – Criando um driver de conexão e uma consulta passagem 1. No arquivo DadosExternos_vazio.accdb, crie uma consulta Passagem e um driver de conexão com o SQL Server, seguindo as configurações adiante: • Nome do driver: Exercicio_Passagem; • Descrição: Driver SQL Server – Northwind; • Servidor: 121P10_INSTRUTOR\INSTRUTOR (pode ser usado outro nome de servidor); • Login: sa; • Senha: ****deixar em branco****; • Banco de dados: NORTHWIND. 2. Na consulta Passagem, utilize o driver de conexão Exercicio_Passagem que foi criado; 3. Nesta consulta você deve fazer um script em SQL selecionando todos os campos da tabela CUSTOMERS e filtrar somente os registros do campo Country = ‘Brazil’ ou ‘Brasil’; 4. Salve como sql_Exercicio_Passagem_WHERE. B – Criando uma consulta passagem com ordenação dos campos 1. Crie uma consulta Passagem, selecione todos os campos da tabela SUPPLIERS e ordene pelos campos city e country em ordem crescente; 2. Salve como sql_Exercicio_Passagem_ORDER_BY. 44 Access 2016 - Módulo II C – Criando uma consulta passagem utilizando WHERE e BETWEEN 1. Crie uma consulta passagem, selecione todos os campos da tabela Employees, filtre os funcionários cujo campo HIREDATE (data de contratação) seja o ano de 1992; 2. Salve como sql_Exercicio_Passagem_BETWEEN. 2 9 Programa; 9 Linguagem; 9 Visual Basic e VBA; 9 Macros e VBA; 9 Módulos; 9 Regras de nomenclatura do Visual Basic; 9 Ambiente IDE; 9 Operadores. Programação 46 Access 2016 - Módulo II 2.1. Programa Programa é um conjunto de palavras e de símbolos que indicam ao computador a sequência de operações que ele deve realizar. Atualmente, a palavra programa é utilizada para designar qualquer conjunto de comandos escrito com uma linguagem de computação e identifica sistemas complexos (ERP) ou extremamente específicos (driver). A Lei 9.609/98 (Lei de Software) define programa de computador como “a expressão de um conjunto organizado de instruções, em linguagem natural ou codificada, contida em suporte físico de qualquer natureza, de emprego necessário em máquinas automáticas de tratamento da informação, dispositivos, instrumentos ou equipamentos periféricos, baseados em técnica digital ou análoga, para fazê-los funcionar de modo e para fins determinados”. Historicamente, o conceito de programa surgiu antes do desenvolvimento do computador eletrônico, sendo aplicado para máquinas de tear por meio de cartões perfurados. Posteriormente, Herman Hollerith utilizou o que hoje conhecemos como processamento de dados para tabular uma pesquisa do censo, nos Estados Unidos. As primeiras máquinas que poderiam ser chamadas de computadores surgiram com Konrad Zuse por volta de 1935/1940. Esses equipamentos, e também os posteriores, foram preparados para a execução de tarefas por meio de ligações elétricas que eram ativadas ou desativadas. John von Neumann criou o conceito de programa residente na memória do computador, e as primeiras linguagens destinavam-se a indicar as operações aritméticas a serem realizadas. Essas linguagens não permitiam a criação de programas mais sofisticados por estarem associadas ao modo físico de operação do computador. Com o passar do tempo, foram criados os compiladores, programas encarregados de traduzir a escrita feita pelo programador para um conjunto de instruções que podiam ser executadas pelo equipamento, e, por volta de 1950, surgiu uma primeira linguagem escrita com palavras e símbolos, o Fortran (Formula Translation). 47 Programação 2 Um programa não se resume à escrita do código, necessitando de etapas anteriores e posteriores para o correto funcionamento. O planejamento e a documentação daquilo que deve ser executado também são elementos necessários no desenvolvimento de um programa. A criação de programas de computador, quando baseada em metodologias ou processos formalizados, é conhecida como Engenharia de Software. 2.2. Linguagem Uma linguagem de programação é um conjunto de regras, palavras e símbolos utilizados para expressar operações isoladas que, em conjunto, realizam uma determinada tarefa. Por meio da linguagem, o programador poderá indicar quais recursos de hardware deverão ser utilizados e como as informações serão tratadas. O programa também poderá interagir com o operador na forma de mensagens ou de perguntas a serem respondidas por escrito. O conjunto de palavras composto de acordo com essas regras é chamado de código-fonte de um software. O código-fonte é, depois, convertido para código de máquina que, por sua vez, é executado pelo processador do computador. O objetivo das atuais linguagens de programação é permitir maior produtividade aos programadores e, também, a utilização dos modernos dispositivos de processamento e de telecomunicações criados pela indústria de hardware. Na medida em que as linguagens permitem um desenvolvimento de programas utilizando regras, sintaxe e vocabulário que se aproximam da comunicação humana, elas são ditas de nível mais alto (por oposição às primeiras linguagens, que se destinavam apenas a indicar operações simples e eram escritas com termos mnemônicos curtos). Por exemplo, um sofisticado programa de CAD permite ao engenheiro ou arquiteto apenas indicar as características do edifício, circuito ou máquina, e ele próprio se encarrega dos cálculos e desenhos necessários. 2.2.1. Principais linguagens de programação Vejamos, a seguir, as principais linguagens de programação existentes: • Linguagens históricas: ALGOL, Assembly, B, BASIC, BCPL, Clipper, COBOL, CPL, Forth, Fortran, Icon, Modula, PL/I, Simula, Smalltalk, SNOBOL 4; 48 Access 2016 - Módulo II • Linguagens acadêmicas: Icon, Pascal, Portugol, Prolog, Haskell, Lisp, Logo, OCaml; • Linguagens proprietárias: ABAP, Ada, AWK, C#, Cω, COBOL, ColdFusion, Delphi, Eiffel, SQL, Visual Basic; • Linguagens não proprietárias: C, C++, Forth, Fortran, Lisp, Smalltalk; • Linguagens livres: Euphoria, Icon, Java, JavaScript, Lua, MUMPS, Perl, PHP, Portugol, Python, R, Ruby, Shell Script, Tcl, Unicon. 2.3. Visual Basic e VBA O Visual Basic foi desenvolvido pela Microsoft para a criação de aplicativos baseados no Windows e, atualmente, é integrante do pacote Visual Studio. Sua versão mais recente faz parte do pacote Visual Studio .NET e é voltada para aplicações .NET. A versão anterior, Visual Basic 6.0, alcançou enorme popularidade e ainda é muito utilizada. A linguagem VBA é visual, e uma parte da programação já está contida em objetos utilizados pelo programador como parte de seu código. Essa linguagem possui um ambiente gráfico de desenvolvimento chamado IDE (Integrated Development Environment), que facilita a construção da interface gráfica das aplicações (ou GUI – Graphical User Interface). Historicamente, o Visual Basic não permitia acesso a bancos de dados, sendo limitado à criação de programas de interação ou de cálculo. Com o sucesso da linguagem, entretanto, a Microsoft adicionou componentes para acesso a dados, oferecendo a possibilidade de criar programas que guardassem as informações calculadas ou obtidas por digitação do operador (tecnologias como DAO, RDO e ADO). Posteriormente, a fabricante adicionou a possibilidade de criação de controles ActiveX (basicamente, objetos já criados que podem ser utilizados na programação), tornando o Visual Basic visual e orientado a objetos. Existem várias linguagens derivadas do Visual Basic, entre as quais podemos citar estas: • VBScript:É a linguagem default (por definição) para Active Server Pages (ASP); 49 Programação 2 • Visual Basic .NET: É a nova versão do Visual Basic. Não é totalmente compatível com as versões anteriores, mas existe a possibilidade de converter códigos antigos; • Visual Basic for Applications (VBA): Permite a criação de macros e o desenvolvimento de programas no aplicativo anfitrião (host). Independentemente, esta linguagem não existe. Ela acompanha diversos aplicativos do conjunto Office e outros produtos, como Visio, WordPerfect Office 2002 e AutoCAD. 2.4. Macros e VBA No Access, é possível realizar muitas tarefas utilizando a interface do usuário, mas, em muitos outros programas de banco de dados, as mesmas tarefas necessitam de programação. A utilização de uma macro ou do Visual Basic for Applications dependerá, geralmente, daquilo que o usuário deseja fazer. As macros possibilitam a programação de operações relativamente simples, como abrir e fechar formulários e executar relatórios. Os detalhes (argumentos) para cada ação são exibidos na janela de construção das macros. Elas também permitem criar teclas de atalho globais e programar operações por evento, como na abertura do banco de dados. O VBA é mais vantajoso quando for necessário realizar estas ações: • Facilitar a manutenção do banco de dados: Embora possam automatizar o uso de um banco de dados, macros são objetos externos aos formulários e relatórios sobre os quais agem. Dessa maneira, a manutenção do banco torna-se trabalhosa. Por sua vez, os procedimentos de evento do VBA fazem parte do formulário ou do relatório, o que proporciona melhor gerenciamento do sistema; • Criar funções personalizadas: Por meio da programação VBA, é possível criar funções personalizadas que efetuem diversos cálculos ou ativem outras funções, internas. As funções personalizadas poderão ser utilizadas na interface do Access ou na própria programação; • Tratar mensagens de erro: Com VBA, é possível administrar possíveis erros graves de preenchimento cometidos pelo operador do sistema; 50 Access 2016 - Módulo II • Executar em outros aplicativos e no sistema: Com VBA, é possível comunicar-se com outro aplicativo ou efetuar operações que estão relacionadas com o sistema operacional. Tecnicamente, são utilizados recursos de automação (COM) ou intercâmbio dinâmico de dados (DDE) para comunicação com outros aplicativos Windows ou, ainda, bibliotecas de vínculo dinâmico (DLLs), no Windows; • Utilizar um registro de cada vez: O VBA é interessante quando utilizamos um registro de cada vez, percorrendo o banco de dados e realizando uma operação em cada registro; • Passar argumentos para os procedimentos do VBA quando eles já foram ativados. Automação é uma técnica para utilizar objetos de um determinado aplicativo a partir de outro. Nas versões anteriores, era chamada automação OLE. A técnica é um padrão de fabricação dos softwares e um recurso do modelo de objeto componente (COM – Component Object Model). DDE (Dynamic Data Exchange) é um dos antigos protocolos estabelecidos para intercâmbio de dados entre programas baseados no Microsoft Windows, e DLL (Dynamic-link Library, ou Biblioteca de Vínculo Dinâmico) é um conjunto de rotinas que podem ser chamadas de procedimentos do Visual Basic e que são carregadas e vinculadas ao aplicativo em tempo de execução. 2.4.1. Procedimentos De maneira geral, chama-se de procedimento uma sequência nomeada de instruções executadas como uma unidade. Um nome de procedimento é sempre definido dentro do módulo. Todo código a ser executado deve estar contido em um procedimento. 2.4.1.1. Procedimento Sub Também chamado de procedure, macro, rotina e sub-rotina, o procedimento Sub é uma série de instruções do VBA que executam ações e que são colocadas entre as instruções Sub e End Sub em um módulo (padrão ou de classe). 51 Programação 2 Um procedimento Sub pode utilizar argumentos, como constantes, variáveis ou expressões passadas por outro procedimento de chamada. Se a rotina não tiver argumentos, a instrução Sub deve ser acompanhada por parênteses vazios. Vejamos o exemplo a seguir: Sub PegarInformacoes() Dim Resp As String Resp = InputBox(“Qual é o seu nome?”, “Sistema de identificação”) If Resp = Empty Then MsgBox “Você não digitou um nome.”, , “Sistema de identificação” Else MsgBox “O seu nome é “ & Resp, , “Sistema de identificação” End If End Sub 2.4.1.2. Procedimento Function Também chamado de função do usuário ou personalizada, o procedimento Function é uma série de instruções do VBA colocadas entre as instruções Function e End Function dentro do módulo (padrão ou de classe). Um procedimento Function é semelhante a um procedimento Sub, mas uma função também pode retornar um valor para uma consulta ou fórmula. Um procedimento Function pode utilizar argumentos, como constantes, variáveis ou expressões, passados para ele por um procedimento que o ativou (chamou). Uma função calcula um valor que é atribuído ao seu nome (retorna um valor) e que pode ser utilizado em outras funções, rotinas ou fórmulas. No exemplo a seguir, o valor da temperatura em graus Celsius é calculado a partir do valor em graus Fahrenheit. Quando a função for chamada por uma rotina, os argumentos devem ser especificados. O resultado do cálculo é devolvido para a rotina que chamou a função que, em seguida, exibe-o em uma caixa de mensagem. Este tipo de procedimento será explicado com mais detalhes no capítulo sobre o procedimento Function. 52 Access 2016 - Módulo II Sub Principal() Dim Temp As Single Temp = InputBox(“Digite a temperatura em graus Farenheit”) MsgBox “A temperatura é “ & ConverterCelsius(Temp) & “ graus Celsius.” End Sub Function ConverterCelsius(GrausF) ConverterCelsius = (GrausF - 32) * 5 / 9 End Function Podemos observar que o resultado da função foi repassado para a rotina principal que ativou a função inicialmente. 2.4.2. Conversão de macros em VBA O Access pode converter macros automaticamente para procedimentos de evento ou módulos que executem ações equivalentes utilizando o código do VBA. As macros podem ser criadas para ativação direta por parte do usuário, ao aplicar um duplo-clique sobre seus nomes (não dependem de formulários, relatórios, botões ou outros objetos). Porém, usualmente, elas estarão ligadas aos objetos, sendo, então, ativadas por meio de eventos suportados por esses objetos. Se as macros forem independentes de formulários e relatórios, devemos seguir o procedimento adiante para convertê-las: 1. No painel de navegação, abra, no modo Design, uma macro existente. Na guia Design, grupo Ferramentas, clique no ícone Converter macros em Visual Basic, como é ilustrado na imagem a seguir: 53 Programação 2 2. Na janela Converter macro, marque as caixas de acordo com a necessidade e clique no botão Converter; O resultado obtido com esse procedimento será um aviso de finalização e um novo módulo, conforme a ilustração adiante: Caso as macros sejam internas a formulários e relatórios (ativadas por eventos), devemos utilizar o procedimento a seguir: 1. Abra o formulário ou o relatório no modo Design; 2. Abra, no modo Design, o formulário que contém as macros. Na superguia Ferramentas de Design de Formulários, localizada na guia Design, grupo Ferramentas, clique sobre o ícone Converter macros do formulário para Visual Basic; 3. Também é possível utilizar um procedimento alternativo. Para isso, selecione o objeto associado à macro, pressione o botão direito do mouse e selecione Propriedades. Na guia Eventos, clique sobre o nome da macro acoplada ao objeto. Em seguida, clique no Construtor (reticências), que, finalmente, abrirá a janela das macros. 54 Access 2016 - Módulo II 2.5. Módulos Um módulo é uma coleção de declarações e procedimentos do VBA que são armazenados conjuntamente, formando uma unidade. Você ativa um módulo clicando na guia Criar e escolhendo, no grupo Macro/Código,as opções Módulo (padrão) ou Módulo Classe: 2.5.1. Módulo padrão Módulo padrão é aquele no qual são escritos procedimentos Sub e Function a serem disponibilizados para outros procedimentos em todo o banco de dados. Os módulos padrão contêm procedimentos de uso geral, que não estão associados a nenhum outro objeto, e procedimentos utilizados com frequência, que podem ser executados a partir de qualquer local do banco de dados. Os módulos padrão estão listados em Módulos sob Objetos, na janela Banco de dados. Os módulos padrão, de formulário e de relatório também estão listados no Pesquisador de objetos. 2.5.2. Módulo de classe Módulos de classe são procedimentos Sub e Function públicos, que são escritos dentro de um módulo de classe, e que se tornam métodos personalizados do objeto. Os módulos de formulário e de relatório são módulos de classe e, frequentemente, contêm procedimentos de evento que são executados em resposta a um evento no formulário ou relatório correspondente a cada um deles. Quando é criado o primeiro procedimento de evento para um formulário ou relatório, o Access cria, automaticamente, um módulo de formulário ou relatório associado. O valor de qualquer variável ou constante que seja declarada ou que exista em um módulo de classe, sem um objeto associado, está disponível para uso somente enquanto o código escrito pelo programador estiver sendo executado e apenas a partir do objeto que está no formulário ou relatório. Os procedimentos nos nossos módulos de formulário e de relatório podem chamar outros procedimentos que tenhamos adicionado aos módulos padrão. 55 Programação 2 2.5.3. Sintaxe do Access VBA Na programação, o termo sintaxe significa a maneira correta de utilizar determinado método, instrução, função, identificando todos os seus elementos, explicando-os e exemplificando como são aplicados. Essas informações estão disponíveis por meio da Ajuda do Access que, por sua vez, é completada na tarefa de explicar pelo Pesquisador de Objetos, outro recurso da linguagem. Os exemplos a seguir mostram como interpretar os elementos de sintaxe mais usuais, utilizando algumas instruções de uso comum. 2.5.3.1. Instruções Simplificadamente, instruções são sentenças de programação, linhas físicas do código. Tecnicamente, são unidades sintaticamente completas que expressam um tipo de ação, declaração ou definição. Se a instrução for extensa, é possível utilizar o caractere de continuação de linha para continuar uma única linha lógica em uma segunda linha física. Para tanto, basta escolher o ponto de quebra da linha lógica, pressionar a barra de espaço, pressionar a tecla de sublinhado (underline) e, em seguida, pressionar CTRL + ENTER. 2.5.3.2. Instrução Option Compare Usada no nível de módulo para declarar o método de comparação padrão a ser usado quando dados de sequência forem comparados. Option Compare {Binary | Text | Database} Na sintaxe da instrução Option Compare, as chaves e a barra vertical indicam uma escolha obrigatória entre três itens. Especificamente, temos estes itens: • Database: Somente pode ser usado no Microsoft Access. Isso resulta em comparações de sequências, baseadas na ordem de classificação utilizada no idioma adotado pelo usuário e definida no painel de controle do sistema operacional Windows; 56 Access 2016 - Módulo II • Binary: Resulta em comparações de sequências de caracteres baseadas em uma ordem de classificação derivada das representações binárias internas dos caracteres. No Microsoft Windows, a ordem de classificação é determinada pela página de código. Observemos a ordenação a seguir: A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø • Text: Resulta em comparações de sequências baseadas em uma ordem de classificação de texto que não diferencia maiúsculas de minúsculas e é determinada pelo idioma definido no sistema operacional. Quando os mesmos caracteres são classificados por meio de Option Compare Text, a ordem de classificação de texto a seguir é produzida: (A=a) < (À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø) 2.5.3.3. Instrução Option Explicit Usada no nível de módulo para forçar a declaração explícita de todas as variáveis deste módulo. 2.5.3.4. Instrução Option Base {0 | 1} Usada no nível de módulo para declarar o limite inferior padrão para os subscritos de matriz, isto é, se a indexação se inicia por 0 ou por 1. Todas as instruções Option devem ser escritas no início do módulo, na área de declaração, antes de qualquer procedimento. 2.6. Regras de nomenclatura do Visual Basic Devemos respeitar as regras adiante ao nomear procedimentos, constantes, variáveis e argumentos em um módulo do Visual Basic: • Utilizar uma letra como primeiro caractere; • Não utilizar um espaço, ponto (.), ponto de exclamação (!) ou os caracteres @, &, $ e # no nome; 57 Programação 2 • O nome pode ter no máximo 255 caracteres; • Não utilizar nomes iguais às funções, instruções e métodos do Visual Basic; • Não repetir nomes no mesmo nível de escopo. 2.7. Ambiente IDE O ambiente IDE (de desenvolvimento integrado), ou Editor do Visual Basic, é o ambiente no qual escrevemos a programação, um novo código e novos procedimentos do Visual Basic for Applications. É nesse ambiente, também, que editamos os códigos e procedimentos já existentes. Neste ambiente, ficam registrados os procedimentos, separados em rotinas (sub...end sub), as funções (function...end function), os módulos de classe e os módulos padrão. O Editor do Visual Basic contém um conjunto de ferramentas de depuração completo para localizar problemas lógicos e de sintaxe e o tempo de execução em seu código. 58 Access 2016 - Módulo II Para chegar até o IDE, estando no ambiente do Access interativo, existem pelo menos três alternativas, a saber: • Digite o atalho ALT + F11; • Na janela principal do banco de dados, clique no objeto Módulo e, depois, no botão Novo; • No modo de utilização Design de um formulário ou relatório, selecione um dos objetos, abra a janela de propriedades desse objeto, selecione a subdivisão Eventos, selecione um dos eventos, clique no botão reticências (que é mostrado do lado direito da linha em que está associado o evento) e, no menu tríplice, clique em Construtor de código. Se o código já existir, a terceira alternativa é ligeiramente diversa: • No modo de utilização Design de um formulário ou relatório, selecione um dos objetos, abra a janela de propriedades desse objeto, selecione a subdivisão Eventos, selecione um dos eventos já criados (a frase Procedimento de evento) e clique no botão reticências que é mostrado no canto direito da linha à qual está associado o evento. 2.7.1. Principais janelas do IDE No IDE, por meio da opção de menu Exibir, há seis janelas que poderão ser abertas simultaneamente, mas é recomendável abrir somente as que serão utilizadas no momento e, em seguida, fechá-las. Todas as janelas são móveis e podem ter o seu tamanho modificado. Quatro delas são as mais costumeiramente utilizadas, a saber: • Janela de código: Janela na qual o código é escrito, certamente a mais utilizada. O código se comporta como um texto e aceita a navegação e vários atalhos utilizados no Word, como PgDn, seleção de uma palavra por duplo-clique, recortar e colar; • Janela de verificação imediata: A verificação imediata é utilizada pelo programador para testar os resultados dos programas escritos. Pode ser utilizada como uma saída alternativa para a impressora ou para a tela, ou como local para verificar o resultado de comandos isolados. O usuário final não trabalhará com esta janela; 59 Programação 2 • Janela de inspeção de variáveis: A inspeção permite ao programador acompanhar o processamento (funcionamento) do programa por meio do comportamento das variáveis, isto é, que conteúdos elas representam, qual a sua natureza, quando são inicializadas, onde são declaradas; • Janela de propriedades: As propriedades determinam o comportamentooperacional e o aspecto dos objetos. São encontradas no Access interativo e, na programação, recebem nomes na língua inglesa. 2.7.2. Menu Depurar A depuração é um recurso automático utilizado para encontrar erros no código escrito. Basicamente, temos três tipos de erros cometidos pelo programador, descritos a seguir: • Erros em tempo de compilação: Tempo de compilação é o tempo durante o qual o VBA prepara o código para execução. Os módulos são compilados automaticamente antes da execução e os erros ocorrem como resultado de uma construção incorreta, por exemplo, um IF sem END IF ou um DO sem LOOP, erros na escrita das palavras-chave, falta de separador, incompatibilidade de tipos, ausência de parênteses, número incorreto de argumentos em uma função; • Erros em tempo de execução: São erros que podem ser detectados somente quando um aplicativo está em execução, e que ocorrem depois de iniciada a execução do aplicativo, por exemplo, uma operação inválida, como divisão por zero; • Erros lógicos: Ocorrem quando o aplicativo não é executado conforme o esperado e produz resultados incorretos. Neste caso, temos um planejamento inadequado do programa. O programador deverá rever a lógica de construção, a ordem das operações e as fórmulas utilizadas nos cálculos. As opções Compilar, Depuração total e Adicionar inspeção de variáveis, contidas no IDE, permitem monitorar o processamento e auxiliam na localização dos possíveis erros. 60 Access 2016 - Módulo II 2.8. Operadores Operadores são palavras-chave ou símbolos utilizados para indicar uma operação aritmética, de comparação, de concatenação ou de lógica. Eles guardam precedência entre si, caso sejam escritos todos em uma mesma expressão. A ordem de precedência é a seguinte: 1. Parênteses; 2. Operadores aritméticos; 3. Operadores de comparação; 4. Operadores lógicos. • Operadores aritméticos: Operador Descrição ^ Indica uma exponenciação, como em 2^3, isto é, 23 ou 2 elevado à terceira potência. / e * Indicam, respectivamente, divisão e multiplicação. + e - Indicam, respectivamente, soma e subtração. Mod Indica o resto da divisão entre dois números. • Operadores de comparação: Operador Descrição = Indica igualdade. <> Indica desigualdade. > Indica que o valor à esquerda é maior do que o valor à direita. < Indica que o valor à esquerda é menor do que o valor à direita. >= Indica que o valor à esquerda é maior ou igual ao valor à direita. <= Indica que o valor à esquerda é menor ou igual ao valor à direita. 61 Programação 2 • Operadores lógicos: Operador Descrição NOT Indica a negação do elemento à direita. Usado para efetuar uma negação lógica em uma expressão. AND Indica comparações e operações booleanas. Usado para efetuar uma conjunção lógica em duas expressões. OR Indica comparações e operações booleanas. Usado para efetuar uma disjunção lógica em duas expressões. IS Usado para comparar duas variáveis de referência a objeto. LIKE Usado para comparar duas sequências de caracteres. • Operador de concatenação: Operador Descrição & Usado para forçar a concatenação de sequências de caracteres de duas expressões. O operador aritmético + (soma) causa o mesmo efeito em conteúdos de texto. Entretanto, é mais seguro utilizar o operador & (E comercial). 62 Access 2016 - Módulo II Pontos principais Atente para os tópicos a seguir. Eles devem ser estudados com muita atenção, pois representam os pontos mais importantes do capítulo. • Um programa é uma coleção de instruções que descrevem uma tarefa a ser realizada por um computador; • O ato de programar um computador é um processo interativo que consiste na criação ou alteração de código-fonte, seguida de testes, análise e posterior refinamento do resultado; • Uma linguagem de programação é um método padronizado para expressar instruções para um computador. É um conjunto de regras sintáticas e semânticas usadas para definir um programa de computador; • O Visual Basic foi desenvolvido pela Microsoft para a criação de aplicativos baseados no Windows. O Visual Basic for Applications (VBA) permite a criação de macros e o desenvolvimento de programas no aplicativo anfitrião (host), está integrado a todos os produtos da família de produtos Microsoft Office e também a outros produtos de terceiros, tais como Visio (agora pertencente à Microsoft), WordPerfect Office 2002 e AutoCAD; • O procedimento Sub, também chamado de procedure, macro, rotina e sub-rotina, é uma série de instruções do VBA colocadas entre as instruções Sub e End Sub, em um módulo (padrão ou de classe), que executam ações, mas não retornam um valor; • O procedimento Function, também chamado de função do usuário ou personalizada, é uma série de instruções do VBA colocadas entre as instruções Function e End Function no módulo (padrão ou de classe); • O Access pode converter macros automaticamente para procedimentos de evento ou módulos do Access que executem ações equivalentes utilizando o código do VBA; • Módulo padrão é um módulo no qual são escritos procedimentos Sub e Function a serem disponibilizados para outros procedimentos em todo o banco de dados. Módulos de classe são módulos associados aos objetos, como um formulário ou um relatório; • O IDE, Editor do Visual Basic, é o ambiente no qual a programação é escrita. Nesse ambiente, ficam registrados os procedimentos, separados em rotinas (sub...end sub), funções (function...end function), módulos de classe e módulos padrão; • Operadores são palavras-chave ou símbolos utilizados para indicar uma operação aritmética, de comparação, de concatenação ou de lógica. 2 Programação Teste seus conhecimentos 64 Access 2016 - Módulo II 1. Qual a alternativa correta para converter a macro em VBA? 2. Qual das seguintes alternativas é incorreta, com relação ao módulo padrão? ☐ a) Com a macro fechada, mas selecionada, na guia Design, clique no botão Converter Macros em VBA. ☐ b) Selecione o nome da macro no painel de navegação da guia Arquivo e escolha Salvar Objeto Como. Logo depois, escolha Salvar objeto como, alterando na janela que é apresentada de macro, para módulo. ☐ c) Com a macro aberta, na guia Design, clique no botão Converter Macros em VBA. ☐ d) As alternativas B e C estão corretas. ☐ e) Somente a alternativa C está correta. ☐ a) Está listado na janela Módulos. ☐ b) Contém procedimentos de uso geral. ☐ c) É um módulo no qual são escritos procedimentos Sub e Function. ☐ d) É uma coleção de declarações e procedimentos do VBA. ☐ e) É um objeto criado a partir da classe que contém sua definição. 65 Programação 2 3. Quando criamos um procedimento sub no módulo de classe, que está em um objeto formulário, como esse procedimento naturalmente fica? 4. O que podemos afirmar sobre a instrução Option Compare? ☐ a) Público, portanto é possível chamá-lo de qualquer outro módulo. ☐ b) Privado, podemos chamá-lo somente no mesmo módulo onde ele foi criado. ☐ c) Procedimento sub não pode chamar outro procedimento sub. ☐ d) Não é possível criar um procedimento sub no módulo de classe. ☐ e) Nenhuma das alternativas anteriores está correta. ☐ a) Compara o conteúdo de duas strings. ☐ b) Define o modo de comparação a ser utilizado no Access. ☐ c) Define o modo de comparação a ser utilizado em todos os módulos. ☐ d) Define o modo de comparação a ser utilizado nos procedimentos do módulo. ☐ e) Indica o limite inferior de subscrição de uma matriz. 66 Access 2016 - Módulo II 5. O que podemos afirmar com relação ao nome de uma variável? ☐ a) Pode conter um ponto de exclamação. ☐ b) Pode ter até 256 caracteres. ☐ c) Pode ser igual ao nome de outra variável, desde que de natureza diferente. ☐ d) Pode ter até 255 caracteres. ☐ e) Não pode começar por letra maiúscula. 3 9 Variáveis; 9 Constantes. Variáveis e constantes 68 Access 2016 - Módulo II 3.1. Variáveis Variáveis são recursos existentes nas linguagens
Compartilhar