Realizar consulta ADO com arquivo somente leitura

Question

Bom dia a todos.

Estou com um problema que me parece não ter como contornar de maneira produtiva. Então peço ajuda para os mais experientes para avaliar se realmente é isso ou se há uma outra forma.

O problema é o seguinte: utilizo a biblioteca ADO para realizar consultas numa base de dados por expressão SQL. Como a base não é grande (retiro ~12.000 linhas de arquivos texto), realizo a cópia dos dados para a planilha atual e realizo a consulta. Normalmente trabalho com alguns desses arquivos abertos simultaneamente, logo para evitar a multiplicação de arquivos, sempre escolho a opção "Novo" (na verdade já salvo como .xltm para fazer isso de forma automática), que me permite abrir uma cópia do arquivo ao invés do próprio arquivo e de quebra mantenho intacto meu modelo. O problema é que para a consulta funcionar há a exigência do arquivo estar salvo! Daí vem a primeira limitação: não é possível abrir 2 arquivos simultaneamente sem copiá-lo com nome diferente. Esse não é um problema grande, apesar de incomodar. Mas a segunda limitação sim: para evitar alterar o modelo, marquei então a opção de "Somente Leitura", mas isso também parece não ser aceito. O código de erro é "-2147467259 - A tabela externa não está no formato esperado."

A pergunta que faço é se há uma forma de contornar a necessidade do arquivo estar salvo ou se pelo menos existe a possibilidade de poder utilizar num arquivo somente leitura?

Obs.: Testei com as bibliotecas "Microsoft ActiveX Data Objects 6.1 Library" e também com a "Microsoft ActiveX Data Objects 2.0 Library" no Excel 2010.

Obrigado.

---

Filipe Magno

Answer 1

Bom, enquanto não vejo uma solução definitiva, utilizei uma saída paliativa. Continuo mantendo o arquivo como modelo e somente leitura, e quando identificada essa situação, é solicitado ao usuário autorização para salvamento num local pré-definido ou indicação de novo local. Para evitar a multiplicação de arquivos e simplificar o salvamento, adicionei um prefixo comum e um sufixo randômico. Assim, ao solicitar o salvamento, o usuário tem a opção de apagar automaticamente todos os arquivos que atendem àquele padrão na pasta de destino, facilitando o gerenciamento. Caso não salve, os controles do formulário relativos à pesquisa ficam desabilitados.

De qualquer forma, se alguém souber de uma solução melhor eu agradeço enormemente.

Vlw.

---

Filipe Magno

Answer 2

Não sei se entendi perfeitamente sua dúvida.

1 - Não é considerado uma boa prática fazer uma conexão numa pasta de trabalho aberta. Você terá problemas de vazamento de memória dessa forma. Tente adotar a prática de sempre executar consultas SQL em pastas de trabalho fechadas.

2 - Se você usa DAO para executar as consultas, não precisa referenciar a biblitoeca Microsoft ActiveX Data Objects 2.0 Library, só a outra que citou mesmo.

3 - Se você usar ADO, precisará da Microsoft ActiveX Data Objects Library. Dica: utilize, no mínimo, a versão 2.6 dessa biblioteca.

4 - Talvez não valha a pena fazer essa adaptação agora, mas dê preferência ao uso de conexões ADO ao invés de DAO. Você irá encontrar na internet as pessoas defendendo os dois lados, mas a minha opinião é que, ADO é mais vantajoso no que tange a compatibilidade com 64 bits e portabilidade de código para migração a outros backends.

5 - Por que não salva uma cópia desse modelo somente para leitura numa pasta temporária, consome os dados, e depois exclui? Exemplo:

Sub Main()
    Dim tempFileName As String
    
    tempFileName = Environ("temp") & "\QueryWorkbook.xlsm"
    ThisWorkbook.SaveCopyAs tempFileName
    
    'instruções que cria a conexão na pasta de trabalho fechada e executa consultas
    
    Kill tempFileName
End Sub

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 3

Boa noite Felipe!

Obrigado pela resposta.

Lendo seus comentários cheguei à conclusão que o caminho que tomei pode ser muito melhorado. Ao invés de salvar uma cópia do modelo, como acabei implementando segundo meu post anterior, vou criar um arquivo simples contendo apenas o resultado da importação, onde farei a consulta. Dessa forma não haverá mais necessidade de criar múltiplos arquivos, apenas um que pode ser sobrescrito, além de garantir que o arquivo estará sempre fechado.

A dúvida que fiquei é se o resultado da consulta, que visualizo num ListBox, pode ser escrito na pasta modelo. Creio que isso não é problema, correto?

Obs.: Estou fazendo com ADO mesmo e não DAO (inclusive estou utilizando como base uma função sua que havia lido no seu antigo site - obrigado!)

Abraço.

---

Filipe Magno

Answer 4

Não há problemas em uma pasta de trabalho receber os resultados de uma consulta, seja ela um modelo ou não.

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 5

Ok.

Apenas fiquei com uma dúvida onde vc diz: "Não é considerado uma boa prática fazer uma conexão numa pasta de trabalho aberta. Você terá problemas de vazamento de memória dessa forma". A intenção é dizer "Pasta de Trabalho Aberta" ou "Pasta de Trabalho Não Salva"?

Pergunto isso porque a base que utilizei foi um artigo seu sugerindo usar consulta SQL para Formulários, e nesse caso os dados estão na própria pasta de trabalho.

De qualquer forma, abandonei a minha primeira opção (que já tinha implementado) de salvar o modelo com nome aleatório e passei a adotar o salvamento dos dados em arquivo único(em fase final), de forma que o arquivo de consulta estará sempre fechado.

Antecipados agradecimentos.

---

Filipe Magno

Answer 6

Eu quis dizer Pasta de Trabalho Aberta mesmo, estando ela salva ou não. Mais detalhes em: https://support.microsoft.com/en-us/kb/319998

A questão de estar salva ou não é que parece que a consulta ADO retorna apenas dados da última versão salva de uma pasta de trabalho, mas, não vale nem a pena explorar esse detalhe porque a pasta de trabalho estar aberta, em si, já não é legal.

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 7

Boa noite Felipe!

Agradeço imensamente pela ajuda prestada, tudo funcionou muito bem. Entretanto me deparei com um outro problema, menor eu espero.

Como fiz o arquivo no Excel 2010, adicionei a última biblioteca disponível, a "Microsoft ActiveX Data Objects 6.1 Library". O problema é que no Excel 2007, onde muitos utilizarão, existe apenas a "Microsoft ActiveX Data Objects 6.0 Library", a qual não existe no Excel 2010. Eu até pensei em verificar e adicioná-las programaticamente, porém teria a necessidade de cada usuário marcar a opção de "Confiar no acesso ao modelo de objeto do projeto VBA", o que penso não ser uma boa alternativa.

A pergunta que faço é se sabe alguma alternativa para contornar esse problema ou se vou ter que adicionar uma versão anterior comum a ambos, como a "2.8"?

Obrigado.

---

Filipe Magno

Answer 8

A versão 6.0 ou superior só aparecem a partir do Windows Vista, então talvez não seja uma boa ideia referencia-la.

Minha recomendação é trabalhar com a versão 2.8, que está presente na instalação de computadores com Windows XP ou superior e, na prática, oferece as mesmas funcionalidades que a 6.0.

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br