MaximoAccess

Caro Usuário, antes de postar pela primeira vez, leia as regras do fórum.

Obrigado

Administração do MaximoAccess

Dicas Ms Access, Exemplos Ms Access, Codigos VBA Ms Access, SQL Ms Access


    Uso correto do ADODB para obter dados externos e gravar em tabela local

    avatar
    danielias
    Novato
    Novato

    Respeito às Regras 100%

    Sexo : Masculino
    Localização : Brasil
    Mensagens : 1
    Registrado : 23/07/2016

    Uso correto do ADODB para obter dados externos e gravar em tabela local Empty Uso correto do ADODB para obter dados externos e gravar em tabela local

    Mensagem  danielias em 2/4/2019, 22:53

    Caros, saudações.
    Sou novato no VBA, e estou empacado numa questão: preciso me conectar numa fonte de dados externa, via ODBC, recuperar alguns dados e gravar numa tabela local. Pelas pesquisas que fiz, devo utilizar o ADODB para recuperar os dados, e até aí tudo bem (já estou conseguindo obter os dados e colocá-los num recordset), mas estou com algumas dúvidas quanto à inserção dos dados do recordset na tabela local. Uma forma que encontrei e que funcionou foi utilizar a instrução SQL "INSERT INTO" dentro de uma repetição, para inserir os registros do recordset na tabela um a um. Esse processo é muito lento, tenho gasto cerca de 10 minutos só pra uma tabela. Um exemplo do código:

    * adRs é um ADODB.Recordset
    * adConn é um ADODB.Connection
    * db é um DAO da tabela local

    Código:

            'executa consulta
            Set adRs = adConn.Execute("SELECT * FROM TIPO;")

            'atualiza status e atualiza tabela
            If Not IsNull(adRs) Then

                'limpa dados da tabela desatualizada
                If Not IsNull(DLookup("Name", "MSysObjects", "Name='TIPO'")) Then
                    Application.DoCmd.SetWarnings False
                    db.Execute ("DELETE * FROM TIPO"), dbFailOnError
                    Application.DoCmd.SetWarnings True
                End If

                'popula tabela com recordset
                Do Until adRs.EOF
                    db.Execute ("INSERT INTO TIPO (ID_TIPO, SIGLA, DESCRICAO) " & _
                                " VALUES (" & adRs![ID_TIPO] & ", '" & LTrim(RTrim(adRs![SIGLA])) & "', '" & Replace(LTrim(RTrim(adRs![DESCRICAO])), "'", "''") & "');"), dbFailOnError
                    adRs.MoveNext
                Loop

                'verifica se tabela foi populada
                If Not IsNull(DLookup("Name", "MSysObjects", "Name='TIPO'")) Then
                    Set adRs = Nothing
                Else
                    Set adRs = Nothing
                    Msgbox("Erro ao atualizar tabela local TIPO!")
                End If
            Else
                Set adRs = Nothing
                Msgbox("Erro na importação dos dados tabela TIPO!")
            End If

    Notem que utilizo o ADODB para obter os dados externos e o DAO para a tabela local. Nessa forma, além de ser lenta, tive um problema na hora de inserir os dados na tabela local (em uma das consultas) ao inserir valores do tipo "double". Por isso pensei em procurar alguma alternativa que fosse mais rápida, e que não precisasse da repetição, que parece ser o que está deixando a execução lenta. Encontrei o que parecia ser uma alternativa: utilizar a função UpdateBatch de um recordset do tipo ADODB. Então eu mudei o código acima para não mais utilizar o tipo DAO:

    Código:


        Dim connLocal As ADODB.Connection
        Dim connExterna As ADODB.Connection
        
        'recordset
        Dim adoRS As ADODB.Recordset
        
        'instancia conexao local
        Set connLocal = New ADODB.Connection
        connLocal.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.CurrentProject.Path & "\BDGC.accdb"
        Set adoRS = New ADODB.Recordset
        adoRS.Open "TIPO", connLocal, adOpenKeyset, adLockOptimistic, acCmdTable

        'instancia conexao externa
        Set connExterna = New ADODB.Connection
        connExterna .ConnectionTimeout = 30
        connExterna .Open Conexao, Usuario, Senha
        
        'verifica se conexao foi aberta
        If connExterna.State = adStateOpen Then

            'executa consulta e seta recordset local
            Set adoRS = connExterna.Execute("SELECT * FROM TIPO;")
            
            'atualiza tabela
            If Not IsNull(adoRS) Then
                
                'apaga dados desatualizados
                adoRS.Open "DELETE * FROM TIPO", connLocal, adOpenKeyset, adLockOptimistic, adCmdText
                'insere dados na tabela
                adoRS.MoveFirst
                adoRS.UpdateBatch
                
                'verifica se tabela foi populada
                If Not IsNull(DLookup("Name", "MSysObjects", "Name='TIPO'")) Then
                    adoRS.Close
                    Set adoRS = Nothing
                Else
                    Msgbox("Erro ao atualizar tabela local TIPO!")
                End If
            Else
                Msgbox("Erro na importação dos dados tabela TIPO!")
            End If
        Else
            Msgbox("Erro na conexao externa!")
        End If


    E não funciona. Acho que me perdi, quando setei o recordset para a tabela local e depois setei o mesmo recordset para receber os dados externos e ainda setei o recordset para apagar os dados da tabela. Como disse inicialmente, sou iniciante em VBA, então não conheço muito bem essas classes. A ideia geral do projeto é: me conectar a fonte externa, executar uma consulta SELECT (isso é imprescindível) para obter os dados e depois inseri-los numa tabela local, e na forma mais rápida e eficiente possível. Este segundo código está no caminho certo? Qual é o jeito certo de se fazer?

    Qualquer ajuda será bem vinda.

    Obrigado.
    marcelo3092
    marcelo3092
    Intermediário
    Intermediário

    Respeito às Regras 100%

    Sexo : Masculino
    Localização : Brasil
    Mensagens : 179
    Registrado : 19/08/2010

    Uso correto do ADODB para obter dados externos e gravar em tabela local Empty Uso correto do ADODB para obter dados externos e gravar em tabela local

    Mensagem  marcelo3092 em 6/4/2019, 02:27

    Ola Amigo boa noite então eu tb estou nessa empreitada online no access estou usando um código que por padrão e do jeferson da kartofel funciona perfeito porem a questao sua e vc esta trabalhando com formularios desvinculado certo, e se esta qual necessidade de alimentar uma tabela, no caso e uma tabela temporaria, eu ate uso algumas tabelas temporarias. da seguinte forma;


    Public Function Carregar_Dados_SubGrupo()

    Dim objRSG As DAO.Recordset

    Call Conexao_Open("select * from tblgrupo;")

    Rem garantindo que a tabela esteja vazia
    CurrentDb.Execute "delete * from temp_grupo;"

    Rem abrindo tabela temporária somente para adição de dados
    Set objRSG = CurrentDb.OpenRecordset("temp_grupo", , Cool

    While (Not rs.EOF)

    objRSG.AddNew
    objRSG.Fields("Cod_Grupo").Value = rs.Fields("Cod_Grupo").Value
    objRSG.Fields("Grupo").Value = rs.Fields("Grupo").Value


    objRSG.Update

    rs.MoveNext 'vai para o proximo registro

    Wend

    rs.Close 'fecha o recorset
    cn.Close 'fecha o banco de dados

    Rem fechando a tabela temporária aberta na memória do vba
    objRSG.Close: Set objRSG = Nothing


    End Function
    marcos_novack
    marcos_novack
    Intermediário
    Intermediário

    Respeito às Regras 100%

    Sexo : Masculino
    Localização : Brasil
    Mensagens : 67
    Registrado : 19/01/2011

    Uso correto do ADODB para obter dados externos e gravar em tabela local Empty Re: Uso correto do ADODB para obter dados externos e gravar em tabela local

    Mensagem  marcos_novack em 7/4/2019, 23:32

    Boa noite amigos!!!

    Então, sou mais um neste processo de migração de base de dados do access para MySql, tudo tem que ser adaptado, e a situação mais intrigante é o tempo de demora para carregar uma tabela temporária no access, fiz teste com View criadas no MySql e o tempo é o mesmo, não muda em nada, tenho uma tabela com 4.500 registros , com 7 campos demora 8 segundos para carregar o formulário de pesquisa (para filtrar um form de cadastro ex.) neste caso a tabela temporária para meu sistema é necessário para usar filtro com critério like no evento ao alterar para ir filtrando até aparecer o registro a ser clicado então os dados são carregados no formulário cadastro.

    meu Processo : criar tbltemp >>> depois montar um select desta tblTemp e colocar na origem dos dados da lista.... como ex: Me.Lista.RowSource = strSql

    Gambiarra  30 segundos para carregar

    alguém tem uma solução..

    código adaptado que peguei aqui do fórum; e do site do usando access

    Dim StrTblTemp As TableDef
    Dim dbs As Database
    Dim strTblLocal As String

      strTblLocal = "tblAdm"
      Set dbs = CurrentDb
     
      ' Checa se a tabela existe e deleta-a
       For Each StrTblTemp In dbs.TableDefs
          If StrTblTemp.Name = strTblLocal Then
             DoCmd.DeleteObject acTable, strTblLocal
               'GoTo DeletedTable
           End If
       Next

      'crio a tbl na base local
           CurrentDb.Execute "CREATE TABLE " & strTblLocal & "  " & _
                                "(" & _
                               "id int, " & _
                               "Lote varchar(255), " & _
                               "Cor varchar(255), " & _
                               "CodMalha varchar(255), " & _
                               "Malha varchar(255), " & _
                               "Gramatura int " & _
                               ");"
                         
       
     ' Busco os dados da View_tbladm no MySQL, neste caso uma View criada no banco Mysql
       Call Conexao_Open("View_tblAdm")
         
      ' Carrego os dados da View_tblAdm do MySQL para a tabela Local tblAdm
      Do While Not rs.EOF
         CurrentDb.Execute ("INSERT INTO " & strTblLocal & "(id,lote,cor,codmalha,malha) VALUES (" & rs!ID & ",'" & rs!LOTE & "','" & rs!Cor & "','" & rs!codmalha & "','" & rs!Malha & "')")
     
         
         rs.MoveNext
    Loop
     
      rs.Close    'fecha o recorset
      cn.Close    'fecha o banco de dados
     
      'strFiltro = Nz("Like ('*" & Me.FiltroLista.Text & "*')")
      strSqlLista = "SELECT tblAdm.id, tblAdm.Lote, tblAdm.Cor, tblAdm.CodMalha, tblAdm.Malha " & _
                     "From tblAdm;"

           Me.Lista.RowSource = strSqlLista
           

    vou acompanhar

    uma opção que estou usando é limitar a consulta a 1000 registros ou menos ...... LIMIT 1000
    marcelo3092
    marcelo3092
    Intermediário
    Intermediário

    Respeito às Regras 100%

    Sexo : Masculino
    Localização : Brasil
    Mensagens : 179
    Registrado : 19/08/2010

    Uso correto do ADODB para obter dados externos e gravar em tabela local Empty Re: Uso correto do ADODB para obter dados externos e gravar em tabela local

    Mensagem  marcelo3092 em 8/4/2019, 14:31

    Bom dia amigo dizem que da forma de carrega tabelas temporárias não é recomendado eu consegui carrega a lista completa sem tabela temporária e coloquei filtro no campo que filtra a listbox funciona certim logo posto pra vc dá uma olhada.
    Não sei qual forma e o correto porém já vi alguns sistemas em ação que ao abrir um formulário de pesquisa a lista fica em branco aí quando se pesquisa ele busca e trás. Informação porém o meu tanto abre com a lista carregada quanto digitando ele pesquisa porém depende muito da internet.
    marcos_novack
    marcos_novack
    Intermediário
    Intermediário

    Respeito às Regras 100%

    Sexo : Masculino
    Localização : Brasil
    Mensagens : 67
    Registrado : 19/01/2011

    Uso correto do ADODB para obter dados externos e gravar em tabela local Empty Re: Uso correto do ADODB para obter dados externos e gravar em tabela local

    Mensagem  marcos_novack em 8/4/2019, 18:12

    Boa tarde

    Perfeito!!!, achei que era limitação do VBA.

    Muito obrigado! fico no aguardo
    marcelo3092
    marcelo3092
    Intermediário
    Intermediário

    Respeito às Regras 100%

    Sexo : Masculino
    Localização : Brasil
    Mensagens : 179
    Registrado : 19/08/2010

    Uso correto do ADODB para obter dados externos e gravar em tabela local Empty Re: Uso correto do ADODB para obter dados externos e gravar em tabela local

    Mensagem  marcelo3092 em 9/4/2019, 00:00

    Boa noite na verdade cada caso é um caso tipo minha conexão e baseada na conexão kartofel vou te manda o modulo que estou usando para conexão:

    Repare que os dados de conexão é busca na tabela parametros.
    Crie um modulo com qualquer nome e salva este código e observe a versão do seu driver ODBC que no caso estou usando este. MySQL ODBC 5.1 Driver
    se o seu e difetente basta mudar o nome dele.
    e tambem crie a tabela _parametros com os campos servidor, senha, usuario, banco_dados



    Option Compare Database
    Global sMySQL As String

    '---------------------------------------------------------------------------------------
    ' PROCEDIMENTO: MySQL_Server
    ' DESCRIÇÃO: Carrega os dados para conexão ao servidor MySQL com base na tabela
    ' parametros
    '---------------------------------------------------------------------------------------

    Public Function MySQL_Server()

    MyslqServidor = DLookup("[Servidor]", "_parametros") 'Servidor Web
    MyslqUsuario = DLookup("[Usuario]", "_parametros") 'Usuário do banco de dados
    MyslqSenha = DLookup("[Senha]", "_parametros") 'Senha do banco de dados
    MyslqDatabase = DLookup("[Banco_Dados]", "_parametros") 'Database

    End Function


    '---------------------------------------------------------------------------------------
    ' PROCEDIMENTO: Conexao_Open
    ' DESCRIÇÃO: Realiza a conexão com o servidor MySQL
    '---------------------------------------------------------------------------------------
    Public Function Conexao_Open(csql)

    On Error GoTo meuerro

    Call MySQL_Server 'Carrega parametros do servidor


    If cn.State = 1 Then
    cn.Close
    End If

    If rs.State = 1 Then
    cn.Close
    End If


    'cn.Open "driver={MySQL ODBC 5.3 Unicode driver};server=" & MyslqServidor & ";database=" & MyslqDatabase & ";uid=" & MyslqUsuario & "; pwd=" & MyslqSenha
    cn.Open "driver={MySQL ODBC 5.1 Driver};server=" & MyslqServidor & ";database=" & MyslqDatabase & ";uid=" & MyslqUsuario & "; pwd=" & MyslqSenha
    rs.CursorLocation = adUseClient
    rs.Open csql, cn, adOpenDynamic, adLockOptimistic

    meuerro:
    If Err.Number = 80004005 Then
    MsgBox " Não há Conexão com a Internet"
    End If

    End Function



    Salvo isto abra o formulario que está a listbox.

    Crie esta função dentro do vb do formulario
    modifique o nome lista7 para a o nome da sua lista e altere ela na propriedade da lista na aba dados de tabela/consulta para lista de valores, no caso dos campos
    rs.Fields(0).Value onde o 0 corresponde a posição na tabela do mysql onde 0 é a primeira 1 segunda e assim por diante na aba formato da listbox coloque o numero de colunas
    que deseja mostrar e na propriedade largura das coluna altera ou coloque 1cm;1cm e assim por diante para vc pode visualizar os dados.


    Public Sub Carrega_Lista_Catalogo(csql, ForMy)


    Call Conexao_Open(csql)

    Dim FormAberto As Form 'Recebe o nome do formulário atual
    Set FormAberto = Forms(ForMy)

    FormAberto.Lista7 = Null 'Limpa seleção do registro
    FormAberto.Lista7.RowSource = "" 'Limpa os dados da caixa de listagem
    FormAberto.Lista7.Requery 'atualiza dados da caixa de listagem

    While (Not rs.EOF)

    FormAberto.Lista7.AddItem rs.Fields(0).Value & ";" & rs.Fields(2).Value & ";" & rs.Fields(3).Value & ";" & rs.Fields(13).Value & ";" & rs.Fields(45).Value & ";" & rs.Fields(4).Value & ";" & rs.Fields(5).Value & ";" & rs.Fields(7).Value & ";" & rs.Fields(Cool.Value & ";" & rs.Fields(22).Value & ";" & rs.Fields(23).Value & ";" & rs.Fields(24).Value & ";" & rs.Fields(25).Value & ";" & rs.Fields(26).Value & ";" & rs.Fields(27).Value & ";" & rs.Fields(28).Value & ";" & rs.Fields(29).Value & ";" & rs.Fields(37).Value & ";" & rs.Fields(38).Value & ";" & rs.Fields(39).Value & ";" & rs.Fields(40).Value & ";" & rs.Fields(41).Value & ";" & rs.Fields(42).Value & ";" & rs.Fields(43).Value & ";" & rs.Fields(44).Value

    rs.MoveNext 'vai para o proximo registro

    Wend

    rs.Close 'fecha o recorset
    cn.Close 'fecha o banco de dados


    End Sub


    feito isso basta chamar a função no evento ao abrir do formulario.


    Call Carrega_Lista_Catalogo("select * from tblprodutos order by Produto", Me.Form.Name)


    ele já vai mostrar os dados altere o nome da tabela para que mostre pra vc seus dados.

    e para filtrar a listbox crie uma caixa de texto e no evento da caixa apos atualizar ou crie um botão e coloque a seguinte função só altere os nomes dos seus campos.

    Dim valor_pesq As String

    valor_pesq = Me.Texto71

    Call Carrega_Lista_Catalogo("select * from tblprodutos where Produto like '%" & valor_pesq & "%' or Genero like '%" & valor_pesq & "%'", Me.Form.Name)

    essa parte Produto like '%" & valor_pesq & "%' vc pode adicionar quantos campos quiser para que a caixa de texto pesquise. veja a foto como fica.

    no meu caso criei um catalogo de produtos em access que ao cadastro o produto no cadastro vc adiciona a foto essa foto é enviada para um servidor via ftp e feita o download para poder exibir ai no catalogo ao escolher o produto carrega as miniaturas e ao clicar na miniatura ela abre e aparece tb a descrição da foto veja nas imagens.

    Cadastro de Produto https://drive.google.com/open?id=1t_6wrkeklJcCU98NR1ONyRtctVYhclh7
    Catalogo de produto https://drive.google.com/open?id=1vVLj0kTQZJsIEh326pAS7k_rKIRQnmnr
    Catalogo Filtro https://drive.google.com/open?id=129jjXjKj9GsISqdWOOHKG0rdXHjR3qWa

    E tudo online sem tabela temporaria.

    É isso qualquer duvida







    Avelino João
    Avelino João
    Avançado
    Avançado

    Respeito às Regras 100%

    Sexo : Masculino
    Localização : Angola
    Mensagens : 306
    Registrado : 10/03/2012

    Uso correto do ADODB para obter dados externos e gravar em tabela local Empty Re: Uso correto do ADODB para obter dados externos e gravar em tabela local

    Mensagem  Avelino João em 9/4/2019, 11:24

    Pessoal bom dia, Aqui é Minha Solução.

    Obs.: Quando precisar carregar a informação faça um filtro só da informação que precisa e não carregar toda informação.


    .................................................................................
    Proaccess - Angola
    marcos_novack
    marcos_novack
    Intermediário
    Intermediário

    Respeito às Regras 100%

    Sexo : Masculino
    Localização : Brasil
    Mensagens : 67
    Registrado : 19/01/2011

    Uso correto do ADODB para obter dados externos e gravar em tabela local Empty Re: Uso correto do ADODB para obter dados externos e gravar em tabela local

    Mensagem  marcos_novack em 9/4/2019, 18:10

    boa tarde!!!

    Avelino, não consigo testar sua solução porque não roda a biblioteca DAO aqui na minha maquina, estou desde cedo a tentar e não funfa, tem conflito e babababab porcaria...
       >>>Microsoft DAO 3.6 Object library.
       >>> Alternativamente, Microsoft Office 15x.0 Access Database Engine Object.

    vou testar em casa para ver se é eficiente .

    Marcelo,

    vou testar seu código, volta depois para dar retorno aqui.

    agradecimentos a todos aqui... by


    Última edição por marcos_novack em 10/4/2019, 23:59, editado 1 vez(es)
    Avelino João
    Avelino João
    Avançado
    Avançado

    Respeito às Regras 100%

    Sexo : Masculino
    Localização : Angola
    Mensagens : 306
    Registrado : 10/03/2012

    Uso correto do ADODB para obter dados externos e gravar em tabela local Empty Re: Uso correto do ADODB para obter dados externos e gravar em tabela local

    Mensagem  Avelino João em 10/4/2019, 08:42

    Bom Dia!!!

    Marco Novack não é porcaria nenhuma deve ter respeito sempre dos colegas, quando se trabalha com MySQL não podemos usar Microsoft DAO, e assim ADO deve activar Microsoft ADO Ext 2.8 for DDL and Security e Microsoft ActiveX Data Objects 2.8 Library.

    Escolher entre DAO e ADO


    .................................................................................
    Proaccess - Angola
    marcos_novack
    marcos_novack
    Intermediário
    Intermediário

    Respeito às Regras 100%

    Sexo : Masculino
    Localização : Brasil
    Mensagens : 67
    Registrado : 19/01/2011

    Uso correto do ADODB para obter dados externos e gravar em tabela local Empty Re: Uso correto do ADODB para obter dados externos e gravar em tabela local

    Mensagem  marcos_novack em 10/4/2019, 18:05

    MarcoS Novack não é porcaria nenhuma deve ter respeito sempre dos colegas;
    ***Me perdoem pelo palavrão Senhores, nada melhor que um dia apos o outro uffa.

    Avelino seu código é uma flecha, muito rápido!!! Resolveu meu impasse, obrigado! Estou falando do link que você disponibilizou  "aqui minha solução".  cheers

    Marcelo, testei tua dica para filtro ficou top, só não gostei da performance quando utilizo no evento "ao alterar" do campo filtro, na digitação do primeiro caractere trava um pouco depois roda normal.

    Agradeço a disponibilidade dos amigos em ajudar ao próximo!!!

    Parabéns

    Código:
    Function CopMySQL()
    Dim rstDao As DAO.Recordset ' Access

    CurrentDb.Execute "Delete From TblAdmTemp" 'Access

    Call Conexao_Open("SELECT * FROM tblAdm") 'Mysql

        Set rstDao = CurrentDb.OpenRecordset("SELECT * FROM tblAdmTemp") 'Access
        
        Do Until rs.EOF 'Mysql
        rstDao.AddNew ' Access
        'Copiar os campos do Mysql para Access, Registo por Registo
        rstDao("id").Value = rs("id").Value
        rstDao("lote").Value = rs("lote").Value
        rstDao("Cor").Value = rs("cor").Value
        rstDao("CodMalha").Value = rs("CodMalha").Value
        rstDao("Malha").Value = rs("Malha").Value
        rstDao("DataAdm").Value = rs("DataAdm").Value
        rstDao.Update
        rs.MoveNext 'Mysql
        Loop
        
        MsgBox "Dados Copiados com Sucesso", vbInformation, UtilizaVariavelGLobalstrMsgInc
                Cn.Close 'Mysql
            rstDao.Close 'Access
            
    End Function

    Conteúdo patrocinado

    Uso correto do ADODB para obter dados externos e gravar em tabela local Empty Re: Uso correto do ADODB para obter dados externos e gravar em tabela local

    Mensagem  Conteúdo patrocinado


      Data/hora atual: 11/12/2019, 00:49