none
Realizar consulta ADO com arquivo somente leitura RRS feed

  • Pergunta

  • 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

    quarta-feira, 17 de junho de 2015 14:36

Respostas

  • 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

    • Marcado como Resposta FilipeMagno sexta-feira, 19 de junho de 2015 22:58
    quinta-feira, 18 de junho de 2015 00:20
    Moderador

  • 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

    • Marcado como Resposta FilipeMagno sexta-feira, 19 de junho de 2015 23:00
    sexta-feira, 19 de junho de 2015 20:11
    Moderador
  • 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

    • Marcado como Resposta FilipeMagno sexta-feira, 26 de junho de 2015 01:24
    terça-feira, 23 de junho de 2015 01:48
    Moderador
  • 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

    • Marcado como Resposta FilipeMagno sexta-feira, 19 de junho de 2015 22:59
    quinta-feira, 18 de junho de 2015 00:31

Todas as Respostas

  • 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

    quinta-feira, 18 de junho de 2015 00:10
  • 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

    • Marcado como Resposta FilipeMagno sexta-feira, 19 de junho de 2015 22:58
    quinta-feira, 18 de junho de 2015 00:20
    Moderador
  • 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

    • Marcado como Resposta FilipeMagno sexta-feira, 19 de junho de 2015 22:59
    quinta-feira, 18 de junho de 2015 00:31
  • 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

    quinta-feira, 18 de junho de 2015 02:30
    Moderador
  • 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


    • Editado FilipeMagno sexta-feira, 19 de junho de 2015 19:35 Complementação
    sexta-feira, 19 de junho de 2015 19:34

  • 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

    • Marcado como Resposta FilipeMagno sexta-feira, 19 de junho de 2015 23:00
    sexta-feira, 19 de junho de 2015 20:11
    Moderador
  • 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

    terça-feira, 23 de junho de 2015 01:21
  • 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

    • Marcado como Resposta FilipeMagno sexta-feira, 26 de junho de 2015 01:24
    terça-feira, 23 de junho de 2015 01:48
    Moderador