Alter Table Access através de UserForm Excel VBA [RESOLVIDO]

Fabio Trindade

05-11-2016 01:25:48

Boa noite, Srs.

Depois de algum tempo, estou de volta no FÓRUM ... Claro com uma duvida punk!
Como faço para mandar um alter table em uma tabela no Access através de um UserForm Excel?
Minha aplicação realiza a conexão com o banco do access da seguinte forma:

Em um Módulo Classe tenho

Código: Selecionar todos

Public Conn As New ADODB.Connection

Public Sub Conectar()

Dim nConectar As String
Dim BancoDados As String
    
    BancoDados = Plan2.Range("D6")
    
    If Val(Application.Version) < 12 Then
        nConectar = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & BancoDados & ";Jet OLEDB:Database"
    Else
        nConectar = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & BancoDados & ";Jet OLEDB:Database"
    End If
    
    Conn.ConnectionString = nConectar
    Conn.Open
    
End Sub

Public Sub Desconectar()
    Conn.Close
End Sub
Em seguida em um UserForm realizado a verificação se existe os campos que estou procurando na tabela e tento inserir no seguinte formato:

Código: Selecionar todos



Function VerificaCampoTabela() As Boolean

Dim Tabela As String
Dim Campos As String
Dim vCampo1 As Long
Dim vCampo2 As Long
Dim vCampo3 As Long

On Error GoTo Erro
         
    SQL = "SELECT * FROM ESTACAONEW"
         
    Set Banco = New ADODB.Recordset
         
    ConexaoBanco.Conectar
         
    ' ABRE A CONEXAO DO BANCO
    Banco.Open SQL, ConexaoBanco.Conn, adOpenForwardOnly, adLockOptimistic
         
        ' VERIFICA SE O CAMPO "LATITUDENEW" EXISTE NA TABELA
        If Not Banco.Fields("LatitudeNew") Is Nothing Then
            Campos = Campos & vbNewLine & "  LatitudeNew"
            vCampo1 = 1
        End If
        
        ' VERIFICA SE O CAMPO "LONGITUDENEW" EXISTE NA TABELA
        If Not Banco.Fields("LongitudeNew") Is Nothing Then
            Campos = Campos & vbNewLine & "  LongitudeNew"
            vCampo2 = 1
        End If
        
        ' VERIFICA SE O CAMPO "ALTITUDENEW" EXISTE NA TABELA
        If Not Banco.Fields("AltitudeNew") Is Nothing Then
            Campos = Campos & vbNewLine & "  AltitudeNew"
            vCampo3 = 1
        End If
    
        MsgBox "A tabela ESTACAO recebeu novos campos, são eles:" & vbNewLine & Campos
    
    ' LIMPA A CONEXAO DO BANCO
    Set Banco = Nothing
    
    ' ENCERA A CONEXAO DO BANCO
    ConexaoBanco.Desconectar
    
    ' FINALIZA A FUNCAO
    Exit Function

Erro:
    
    ' CASE COM A VERIFICACAO DO CAMPO
    Select Case Err
        ' O CAMPO NAO EXISTE
        Case 3265
        
            ' SQL INSERIR COLUNAS LATITUDENEW - LONGITUDENEW - ALTITUDENEW
            If vCampo1 = 0 Then
                SQL = ""
                SQL = SQL & "ALTER TABLE ESTACAONEW ADD COLUMN LatitudeNew LONG; "
                MsgBox "Criado o campo LATITUDENEW "
            ElseIf vCampo2 = 0 Then
                SQL = ""
                SQL = SQL & "ALTER TABLE ESTACAONEW ADD COLUMN LongitudeNew LONG; "
                MsgBox "Criado o campo LONGITUDENEW "
            ElseIf vCampo3 = 0 Then
                SQL = ""
                SQL = SQL & "ALTER TABLE ESTACAONEW ADD COLUMN AltitudeNew LONG; "
                MsgBox "Criado o campo ALTITUDENEW"
            End If
            
            Resume Next
            
        ' O CAMPO EXISTE
        Case Else
            MsgBox "Outro tipo de erro"
    End Select
    
    ' LIMPA A CONEXAO DO BANCO
    Set Banco = Nothing
    
    ' ENCERA A CONEXAO DO BANCO
    ConexaoBanco.Desconectar

End Function
Porém não esta funcionando onde estou errando?
Tentei mais esse dois códigos ..
Assim
Banco.Execute "ALTER TABLE ESTACAONEW ADD COLUMN LatitudeNew LONG;"
e assim
DoCmd.RunSQL "ALTER TABLE ESTACAONEW ADD COLUMN LatitudeNew LONG;".

Mas sem sucesso .... Gostaria de uma ajuda dos senhores !

Mikel Silveira Fraga

05-11-2016 12:15:42

Trindade, bom dia. Tudo bem cara.

Olha, nunca fiz esse tipo de situação, mas não é possível fazer isso com o ADO, apenas com o ADOX.

A Linguagem SQL é dividida em dois conceitos: DDL (Data Definition Language, ou Linguagem de Definição de Dados) e DML (Data Manipulation Language, ou Linguagem de Manipulação de Dados).

Basicamente, o ADO é utilizado para se trabalhar com os comandos DML básicos (select, insert, update e delete). Como o próprio nome diz, é uma manipulação dos dados existentes na tabela.

Já o ADOX, é utilizado para se trabalhar com os comandos DDL básicos (create, alter e drop). Também é possível trabalhar com criação de triggers, views e outros recursos de bancos de dados mais robustos.

Seguem dois links, que podem lhe ajudar a compreender o uso do ADOX:
- ADOX Fundamentals
- ADO, ADOX - Manual de Sobrevivência

Essa biblioteca já esta presente no Excel e pode ser utilizada normalmente.

Espero que a explicação e os links possam lhe ajudar.

Abraços e excelente final de semana.

Fabio Trindade

06-11-2016 03:29:27

Bom dia, Mikel.

Primeiro, muito obrigado por sua explicação achei bastante interessante a questão de diferenciar o que é DDL de DML pois eu não sabia desses detalhes.
Os link's em seu comentário foram de muita ajuda, estou desde de cedo quebrando cabeça lendo, entendendo e testando os código.

E por fim conseguir fazer o que precisava, ficou assim meu código:

Código: Selecionar todos

Function VerificaCampoTabela() As Boolean

Dim Campos As String
Dim vCampo1 As Long
Dim vCampo2 As Long
Dim vCampo3 As Long

Dim Tabela As ADOX.Table
Dim NomeColuna As New ADOX.Column
Dim BancoDados As String
Dim CaminhoBancoDados As String
Dim NomeTabela As String

NomeTabela = "ESTACAONEW"

On Error GoTo Erro
         
    SQL = "SELECT * FROM " & NomeTabela & ";"
         
    Set Banco = New ADODB.Recordset
         
    ConexaoBanco.Conectar
         
    ' ABRE A CONEXAO DO BANCO
    Banco.Open SQL, ConexaoBanco.Conn ', adOpenForwardOnly, adLockOptimistic
         
        ' VERIFICA SE O CAMPO "LATITUDENEW" EXISTE NA TABELA
        If Not Banco.Fields("LatitudeNew") Is Nothing Then
            vCampo1 = 1
        End If
        
        ' VERIFICA SE O CAMPO "LONGITUDENEW" EXISTE NA TABELA
        If Not Banco.Fields("LongitudeNew") Is Nothing Then
            vCampo2 = 1
        End If
        
        ' VERIFICA SE O CAMPO "ALTITUDENEW" EXISTE NA TABELA
        If Not Banco.Fields("AltitudeNew") Is Nothing Then
            vCampo3 = 1
        End If
        
    
    ' LIMPA A CONEXAO DO BANCO
    Set Banco = Nothing
    
    ' ENCERA A CONEXAO DO BANCO
    ConexaoBanco.Desconectar
    
    ' VERIFICA A VARIAVEL CAMPO <> 1 PARA DISPARAR MSGBOX PARA O USUARIO
    ' INFORMANDO QUE AS COLUNAS FORAM INSERIRAS NO BANCO
    If vCampo1 <> 0 Or vCampo2 <> 0 Or vCampo3 <> 0 Then

        MsgBox "A tabela " & NomeTabela & " recebeu novos campos, são eles:" & vbNewLine & Campos, _
        vbInformation, "Atenção"
    
    End If
    
    ' FINALIZA A FUNCAO
    Exit Function

Erro:
    
    ' CAMINHO DO BANCO DE DADOS
    BancoDados = Plan2.Range("D6")
    
    If Val(Application.Version) < 12 Then
        CaminhoBancoDados = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & BancoDados & ";Jet OLEDB:Database"
    Else
        CaminhoBancoDados = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & BancoDados & ";Jet OLEDB:Database"
    End If
    
    ' ABRE O CATALOGO
    ConexaoNew.ActiveConnection = CaminhoBancoDados
    
    ' SETA A TABELA DEFINIDA NA VARIACEL NOMETABELA
    Set Tabela = ConexaoNew.Tables(NomeTabela)
    
    ' CASE COM A VERIFICACAO DO CAMPO
    Select Case Err
        ' O CAMPO NAO EXISTE
        Case 3265
        
            ' VERIFICA COLUNAS FALTANTES LATITUDENEW - LONGITUDENEW - ALTITUDENEW
            If vCampo1 = 0 Then
            
                ' ENCERA A CONEXAO ANTERIOR COM O BANCO DE DADOS
                ConexaoBanco.Desconectar
                
                ' ALTER TABLE COM A NOVA COLUNA LATITUDENEW
                ' DEFINI O TIPO DE DADOS DA COLUNA
                ' ADD A COLUNA NA TABELA
                NomeColuna.Name = "LatitudeNew"
                NomeColuna.Type = adDouble
                ConexaoNew.Tables(NomeTabela).Columns.Append NomeColuna.Name, adDouble

                Campos = Campos & "     » LatitudeNew" & vbNewLine
                
            ElseIf vCampo2 = 0 Then
            
                ' ENCERA A CONEXAO DO BANCO
                ConexaoBanco.Desconectar
                
                ' ALTER TABLE COM A NOVA COLUNA LONGITUDENEW
                ' DEFINI O TIPO DE DADOS DA COLUNA
                ' ADD A COLUNA NA TABELA
                NomeColuna.Name = "LongitudeNew"
                NomeColuna.Type = adDouble
                ConexaoNew.Tables(NomeTabela).Columns.Append NomeColuna.Name, adDouble
                
                Campos = Campos & "     » LongitudeNew" & vbNewLine
                
            ElseIf vCampo3 = 0 Then
                
                ' ENCERA A CONEXAO DO BANCO
                ConexaoBanco.Desconectar
                
                ' ALTER TABLE COM A NOVA COLUNA ALTITUDENEW
                ' DEFINI O TIPO DE DADOS DA COLUNA
                ' ADD A COLUNA NA TABELA
                NomeColuna.Name = "AltitudeNew"
                NomeColuna.Type = adDouble
                ConexaoNew.Tables(NomeTabela).Columns.Append NomeColuna.Name, adDouble
                
                Campos = Campos & "     » AltitudeNew" & vbNewLine
                
            End If
                        
            ' LIMPAR AS VARIAVEIS
            Set ConexaoNew.ActiveConnection = Nothing
            Set ConexaoNew = Nothing
            Set Tabela = Nothing
            
            ' ENCERRA A CONEXAO
            ConexaoBanco.Conectar
            
            ' RETORNO NA INTRUCAO ANTERIOR
            Resume Next
            
        ' O CAMPO EXISTE
        'Case Else
        
        '    MsgBox "Outro tipo de erro"
    
    End Select
    
Exit Function
    
    ' LIMPA A CONEXAO DO BANCO
    Set Banco = Nothing
    
    ' ENCERA A CONEXAO DO BANCO
    ConexaoBanco.Desconectar

End Function
Muito obrigado pela resposta, valew.

Mikel Silveira Fraga

06-11-2016 13:15:14

Trindade, bom dia.

Cara, excelente notícia cara. Como havia dito, nunca utilizei essa rotina, mas sabia que poderia funcionar do jeito que estava precisando.

Tendo dúvidas, é só mandar.

Abraços e excelente domingo.