https://www.youtube.com/watch?v=hQb-QQYQv-g

Extrair DDL Access em SQL – Como extrair criar tabelas Access em SQL

Neste artigo você verá como extrair DDL do Access em SQL, ou seja, como extrair o criar tabelas do Access em script SQL.

Este código foi desenvolvido há mais de 10 anos pelo mestre e amigo Adelson Silva ao qual agradeço pela colaboração, pois me ajudou muito em um projeto e acredito que possa ajudar várias pessoas.

Extrair o SQL de criação de tabelas Access

O Access não tem por padrão uma opção, como têm o SQL Server e outros bancos relacionais de exportar a estrutura do banco de dados em SQL.

Esta opção facilita muito a migração de bancos, alterações de estruturas e criação dinâmica de bancos em determinadas situações.

Pense por exemplo que você tem um sistema que você distribui para vários clientes e ele possui um banco de dados access.

Você poderia por exemplo enviar uma atualização via criação de tabelas e alteração e inclusão de campos para executar na sua planilha Excel ao abrir ela.

Isso pode ser feito manualmente, claro, mas pode ser um trabalho grande, principalmente quando se trata de muitos códigos.

Pensando nesta situação o Adelson criou um código que ao ser executado cria um arquivo txt com toda a criação em SQL da DDL, da criação de tabelas, relacionamentos, chaves, tudo criado dinamicamente e já em um formato que pode ser colocado em um módulo VBA Excel e executado para a criação de toda a estrutura da tabela novamente.

Como criar o script SQL de criação de tabelas do Access automaticamente

Para extrair o script SQL de criação da sua estrutura de um banco de dados Access siga as seguintes etapas:

1. Abra o seu projeto no Access
2. Pressione ALT+F11 para acessar o seu código VBA
3. Crie um módulo. Para criar o módulo clique no VBE no menu Inserir->Módulo
4. No módulo criado cole o seguinte código VBA

Option Compare Database
'Criado por https://www.linkedin.com/in/adelsonrms
Sub ExtrairDDLTabelas()

    Dim DB As Database
    Dim tdf As TableDef
    Dim fld As DAO.Field
    Dim ndx As DAO.Index
    Dim strSql As String
    Dim strFlds As String
    Dim strCn As String

    Dim fs, f

    Set DB = CurrentDb

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.createTextFile("C:\Temp\Schema.txt")

    f.WriteLine "Sub CreateTables()"
    f.WriteLine ""


    For Each tdf In DB.TableDefs
        If Left(tdf.Name, 4) <> "Msys" Then
            strSql = "strSQL=""CREATE TABLE [" & tdf.Name & "] ("

            strFlds = ""

            For Each fld In tdf.Fields

                strFlds = strFlds & ",[" & fld.Name & "] "

                Select Case fld.Type

                Case dbText
                    'No look-up fields
                    strFlds = strFlds & "Text (" & fld.Size & ")"

                Case dbLong
                    If (fld.Attributes And dbAutoIncrField) = 0& Then
                        strFlds = strFlds & "Long"
                    Else
                        strFlds = strFlds & "Counter"
                    End If

                Case dbBoolean
                    strFlds = strFlds & "YesNo"

                Case dbByte
                    strFlds = strFlds & "Byte"

                Case dbInteger
                    strFlds = strFlds & "Integer"

                Case dbCurrency
                    strFlds = strFlds & "Currency"

                Case dbSingle
                    strFlds = strFlds & "Single"

                Case dbDouble
                    strFlds = strFlds & "Double"

                Case dbDate
                    strFlds = strFlds & "DateTime"

                Case dbBinary
                    strFlds = strFlds & "Binary"

                Case dbLongBinary
                    strFlds = strFlds & "OLE Object"

                Case dbMemo
                    If (fld.Attributes And dbHyperlinkField) = 0& Then
                        strFlds = strFlds & "Memo"
                    Else
                        strFlds = strFlds & "Hyperlink"
                    End If

                Case dbGUID
                    strFlds = strFlds & "GUID"

                End Select

            Next

            strSql = strSql & Mid(strFlds, 2) & " )""" & vbCrLf & "Currentdb.Execute strSQL"

            f.WriteLine vbCrLf & strSql

            'Indexes
            For Each ndx In tdf.Indexes

                If ndx.Unique Then
                    strSql = "strSQL=""CREATE UNIQUE INDEX "
                Else
                    strSql = "strSQL=""CREATE INDEX "
                End If

                strSql = strSql & "[" & ndx.Name & "] ON [" & tdf.Name & "] ("

                strFlds = ""

                For Each fld In tdf.Fields
                    strFlds = ",[" & fld.Name & "]"
                Next

                strSql = strSql & Mid(strFlds, 2) & ") "

                strCn = ""

                If ndx.Primary Then
                    strCn = " PRIMARY"
                End If

                If ndx.Required Then
                    strCn = strCn & " DISALLOW NULL"
                End If

                If ndx.IgnoreNulls Then
                    strCn = strCn & " IGNORE NULL"
                End If

                If Trim(strCn) <> vbNullString Then
                    strSql = strSql & " WITH" & strCn & " "
                End If

                f.WriteLine vbCrLf & strSql & """" & vbCrLf & "Currentdb.Execute strSQL"
            Next
        End If
    Next

    f.WriteLine ""
    f.WriteLine "End Sub"

    f.Close

End Sub

5. Pressione F5 e veja a criação do arquivo. É necessário que você tenha uma pasta em C:\Temp\, ou altere no código este caminho. O arquivo criado será o Schema.txt.

Pronto. Esse código é responsável pela criação de uma DDL pronta para ser executada dentro do VBA do Excel ou Access para a criação de toda a estrutura do banco de dados atual.

Criar tabelas access automaticamente no Excel

O arquivo com a estrutura SQL para a criação do banco de dados completo acima já está pronta para ser executada diretamente no Excel por exemplo.

Para isso você deve conectar o Excel no banco de dados aonde deseja criar esta estrutura e depois somente copiar o código fonte, por exemplo.

No gif acima é demonstrado como é prático o uso, com o seguinte código adaptado, é feito:

1. Conexão com a base
2. Chamada a criação das tabelas pelo método CreateTable
3. Desconectada a base

Para realizar a criação das tabelas do Access utilizando VBA Excel siga os seguintes passos:

1. Abra o Excel e pressione ALT+F11 para abrir o VBE
2. Crie um módulo, clicando em Inserir->Módulo
3. Clique no menu Projetos->Referências e marque a opção Microsoft ActiveX Data Objects x.x, use a versão mais recente
   

   

4. Cole o seguinte código abaixo e depois abra o txt gerado de SQL para criação de tabelas access e cole abaixo do código.

Option Explicit

Global Currentdb As ADODB.Connection

Public Sub lsCriarEstrutura()
    gsConectarBD
    CreateTables
    gsDesconectarBD
End Sub

Public Sub gsConectarBD()
    Dim SQL As String
    
    If Currentdb Is Nothing Then
        Set Currentdb = New ADODB.Connection
    End If
    
    If Currentdb.State <> 1 Then
            SQL = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & "c:\temp\teste.accdb;"

        Currentdb.Open SQL
    End If
End Sub

Public Sub gsDesconectarBD()
    Dim SQL As String
    
    If Currentdb Is Nothing Then
        Exit Sub
    Else
        If Currentdb.State <> 0 Then
            Currentdb.Close
        End If
    End If
End Sub

5. Altere no código acima o caminho C:\TEMP\TESTE.ACCDB colocando o caminho do seu banco de dados access
6. Clique em lsCriarEstrutura e pressione F9 para que o Excel execute o SQL e crie a estrutura automaticamente no seu banco de dados Access.

Conclusão e agradecimento

A extração do SQL de criação de estrutura das tabelas access deveria ser algo nativo neste banco de dados.

Felizmente o mestre Adelson Silva desenvolveu este código que auxilia muito nesta tarefa e que pode ser usado para:

1. Replicar um banco de dados em uma base limpa sem ter que limpar todas as tabelas e zerar os contadores
2. Usar parte do código para atualizar bancos de dados de clientes sem ter que alterar a estrutura do access manualmente
3. Analisar a estrutura SQL em busca de erros estruturais

Então, mais uma vez agradecemos o trabalho do Adelson Silva, clique no nome dele para acessar o seu Linkedin.

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: