Lembre-se dos dias felizes da juventude quando sopa de letrinhas era uma refeição divertida. Fique com esses pensamentos felizes no fundo da sua mente enquanto provamos a versão de banco de dados da sopa de letrinhas. As seções a seguir explicam os conceitos básicos sobre como chegar a um banco de dados com cadeias de conexão e usar uma interface de programação de banco de dados no código Access VBA.
Neste artigo
Usando o driver ODBC ou provedor OLE DB
Programaticamente conecte ao SQL Server a partir do Access
Resumo das versões do driver ODBC
Resumo das versões do provedor OLE DB
Resumo de palavras-chave do ODBC
Resumo de palavras-chave do OLE DB
Usando o driver ODBC ou provedor OLE DB
As cadeias de conexão existem há muito tempo. Você pode definir uma cadeia de conexão formatada na interface do usuário do Access ou no código VBA. Uma cadeia de conexão (tanto ODBC como OLE DB) passa informações diretamente para o banco de dados, como local do servidor, nome do banco de dados, tipo de segurança e outras opções úteis. Por exemplo:
ODBC;DRIVER=SQL Server;SERVER="MyServer";DATABASE="MyHRdb";TRUSTED_CONNECTION=Yes
Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyHRdb; Integrated Security=SSPI;
No início, havia o SNAC (SQL Server Native Client), uma biblioteca autônoma que continha as tecnologias ODBC e OLEDB, e ainda está disponível para as versões do SQL Server de 2005 a 2012. Muitos aplicativos herdados usavam o SNAC e ainda são compatíveis com versões anteriores, mas não recomendamos usá-lo para o desenvolvimento de novos aplicativos. Você deve usar versões posteriores individuais e baixáveis dos drivers ODBC.
Drivers ODBC
ODBC (Open Database Connectivity) é um protocolo usado para conectar um banco de dados do Access a uma fonte de dados externa, como o Microsoft SQL Server. Normalmente, você usa fontes de dados de arquivo (também chamadas de arquivos DSN) para adicionar uma cadeia de conexão, neste caso, a palavra-chave FILEDSN é usada na cadeia de conexão, ou armazenada no registro, neste caso, a palavra-chave DSN é usada. Como alternativa, você pode usar o VBA para definir essas propriedades usando uma cadeia de conexão "sem DSN".
Ao longo dos anos, os drivers ODBC foram distribuídos em três fases:
- Antes de 2005, os drivers ODBC eram fornecidos com o Windows Data Access Components (WDAC), originalmente chamado de Microsoft Data Access Components (MDAC). Esses componentes ainda são fornecidos com o Windows para compatibilidade com versões anteriores. Para obter mais informações, consulte Microsoft ou Componentes de Acesso a Dados do Windows.
- Drivers ODBC fornecidos com o SNAC para SQL Server 2005 por meio do SQL Server 2012.
- Após o SQL Server 2012, os drivers ODBC foram enviados individualmente e contêm suporte para novos recursos do SQL Server.
Para novas atividades de desenvolvimento, evite usar drivers ODBC das duas primeiras fases e use drivers ODBC da terceira fase.
Provedores OLE DB
Vinculação e Incorporação de Objetos, Banco de Dados (OLE DB) é um protocolo mais recente usado para conectar um banco de dados do Access a uma fonte de dados externa, como o Microsoft SQL Server. O OLE DB não requer um DSN e também fornece acesso total a fontes de dados ODBC e drivers ODBC.
Sugestão Normalmente, utiliza a caixa de diálogo Propriedades da Ligação de Dados para adicionar um cadeia de conexão OLE DB. Embora não haja nenhuma maneira para abrir a caixa de diálogo Propriedades de Vínculo de Dados a partir do Access, no Windows Explorer, você pode criar um arquivo .txt vazio, alterar o tipo de arquivo para .udl e, em seguida, clicar duas vezes no arquivo. Depois de criar uma cadeia de conexão, altere o tipo de arquivo de volta para .txt.
Ao longo dos anos, os provedores OLE DB foram fornecidos em três fases:
- Antes de 2005, os provedores OLE DB eram fornecidos com o Windows Data Access Components (WDAC), originalmente chamado de Microsoft Data Access Components (MDAC).
- Os provedores OLE DB eram fornecidos com o SQL Server 2005 a SQL Server 2017. Foi preterido em 2011.
- Em 2017, o provedor OLE DB do SQL Server não se encontrava preterido.
A versão atualmente recomendada para o desenvolvimento de novas soluções é o OLE DB Driver 18 para SQL Server.
Como otimizar o desempenho com uma cadeia de conexão ODBC
Para otimizar o desempenho, minimizar o tráfego de rede e reduzir o acesso de vários usuários ao banco de dados do SQL Server, use o mínimo possível de sequências de conexão compartilhando cadeias de conexão em vários conjuntos de registros. Embora o Ace simplesmente passe uma cadeia de conexão para o servidor, ele entende e usa as seguintes palavras-chave: DSN, DATABASE, UID, PWD e DRIVER para ajudar a minimizar a comunicação entre o cliente e o servidor.
Nota Se uma ligação ODBC a uma origem de dados externa for perdida, o Access tenta ligar-se automaticamente à mesma. Se a tentativa for bem sucedida, você poderá continuar trabalhando. Se a repetição falhar, pode continuar a trabalhar com objetos que não dependem da ligação. Para reconectar, feche e abra novamente o Access.
Recomendações ao usar o ODBC e o OLE DB
Evite misturar as cadeias de conexão e as tecnologias de acesso a banco de dados. Use uma cadeia de conexão ODBC para o DAO. Use uma cadeia de conexão OLE DB para o ADO. Se o aplicativo contiver código VBA que usa DAO e ADO, use o driver ODBC para DAO e o provedor OLE DB para ADO. Esforce-se para obter os últimos recursos e suporte tanto para ODBC como para OLEDB, respectivamente.
O ODBC usa o driver de termo e o OLE DB usa o provedor de termo. Os termos descrevem o mesmo tipo de componente de software, mas não são intercambiáveis na sintaxe da cadeia de conexão. Use o valor correto conforme documentado.
Programaticamente conecte ao SQL Server a partir do Access
Há duas maneiras principais de conectar programaticamente a um banco de dados do SQL Server a partir do Access.
DAO
Um objeto de acesso a dados (DAO) fornece uma interface abstrata para um banco de dados. O Microsoft Data Access Objects (DAO) é o modelo de objeto de programação nativo que lhe permite aceder ao centro do Access e SQL Server para criar, eliminar, modificar e listar objetos, tabelas, campos, índices, relações, consultas, propriedades e bases de dados externas.
Para obter mais informações, veja Referência de Objetos do Microsoft Data Access.
ADO
O ActiveX Data Objects (ADO) fornece um modelo de programação de alto nível e está disponível no Access por meio de uma referência a uma biblioteca de terceiros. O ADO é simples de aprender e permite que aplicativos clientes acessem e manipulem dados de várias fontes, incluindo o Access e o SQL Server. Seus principais benefícios são a facilidade de uso, alta velocidade, baixa sobrecarga de memória e um pequeno espaço de disco. O ADO também oferece suporte aos principais recursos para aplicativos de criação e baseados na Web.
Para obter mais informações, veja Microsoft ActiveX Data Objects reference (Referência de Objetos de Dados Do Microsoft ActiveX ) e Microsoft ActiveX Data Objects (ADO)).
Qual deles você deve usar?
Em uma solução do Access que usa o código VBA, você pode usar DAO, ADO ou ambos como sua tecnologia de interface de banco de dados. O DAO continua a ser o padrão no Access. Por exemplo, todos os formulários, relatórios e consultas do Access usam o DAO. Mas quando você migrar para o SQL Server, considere usar o ADO para tornar sua solução mais eficiente. Estão são algumas diretrizes gerais para ajudá-lo a decidir quando usar o DAO ou o ADO.
Use o DAO quando o seu objetivo for:
- Criar um formulário vinculado de leitura/gravação sem usar o VBA.
- Consultar tabelas locais.
- Baixar dados para tabelas temporárias.
- Usar consultas de passagem como fontes de dados para relatórios ou formulários no modo somente leitura.
- Definir e usar um objeto TableDef ou Querydef no VBA.
Use o ADO quando o seu objetivo for:
- Usar novas formas de otimização, como executar operações assíncronas.
- Executar consultas de passagem DDL e DML.
- Obter dados do SQL Server diretamente por meio de conjuntos de registros no VBA.
- Escreva um código mais simples para determinadas tarefas, como streaming de Blobs.
- Chame um procedimento armazenado diretamente, com parâmetros, usando um objeto de comando no VBA.
Resumo das versões do driver ODBC
A tabela a seguir resume informações importantes sobre versões de driver ODBC, locais de download e suporte a recursos. Certifique-se de usar a versão de bits correta (64 bits ou 32 bits) do driver com base no Windows e não no Office. Se você estiver executando o Access de 32 bits no Windows de 64 bits, instale os drivers de 64 bits, que incluem os componentes de 32 bits necessários para o Access.
Para obter mais informações, consulte Utilizar Palavras-chave de Cadeia de Ligação com SQL Server Native Client, Notas de Versão do ODBC para SQL Server no Windows (V17) e Funcionalidades do Controlador ODBC da Microsoft para SQL Server no Windows (V13, 11).
Resumo das versões do provedor OLE DB
A tabela a seguir resume informações importantes sobre versões de provedores OLE DB, locais de download e suporte a recursos. Certifique-se de usar a versão de bits correta (64 bits ou 32 bits) do driver com base no Windows e não no Office. Se você estiver executando o Access de 32 bits no Windows de 64 bits, instale os drivers de 64 bits, que incluem os componentes de 32 bits necessários para o Access.
Para obter mais informações, veja Using Connection String Keywords with SQL Server Native Client (Utilizar Palavras-chave de Cadeia de Ligação com SQL Server Native Client).
| Provedor OLE DB | Versão | Baixar | Novos recursos |
|---|---|---|---|
| Driver OLE DB 18.2.1 (MSOLEDBSQL) |
SQL Server 2017 | Baixar | Consulte Driver do OLE DB para SQL Server Notas de Funcionalidade e Versão do Controlador OLE DB da Microsoft para obter SQL Server |
| SQL Server Native Client (SQLNCLI) | SQL Server 2005 a 2012 | Preterido, não use | |
| Driver OLE DB (SQLOLEDB) | Preterido, não use |
Resumo de palavras-chave do ODBC
A tabela a seguir resume as palavras-chave ODBC reconhecidas pelo SQL Server e sua finalidade. Apenas um subconjunto é reconhecido pelo Access.
| Palavra-chave | Descrição |
|---|---|
| Endereço | O endereço de rede do servidor que está executando uma instância do SQL Server. |
| AnsiNPW | Especifica o uso de comportamentos definidos por ANSI para manipular comparações NULL, preenchimento de dados de caractere, avisos e concatenação NULL (Sim ou Não). |
| APLICATIVO | Nome do aplicativo chamando o SQLDriverConnect. |
| ApplicationIntent | Declara o tipo de carga de trabalho do aplicativo ao se conectar a um servidor (ReadOnly ou ReadWrite). |
| AttachDBFileName | Nome do arquivo principal de um banco de dados anexável. |
| AutoTranslate | Especifica se as cadeias de caracteres ANSI são enviadas entre o cliente ou o servidor ou se são convertidas para Unicode (Sim ou Não). |
| Banco de dados | O nome do banco de dados. Descrição O objetivo da ligação. Nome do Controlador do controlador, conforme devolvido pelos SQLDrivers. |
| DSN | Nome de um usuário ODBC ou fonte de dados do sistema existente. Encriptar Especifica se os dados devem ser encriptados antes de os enviar através da rede (Sim ou Não). |
| Failover_Partner | Nome do servidor do parceiro de failover a ser usado se não for possível estabelecer uma conexão com o servidor principal. |
| FailoverPartnerSPN | O SPN para o parceiro de failover. |
| Fallback | Palavra-chave preterida. |
| FileDSN | Nome de uma fonte de dados de arquivo ODBC existente. Idioma O idioma SQL Server. |
| MARS_Connection | Especifica vários conjuntos de resultados ativos (MARS) na conexão para o SQL Server 2005 (9.x) ou posterior (Sim ou Não). |
| MultiSubnetFailover | Especifica se você deve se conectar ao ouvinte do grupo de disponibilidade de um grupo de disponibilidade do SQL Server, ou a uma Instância de Cluster de Failover (Sim ou Não). |
| Líquido | dbnmpntw indica pipes nomeados e dbmssocn indica TCP/IP. |
| PWD | A senha de logon do SQL Server. |
| QueryLog_On | Especifica o registro em log de consultas demoradas (Sim ou Não). |
| QueryLogFile | Caminho e nome de arquivo completos de um arquivo a ser usado para registrar dados em consultas demoradas. |
| QueryLogTime | Cadeia de caracteres de dígitos especificando o limite (em milissegundos) para o registro em log de consultas de longa execução. |
| QuotedId | Especifica se o SQL Server usa as regras ISO relacionadas ao uso de aspas nas instruções SQL (Sim ou Não). |
| Regional | Especifica se o driver ODBC do SQL Server Native Client usa configurações do cliente ao converter dados de moeda, data ou hora em dados de caractere (Sim ou Não). |
| SaveFile | Nome de um arquivo de fonte de dados ODBC no qual os atributos da conexão atual são salvos se a conexão for bem-sucedida. |
| Servidor | O nome de uma instância do SQL Server: Servidor na rede, um endereço IP ou um alias do Gerenciador de Configurações. |
| ServerSPN | O SPN para o servidor. |
| StatsLog_On | Habilita a captura dos dados de desempenho do driver ODBC do SQL Server Native Client. |
| StatsLogFile | Caminho e nome de arquivo completos de um arquivo usado para registrar estatísticas de desempenho do driver ODBC do SQL Server Native Client. |
| Trusted_Connection | Especifica se o Modo de Autenticação do Windows ou o nome de usuário ou a senha do SQL Server são usados para a validação de logon (Sim ou Não). |
| TrustServerCertificate | Quando usado com o Encrypt, permite fazer criptografia usando um certificado de servidor autoassinado. |
| UID | O nome de logon do SQL Server. |
| UseProcForPrepare | Palavra-chave preterida. |
| WSID | O identificador da estação de trabalho, o nome da rede do computador no qual o aplicativo reside. |
Resumo de palavras-chave do OLE DB
A tabela a seguir resume palavras-chave OLE DB reconhecidas pelo SQL Server e sua finalidade. Apenas um subconjunto é reconhecido pelo Access.
| Palavra-chave | Descrição |
|---|---|
| Endereço | O endereço de rede do servidor que está executando uma instância do SQL Server. |
| APLICATIVO | A cadeia de caracteres que identifica o aplicativo. |
| ApplicationIntent | Declara o tipo de carga de trabalho do aplicativo ao se conectar a um servidor (ReadOnly ou ReadWrite). |
| AttachDBFileName | Nome do arquivo principal de um banco de dados anexável. |
| AutoTranslate | Configura a conversão de caracteres OEM/ANSI (Verdadeiro ou Falso). |
| Tempo limite de conexão | A quantidade de tempo (em segundos) a aguardar até a conclusão da inicialização da fonte de dados. |
| Idioma Atual | O nome do idioma do SQL Server. |
| Fonte de Dados | O nome de uma instância do SQL Server na organização. |
| Banco de dados | O nome do banco de dados. |
| DataTypeCompatibility | Um número que indica o modo de manipulação de tipo de dados que será usado. |
| Criptografar | Especifica se os dados devem ser criptografados antes de enviá-los pela rede (Sim ou Não). |
| FailoverPartner | O nome do servidor de failover usado para espelhamento de banco de dados. |
| FailoverPartnerSPN | O SPN para o parceiro de failover. |
| Catálogo Inicial | O nome do banco de dados. |
| Nome de Arquivo Inicial | O nome do arquivo principal (inclua o nome do caminho completo) de um banco de dados anexável. |
| Segurança Integrada | Usada para Autenticação do Windows (SSPI). |
| Idioma | O idioma do SQL Server. |
| MarsConn | Especifica vários conjuntos de resultados ativos (MARS) na conexão para o SQL Server 2005 (9.x) ou posterior (Sim ou Não). |
| Líquido | A biblioteca de rede usada para estabelecer uma conexão com uma instância do SQL Server na organização. |
| Endereço de Rede | O endereço de rede de uma instância do SQL Server na organização. |
| PacketSize | Tamanho do pacote de rede. O padrão é 4096. |
| Informações de Persistência de Segurança | Especifica se a Persistência de Segurança está ativada (Verdadeiro ou Falso). |
| PersistSensitive | Especifica se a Persistência Sensível está ativada (Verdadeiro ou Falso). |
| Provedor | Para o SQL Server Native Client, deve ser SQLNCLI11. |
| PWD | A senha de logon do SQL Server. |
| Servidor | O nome de uma instância do SQL Server: Servidor na rede, um endereço IP ou um alias do Gerenciador de Configurações. |
| ServerSPN | O SPN para o servidor. |
| Tempo limite | A quantidade de tempo (em segundos) a aguardar até a conclusão da inicialização da fonte de dados. |
| Trusted_Connection | Especifica se o Modo de Autenticação do Windows ou o nome de usuário ou a senha do SQL Server são usados para a validação de logon (Sim ou Não). |
| TrustServerCertificate | Especifica se um certificado do servidor é validado (Verdadeiro ou Falso). |
| UID | O nome de logon do SQL Server. |
| Usar Criptografia para os Dados | Especifica se os dados devem ser criptografados antes de enviá-los pela rede (Verdadeiro ou Falso). |
| UseProcForPrepare | Palavra-chave preterida. |
| WSID | O identificador da estação de trabalho, o nome da rede do computador no qual o aplicativo reside. |
Administrar fontes de dados ODBCGerenciar tabelas vinculadas