VBA Access - Usando o DML - Data Definition Language - Using DML (Data Definition Language) Effectively

Exemplos de Código com o DDL

O SQL padrão é uma sublinguagem utilizada no MS Access para lidar com os dados, tabelas, querys, etc...

Objeto Tipo

Tabela 1

Query 5

Tabela Conectada 4, 6, or 8

Formulário -32768

Relatório -32764

Módulo -32761

Data Manipulation Language (DML): O comando SELECT e queries de ação (DELETE, UPDATE, INSERT INTO, ...)

Data Definition Language (DDL): Comandos que alterem o "schema" (Mudando tabelas, campos, índices, relações, queries, etc.)

Usando o DML para queries, poderemos ler alguns aspectos do "schema" do banco de dados.

Poderá listar os objetos na base de dados Access como abaixo:

SELECT MSysObjects.Type, MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Name Not Like "~*" ORDER BY MSysObjects.Type, MSysObjects.Name;

Onde Type poderá colocar um dos valores da tabela acima. (Infelizmente, o modo provido pelo DML não é o caminho mais fácil para se ler os nomes dos campos nas tabelas.)

O DDL provê outras características de intervenção como:

CREATE TABLE para gerar uma nova tabela, especificando os nomes dos campos, tipos, e constraints.

ALTER TABLE para adicionar uma coluna para a tabela, deletar uma coluna na tabela, ou mudar a tabela como tipo e tamanho da mesma.

DROP TABLE para deletar uma tabela.

Similarmente, você pode aplicar o comandos CREATE/ALTER/DROP em outras coisas tais como índices, constraints, views e procedures (queries), usuário e grupos (segurança.)

Enquanto o DDL é importante para algumas bases de dados enormes, ele é limitado no uso com o MS Access. Você pode criar um campo Texto, mas não pode configurá-lo com a propriedade Largura Diferente de Zero, ou características similares. Pode criar um campo Yes/No, mas não pode dizer que o dataentry ocorrerá por meio de um text box, ou um check box. Também poderá criar um campo Date/Time, mas não poderá configurar a sua propriedade Format. DDL não pode criar campos Hyperlink, ou campos Attachment.

Poderá executar uma query DDL sob o DAO ou ADO.

Parar DAO, use: dbEngine(0)(0).Execute strSql, dbFailOnError

Parar ADO, use: CurrentProject.Connection.Execute strSql

Algumas características do JET 4 (Access 2000 e superior) são suportados somente sob o ADO.

Uma situação na qual o DDL é realmente utilizável é quanto a mudança do Tipo ou Tamanho dos campos. Você não pode fazer isto com o DAO ou ADOX, utilizar o DDL é a técnica mais prática para estes fins. Obviamente existem outras saídas mais trabalhosas e incoerentes pelo simples fato de serem mais demoradas tanto na implementação quanto na execução.

Abaixo disponibilizo alguns exemplos para que você possa iniciar-se nas técnicas de utilização do DDL.

Índice das Funções	Descrição
CreateTableDDL()	Cria duas tabelas, seus índices e relacionamentos, ilustrando os diferentes tipos de campos suas propriedades configuradas.
CreateFieldDDL()	Ilustra como adicionar um campo para uma tabela.
CreateFieldDDL2()	Adiciona um campo a uma tabela em outra base de dados.
CreateViewDDL()	Cria uma nova query.
DropFieldDDL()	Deleta o campo de uma tabela.
ModifyFieldDDL()	Muda o tipo ou tamanho de um campo. (Este é o mais comum uso do DDL.)
AdjustAutoNum()	Configura o start da AutoNumeração.
DefaultZLS()	Cria um campo que tem por default ser uma stringque não suporta ficar vazia.

   15.01.2025

Option Compare Database
Option Explicit

' Função para criar a tabela tblDdlContractor
Sub CreateTableDDL()
' Declaração das variáveis necessárias
Dim cmd As New ADODB.Command
Dim strSql As String

' Define a conexão ativa para o comando
Let cmd.ActiveConnection = CurrentProject.Connection

' Cria a tabela tblDdlContractor com seus campos e restrições
strSql = "CREATE TABLE tblDdlContractor " & _
"(ContractorID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Surname TEXT(30) WITH COMP NOT NULL, " & _
"FirstName TEXT(20) WITH COMP, " & _
"Inactive YESNO, " & _
"HourlyFee CURRENCY DEFAULT 0, " & _
"PenaltyRate DOUBLE, " & _
"BirthDate DATE, " & _
"EnteredOn DATE DEFAULT Now(), " & _
"Notes MEMO, " & _
"CONSTRAINT FullName UNIQUE (Surname, FirstName));"

' Executa o comando SQL para criar a tabela
Let cmd.CommandText = strSql
cmd.Execute

' Imprime mensagem de sucesso
Debug.Print "tblDdlContractor criada."

' Cria a tabela tblDdlBooking
strSql = "CREATE TABLE tblDdlBooking " & _
"(BookingID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"BookingDate DATE CONSTRAINT BookingDate UNIQUE, " & _
"ContractorID LONG REFERENCES tblDdlContractor (ContractorID) " & _
"ON DELETE SET NULL, " & _
"BookingFee CURRENCY, " & _
"BookingNote TEXT(255) WITH COMP NOT NULL);"

' Executa o comando SQL para criar a tabela
cmd.CommandText = strSql
cmd.Execute

' Imprime mensagem de sucesso
Debug.Print "tblDdlBooking criada."
End Sub

' Função para adicionar um novo campo à tabela MyTable
Sub CreateFieldDDL()
' Declaração das variáveis necessárias
Dim strSql As String
Dim db As DAO.Database

' Inicializa a conexão com o banco de dados
Set db = CurrentDb()

' SQL para adicionar o novo campo à tabela
strSql = "ALTER TABLE MyTable ADD COLUMN MyNewTextField TEXT(5);"

' Executa o comando SQL para adicionar o campo
db.Execute strSql, dbFailOnError

' Libera recursos
Set db = Nothing

' Imprime mensagem de sucesso
Debug.Print "MyNewTextField adicionado para MyTable"
End Sub

' Função para adicionar um campo à tabela de um banco de dados externo
Function CreateFieldDDL2()
' Declaração das variáveis necessárias
Dim strSql As String
Dim db As DAO.Database

' Inicializa a conexão com o banco de dados
Set db = CurrentDb()

' SQL para adicionar o novo campo à tabela no banco de dados externo
strSql = "ALTER TABLE Table IN 'C:\A&A\Junkki.mdb' ADD COLUMN MyNewField TEXT(5);"

' Executa o comando SQL para adicionar o campo
db.Execute strSql, dbFailOnError

' Libera recursos
Set db = Nothing

' Imprime mensagem de sucesso
Debug.Print "MyNewField Adicionado!"
End Function

' Função para criar uma visualização (VIEW)
Function CreateViewDDL()
' Declaração da variável para o comando SQL
Dim strSql As String

' SQL para criar a visualização (VIEW)
strSql = "CREATE VIEW qry1 AS SELECT tblInvoice.* FROM tblInvoice;"

' Executa o comando SQL para criar a visualização
CurrentProject.Connection.Execute strSql

' Imprime mensagem de sucesso
Debug.Print "Visualização qry1 criada."
End Function

' Função para excluir um campo de uma tabela
Sub DropFieldDDL()
' Declaração da variável para o comando SQL
Dim strSql As String

' SQL para excluir o campo da tabela
strSql = "ALTER TABLE [MyTable] DROP COLUMN [DeleteMe];"

' Executa o comando SQL para excluir o campo
DBEngine(0)(0).Execute strSql, dbFailOnError

' Imprime mensagem de sucesso
Debug.Print "Campo DeleteMe excluído da tabela MyTable."
End Sub

' Função para modificar um campo de uma tabela
Sub ModifyFieldDDL()
' Declaração da variável para o comando SQL
Dim strSql As String

' SQL para alterar o tipo de um campo (alterando o tamanho do campo MyText2Change)
strSql = "ALTER TABLE MyTable ALTER COLUMN MyText2Change TEXT(100);"

' Executa o comando SQL para modificar o campo
DBEngine(0)(0).Execute strSql, dbFailOnError

' Imprime mensagem de sucesso
Debug.Print "Campo MyText2Change alterado para TEXT(100)."
End Sub

' Função para ajustar a configuração do tipo de campo COUNTER
Function AdjustAutoNum()
' Declaração da variável para o comando SQL
Dim strSql As String

' SQL para ajustar o valor inicial do campo COUNTER
strSql = "ALTER TABLE MyTable ALTER COLUMN ID COUNTER(1000,1);"

' Executa o comando SQL para ajustar o campo COUNTER
CurrentProject.Connection.Execute strSql

' Imprime mensagem de sucesso
Debug.Print "Campo COUNTER ID ajustado para iniciar em 1000."
End Function

' Função para adicionar um campo com valor padrão (ZLS)
Function DefaultZLS()
' Declaração da variável para o comando SQL
Dim strSql As String

' SQL para adicionar um campo com valor padrão vazio (ZLS)
strSql = "ALTER TABLE MyTable ADD COLUMN MyZLSfield TEXT(100) DEFAULT ' ';"

' Executa o comando SQL para adicionar o campo
CurrentProject.Connection.Execute strSql

' Imprime mensagem de sucesso
Debug.Print "Campo MyZLSfield adicionado com valor padrão."
End Function