✔ VBA Dashboards Specialist®: VBA Tips - ADO

11 de novembro de 2013

VBA Tips - ADO - Criando Tabela

Consultancy in Excel, Access, VBA & PowerPoint projects

DESENVOLVO APLICAÇÕES

Perguntas, Questões e Esclarecimentos

Exemplo de criação de uma tabela chamada tblARPDetail. Requer referência a ADOx.

Public Sub ADOXCreateDetailTable()

Dim cat As New ADOX.Catalog

Dim tbl As ADOX.table


Set cat.ActiveConnection = CurrentProject.Connection


On Error Resume Next

Set tbl = cat.Tables("tblARPDetail")


If tbl Is Nothing Then

Else

cat.Tables.Delete "tblARPDetail"


Set tbl = Nothing
End If

Set tbl = New ADOX.table

cat.Tables.Delete "tblARPDetail"


tbl.Name = "tblARPDetail"

With tbl.Columns

.Append "dtmPayableDate", adDate


.Append "strPrefix", adVarWChar, 3


.Append "strCheckNumber", adVarWChar, 13


.Append "curAmount", adCurrency


.Append "strLoanAccount", adVarWChar, 12


.Append "strShortName", adWChar, 40


.Append "strCity", adVarWChar, 40


.Append "strState", adVarWChar, 2


.Append "strZip", adVarWChar, 12


.Append "strName", adVarWChar, 40


.Append "strAddress1", adVarWChar, 40


.Append "strAddress2", adVarWChar, 40


.Append "strAddress3", adVarWChar, 40


.Append "strAddress4", adVarWChar, 40


.Append "strSSN", adVarWChar, 12


.Append "strInternalNumber", adVarWChar, 12


.Append "strLoanNumber", adVarWChar, 12


.Append "strDatabase", adVarWChar


.Append "strLoanType", adVarWChar, 4


.Append "strPayeeNumber", adVarWChar


.Append "bolForeign", adBoolean


.Append "bolEligible", adBoolean


.Append "bolLump", adBoolean


.Append "bolDueDiligence", adBoolean


With !dtmPayableDate
Set .ParentCatalog = cat
.Properties("Description") = "Payable Date"
End With

With !strPrefix

Set .ParentCatalog = cat
.Properties("Description") = "Check Prefix"

.Properties("AllowZeroLength") = True
End With


With !strCheckNumber
Set .ParentCatalog = cat
.Properties("Description") = "Check Number"
End With

With !curAmount

Set .ParentCatalog = cat
.Properties("Description") = "Check Face Amount"

End With

With !strLoanAccount
Set .ParentCatalog = cat
.Properties("Description") = "Hogan Account"
End With

With !strShortName
Set .ParentCatalog = cat

.Properties("Description") = "BondMaster Loan Short Name"
End With

With !strCity

Set .ParentCatalog = cat
.Properties("Description") = "Payee City"

End With

With !strState
Set .ParentCatalog = cat
.Properties("Description") = "Payee State"
End With

With !strZip
Set .ParentCatalog = cat

.Properties("Description") = "Payee Zip"
End With

With !strName

Set .ParentCatalog = cat
.Properties("Description") = "Payee Name"

End With

With !strAddress1
Set .ParentCatalog = cat
.Properties("Description") = "Payee Address Line 1"
End With

With !strAddress2
Set .ParentCatalog = cat

.Properties("Description") = "Payee Address Line 2"
End With

With !strAddress3

Set .ParentCatalog = cat
.Properties("Description") = "Payee Address Line 3"

End With

With !strAddress4
Set .ParentCatalog = cat
.Properties("allowzerolength") = True
End With

With !strSSN
Set .ParentCatalog = cat

.Properties("Description") = "Payee SSN"
End With

With !strLoanNumber

Set .ParentCatalog = cat
.Properties("Description") = "BondMaster Internal Loan Number"

End With

With !strDatabase
Set .ParentCatalog = cat
.Properties("Description") = "BondMaster Database"
End With

With !strLoanType
Set .ParentCatalog = cat

.Properties("Description") = "BondMaster Loan Type (Corp = 5 and Muni = 2)"
End With

With !strPayeeNumber

Set .ParentCatalog = cat
.Properties("Description") = "BondMaster Payee Number"

End With

End With

cat.Tables.Append tbl


Set tbl = Nothing
Set cat = Nothing

End Sub