ADOX - Gerenciando seu banco de dados

ADOX - Gerenciando o seu banco de dados

Com a ADO podemos acessar , armazenar e recuperar dados de uma fonte de dados de uma maneira simples e rápida. No entanto se você precisar de mais informações sobre os objetos do seu banco de dados (tabelas, procedimentos, indíces, etc...) ou quere implementar a segurança ( grupos , usuários,etc...) , só a ADO não basta ,você vai precisar de algo mais...

A ADOX ( ADO Extension for DDL and Security) é uma extensão do modelo objeto ADO padrão que oferece objetos para manipular informações sobre os objetos do seu banco dados e questões relacionadas a segurança. Ela fornece os métodos , eventos e propriedades

Ao usar a DAO , você tinha a disposição toda a funcionalidade para armazenar e recuperar dados , manipular os objetos do seu banco dados como também implementar a segurança. Com uma única referência a biblioteca DAO tudo estava a sua disposição. Com a ADO é diferente...

Existem na ADO três modelos de objetos distintos que juntos fornecem toda a funcionalidade que você encontrava na DAO. Vejamos cada um deles e um resumo de suas funções

Microsoft ActiveX Data Objects 2.x ( ADODB) - Permite acessar e manipular dados em uma fonte de dados usando um provedor OLE DB.
Microsoft ActiveX Data Objects Extensions for DDL and Security 2.x ( ADOX) - Permite criar e modificar estrutura de banco de dados , tabelas e consultas , bem como criar e modificar contas de grupos e usuários , atribuindo e revogando permissões e acessos aos objetos. Trabalha com qualquer provedor OLE DB que suporta a interface.
Microsoft Jet and Replication Objects 2.x ( JRO ) - Permite criar , modificar e sincronizar replicas de seu banco de dados. Funciona somente com o Provedor OLE DB Jet 4.0 da Microsoft.

A funcionalidade esta dividida assim dividida entre estes três modelos por uma questão de economia de recursos, pois em muitas aplicações você não vai precisar de toda a funcionalidade da ADO e, portanto, vai precisar referenciar somente a biblioteca adequada a tarefa que você vai realizar.

Neste artigo iremos focar a ADOX e sua funcionalidade e aprender a trabalhar com seus objetos nas tarefas mais comuns relacionadas com banco de dados.

A ADOX não é parte integrante da ADO e sim uma biblioteca que a acompanha, assim no seu projeto você vai precisar fazer a referência a Microsot ADO Ext. 2.X for DLL and Security.

Abaixo temos um esboço do modelo do objeto da ADOX :

Nota:

Você pode usar o método OpenSchema da ADO para recuperar informações do esquema ( definições ) de uma fonte de dados.

Infelizmente ao usar OpenSchema você não vai poder alterar o esquema , apenas visualizá-lo.

Veja o artigo :

ADO - Obtendo Informações sobre a fonte de dados para saber como utilizar este método com a ADO.

Como a ADOX é uma extensão da ADO há muitos elementos no modelo acima ( e que não foram desenhados) com os quais você já esta familiarizado, por exemplo , para iniciar o trabalho com um banco de dados você vai usar o já conhecido objeto Connection da ADO.

Antes de iniciar vamos deixar claro que a ADOX suporta somente provedores OLE DB como fonte de dados , os drivers ODBC não são suportados. Além disto somente o provedor Jet OLE DB suporta toda a funcionalidade do ADOX outros provedores devem suportar apenas parte da funcionalidade ADOX. Aqui , estaremos trabalhando somente com banco de dados Jet.

O objeto Catalog está no topo da 'hierarquia', ou seja , ele é o objeto raiz do modelo ADOX. Com ele podemos acessar e manter tabelas, visualizações , procedimentos e todos os objetos de segurança. Podemos usá-lo de duas maneiras: Abrindo um Catalogo ou Criando um Catálogo Novo.

Abrindo um Catálogo já existente

Um catálogo já existente pode ser uma fonte de dados ( Access , SQl Server ) que suporte a funcionalidade ADOX. Abaixo temos o código que mostra como abrir um catálogo para um banco de dados Access.As etapas envolvidas são:

Abrir o novo projeto padrão no VB
Referenciar a biblioteca ADO
Referenciar a biblioteca ADOX

Dim con as ADODB.Connection
Dim cat as ADOX.Catalog

Set con = New ADODB.Connection
Set cat = New ADOX.Catalog

'--Para um banco de dados Access: Biblio.mdb -----------------

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\teste\biblio.mdb"
Set cat.ActiveConnection = con

Criando um novo Catálogo

Para criar um catálogo vamos precisar de apenas uma linha de código. Por exemplo: Você pode querer criar um catálogo para fazer o backup dos dados de outro banco de dados.

Dim cat as New ADOX.Catalog

'--Para um criar um catálogo novo de banco de dados Access: teste.mdb -----------------

cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\teste\teste.mdb"

Vamos ao que interessa: gerenciamento de tabelas

Vamos mostrar como usar a ADOX para visualizar informações das tabelas de um banco de dados . A ADOX utiliza o objeto Table e seus dependentes ( colunas, índices e chaves) para obter estas informações. Vamos usar os seguintes objetos:

Catalog
- Table - Name e Type (para exibir o nome e o tipo da tabela)

Abaixo temos o código que lista todas as tabelas - nome e tipo - do banco de dados biblio.mdb e as exibe em um listbox:

Private Sub Command1_Click()

Dim con As New ADODB.Connection

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

'estabelecendo a conexao
Set con = New ADODB.Connection
conexao = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\teste\biblio.mdb"
con.Open conexao
Set cat.ActiveConnection = con

'exibindo o nome e os tipos das tabelas
For Each tbl In cat.Tables
listatabelas.AddItem tbl.Name & vbTab & tbl.Type
Next

End Sub

- Declaramos uma variável con usando como um objeto Conection da ADO
- A seguir declaramos as variáveis cat - objeto catalog - e tbl - objeto Table da ADOX
- Iniciamos a conexão e a abrimos definindo o catalogo para a conexão ativa
- Depois usando um laço for/each percorremos todas a tabelas do objeto Catalog e inserimos em um controle listbox - Listabelas - o nome da tabela ( tbl.Name ) e o seu Tipo ( tbl.Type )

O resultado é o seguinte:

Observe os tipos das tabelas:

VIEW - Uma visão , ou Consulta no Access

TABLE - uma tabela do usuário

ACCESS TABLE - uma tabela do Access

SYSTEM TABLE - uma tabela do sistema

Podemos obter a data da criação da tabela usando a propriedade DateCreated ou quando ela foi modificada usando a propriedade DateModified , o código fica assim:

For Each tbl In cat.Tables
listatabelas.AddItem tbl.Name & vbTab & tbl.DateCreated & vbTab & tbl.DateModified
Next

Exibindo informações sobre os campos (Colunas)

Para ver as informações sobre os campos das tabelas ( colunas ) devemos usar o objeto Column . Para isto vamos declarar a variável objeto Col como do tipo ADOX.Column e depois usando um laço for/each listaremos cada coluna para cada tabela exibindo-as no controle lixtbox - listacolunas:

Private Sub Command1_Click()

Dim con As New ADODB.Connection

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim Col As New ADOX.Column

'estabelecendo a conexao
Set con = New ADODB.Connection
conexao = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\teste\biblio.mdb"
con.Open conexao
Set cat.ActiveConnection = con

'exibindo o nome e os tipos das tabelas
For Each tbl In cat.Tables
     listatabelas.AddItem tbl.Name
     For Each Col In tbl.Columns
          listacolunas.AddItem Col.Name
     Next
Next

End Sub

Da mesma forma poderiamos obter informações sobre o tamanho de cada Coluna(campo) através da propriedade DefinedSize.

Exibindo informações sobre os índices (Index)

Podemos também usar o objeto Index para obter informações sobre os índices da tabela. Para isto vamos declarar mais uma variável , desta vez a variável objeto Idx do tipo ADOX.Index. As informações sobre as tabelas ( nome ) são exibidas no controle ListBox(ListaTabelas) e o nome dos índices em outro Listbox(ListaColunas) O código fica como abaixo:

Private Sub Command1_Click()

Dim con As New ADODB.Connection

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim Idx As New ADOX.Index

'estabelecendo a conexao
Set con = New ADODB.Connection
conexao = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\teste\biblio.mdb"
con.Open conexao
Set cat.ActiveConnection = con

'exibindo o nome e os tipos das tabelas
For Each tbl In cat.Tables
    listatabelas.AddItem tbl.Name
    For Each Idx In tbl.Indexes
        listacolunas.AddItem Idx.Name
    Next
Next

End Sub

O resultado é exibido na tela abaixo:

Para obter informações sobre as chaves : primária , estrangeira ou única , usamos a coleção Keys . Primeiro devemos declarar uma variável objeto do tipo ADOX.Key e depois percorremos a coleção Keys do objeto Table para exibir suas propriedades ( nome -Name , Tipo-Type , etc..). Abaixo o código ajustado para exibir as chaves das tabelas do banco de dados biblio.mdb.

Private Sub Command1_Click()

Dim con As New ADODB.Connection

Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim key As New ADOX.key

'estabelecendo a conexao
Set con = New ADODB.Connection
conexao = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\teste\biblio.mdb"
con.Open conexao
Set cat.ActiveConnection = con

'exibindo o nome e os tipos das tabelas
For Each tbl In cat.Tables
    listatabelas.AddItem tbl.Name
    For Each key In tbl.Keys
       listacolunas.AddItem key.Name
    Next
Next

End Sub

Você já percebeu como é fácil acessar informações de uma tabela usando ADOX, mas, você pode ir além , criando e modificando tabelas...

Criando Tabelas com ADOX

Para criar uma tabela usando ADOX siga as seguintes etapas:

Abra um objeto Catalog para o banco de dados no qual quer criar a tabela
Crie um novo objeto Table
Use o método Append da coleção Columns para incluir as definições dos campos na coleção Columns do objeto Table
Insira o novo objeto Table a coleção Tables do objeto Catalog. Pronto !

Observe que o método Append é usado para criar e incluir o objeto Column a coleção Columns. Vejamos um exemplo pratica onde iremos criar uma tabela no banco de dados biblio.mdb chamada Nova_Tabela , primeiro sem definir campo algum. Veja baixo o código

Private Sub Command1_Click()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
conexao = "Provider=Microsoft.Jet.OLEDB.4.0;_

Data Source=c:\teste\Biblio.mdb"

cat.ActiveConnection = conexao

Set tbl = New ADOX.Table
tbl.Name = "Nova_Tabela"
cat.Tables.Append tbl

End Sub

O código para criar a tabela Nova_Tabela

A tabela recém criada sendo exibida

Bem , embora seja muito simples criar uma tabela , ela somente terá um significado a medida que criarmos colunas (campos) para poder armazenar os dados. Vamos incluir os campos : Nome, Endereço , Telefone e Observacao na tabela. O código é dado abaixo:

Private Sub Command1_Click()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
conexao = "Provider=Microsoft.Jet.OLEDB.4.0;Data _ Source=c:\teste\Biblio.mdb"

cat.ActiveConnection = conexao

Set tbl = New ADOX.Table

'cria o novo objeto table
With tbl
   .Name = "Nova_tabela"
   'cria campos e os anexa a coleção columns
   With .Columns
       .Append "Nome", adVarWChar
       .Append "Endereco", adVarWChar
       .Append "Telefone", adVarWChar
       .Append "Observacao", adLongVarWChar
   End With
End With

'inclui a nova tabela a coleção tables
cat.Tables.Append tbl
set cat=nothing
End Sub

O código para criar a tabela com os campos

A nova tabela com os campos criados

Para definir os tipos dos campos da tabela usamos a nomenclatura da ADOX que é diferente da DAO. Abaixo temos uma tabela comparando as duas nomenclaturas:

Tipo de Dados usados no Access	Tipo de dados usado na DAO	Tipos de dados da ADOX
Yes/No	dbBoolean	adBoolean
Number (FieldSize = Byte)	dbByte	adUnsignedTinyInt
Currency	dbCurrency	adCurrency
Data/Time	dbDate	adDate
Number (FieldSize = Decimal)	dbDecimal	adDecimal
Number (FieldSize = Double)	dbDouble	adDouble
Number or AutoNumber (FieldSize = Replication ID)	dbGUID	adGUID
Number (FieldSize = Integer)	dbInteger	adSmallInt
Number or AutoNumber (FieldSize = LongInteger)	dbLong	adInteger
OLE Object	dbLongBinary	adLongVarBinary
Memo	dbMemo	adLongVarWChar
Number (FieldSize = Single)	dbSingle	adSingle
Text	dbText	adVarWChar
Hyperlink	dbMemo	adLongVarWChar, mais ADOX provider-specific Column propriedade definida para o Jet OLEDB:Hyperlink

Podemos , ao criar os campos de uma tabela , configurar os atributos específicos e as propriedades de cada campo mais detalhadamente: Ex: Campo Autonumeração , etc...

Vamos a seguir criar uma nova tabela chamada Nova_tabela em um banco de dados Access: Novo.mdb , com os seguinte campos:

Nome da coluna ( campo )	Propriedade
Codigo	Autonumeração
Nome	Texto com 50 caracteres
Endereco	Texto com 60 caracateres
Idade	Inteiro

Veja o código :

Private Sub Command1_Click()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
conexao = "Provider=Microsoft.Jet.OLEDB.4.0;Data _ Source=c:\teste\Novo.mdb"

cat.ActiveConnection = conexao

Set tbl = New ADOX.Table

'cria o novo objeto table
With tbl
   .Name = "Nova_tabela"
    Set .ParentCatalog = cat
    'cria campos e os anexa a coleção columns
    With .Columns
        .Append "Codigo", adInteger
        'tornando o campo autonumeração
        .Item("Codigo").Properties("Autoincrement") = True
        .Append "Nome", adVarWChar, 50
        .Append "Endereco", adVarWChar, 60
        .Append "Idade", adInteger
    End With
End With

'inclui a nova tabela a coleção tables
cat.Tables.Append tbl

set cat=nothing

End Sub

O código para atribuir propriedades

A tabela criada com os campos

Para tornar o campo Codigo do tipo Autonumeração definimos a propriedade Autoincrement da coluna Codigo como True.

Criando Índices

Para criar um índice usando ADOX , primeiro você deve definir qual coluna será declarada como um índice para a tabela em questão . No exemplo anterior criamos a tabela Nova_Tabela mas não definimos ainda qual deverá ser o índice. Geralmente o índice mais importante de uma tabela é a chave primária. Vamos criar um índice chave primária para o campo código. Abaixo a estrutura da tabela:

Nota: chave primária - definição

Um ou mais campos (colunas) cujo valor ou valores identificam de modo exclusivo cada registro de uma tabela. Uma chave primária não permite valores Nulos e deve sempre ter um índice exclusivo. Uma chave primária é usada para relacionar uma tabela a chaves estrangeiras em outras tabelas.

Nome da coluna ( campo )	Propriedade
Codigo	Autonumeração
Nome	Texto com 50 caracteres
Endereco	Texto com 60 caracateres
Idade	Inteiro

O código para criar a chave primária para o campo Codigo:

Private Sub Command1_Click()
Dim cat As ADOX.Catalog
Dim Idx As New ADOX.Index

On Error GoTo trataerro

'estabelecendo a conexao
Set cat = New ADOX.Catalog
conexao = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\teste\Novo.mdb"
cat.ActiveConnection = conexao

'definindo as propriedades do índice
Set Idx = New ADOX.Index
With Idx
   .Name = "PrimaryKey"
   .IndexNulls = adIndexNullsAllow
   .PrimaryKey = True
   .Unique = True
   .Columns.Append "Codigo"
End With

'anexando o índice a coleção das tabelas
Set tbl = cat.Tables("Nova_Tabela")
tbl.Indexes.Append Idx

MsgBox "Indice criado com sucesso"
Exit Sub

trataerro:
MsgBox Err.Description

End Sub

O código para criar uma chave primária na tabela Nova_Tabela do banco de dados Novo.mdb

A novidade neste código é a inicialização do objeto Idx e a definição das propriedades para este objeto : o nome do índice (PrimaryKey), não pode conter valores nulos ( adIndexNullsAllow ) , Unique=True , e finalmente definimos a coluna ( campo) para o qual o índice foi definido ( Codigo ). Para encerrar precisamos conectar o objeto Idx a tabela Nova_tabela através do Indexs.Append. O resultado é exibido abaixo:

Criando uma Chave Estrangeira

Uma chave estrangeira define o relacionamento entre duas tabelas. A chave estrangeira é a relação que você define entre um campo de uma tabela A ao campo chave primária da Tabela B (você pode ter mais de um campo definido na relação). Assim , na figura abaixo temos um exemplo de um relacionamento entre a chave estrangeira -CodigoCliente- da tabela Nova_Tabela_B e a chave primária - PrimaryKey- da tabela Nova-Tabela.

Codigo é chave Primária já definida na tabela Nova_Tabela

CodigoCliente é a chave estrangeira definida na tabela Nova_Tabela_B que esta relacionada a chave primária da tabela Nova_Tabela

Na janela Editar relacionamentos mostramos o tipo de relacionamento que será criado via código.

Obs: Uma chave estrangeira não pode ser nula e deve ter o mesmo tipo de dado da chave primária.

Agora o código de como criar este relacionamento usando ADOX ( não vou mostrar como criar a tabela Nova_Tabela_B):

Private Sub Command1_Click()
Dim cat As New ADOX.Catalog
Dim key As New ADOX.key

'On Error GoTo trataerro

'estabelecendo a conexao
Set cat = New ADOX.Catalog
conexao = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\teste\Novo.mdb"
cat.ActiveConnection = conexao

Set key = New ADOX.key
With key
   .Name = "FK_Nova_Tabela"
   .Type = adKeyForeign
   .RelatedTable = "Nova_tabela"
   .Columns.Append "CodigoCliente", adInteger
   .Columns("CodigoCliente").RelatedColumn = "Codigo"
   .UpdateRule = adRICascade
End With

cat.Tables("Nova_Tabela_B").Keys.Append key
MsgBox "Chave estrangeira criada com sucesso"
Exit Sub

trataerro:
MsgBox Err.Description

End Sub

Como funciona passo a passo:

Definimos a nova chave - .Name = "FK_Nova_Tabela"
A seguir definimos o tipo da chave = .Type = adKeyForeign
Precisamos definir a tabela Mãe usando a propriedade RelatedTable = .RelatedTable = "Nova_tabela"
Anexamos as colunas que formam a chave estrangeira na tabela filha = .Columns.Append "CodigoCliente", adInteger
Relacionamos a coluna filha com a coluna Mãe = .Columns("CodigoCliente").RelatedColumn = "Codigo"
O passo final é determinar como as atualizações na tabela mãe(Nova_Tabela) serão propagadas á tabela filha (Nova_Tabela_B) .=.UpdateRule = adRICascade . Escolhemos o tipo adRICascade onde todas as mudanças na tabela mãe são propagadas as tabelas filhas. Os outros tipos suportados são:

adRINone	Quando a coluna mãe muda nada ocorre. As colunas filhas estão independentes
adRISetDefault	Muda para o valor da coluna mãe ao definir a coluna filha ao seu valor padrão.
adRISetNull	O valor da coluna filha muda para Null quando a coluna mãe muda.

Findamos aqui a primeira parte deste artigo. Aguardem a segunda parte onde falarei sobre o gerenciamento da segurança com ADOX - trabalhando com grupos, usuários e permissões de acesso... Até la´...