How to create multiple Tables for Access?

[mrdutchie]

I tried to create multiple tables within VB.NEt for access, but it
doesn't create all the tables.

This is what I have:


Public Sub CREATE_MAIN_DATABASE()
Dim ADOXcatalog As New ADOX.Catalog
Dim ADOXtable As New ADOX.Table
Dim ADOXindex As New ADOX.Index

On Error GoTo errhandler
ADOXcatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "c:\donation.mdb")

On Error Resume Next


errhandler:
If Err.Number = -2147217897 Then
MsgBox("Database already exists")
ElseIf Err.Number <> 0 Then
MsgBox("Err " & Err.Number & " " & Err.Description)

End If
ADOXtable = New ADOX.ADOXTable

ADOXtable.Name = "Addressbook"
ADOXtable.Columns.Append("Prefix", ADOX.DataTypeEnum.adVarWChar, 50)
ADOXtable.Columns.Append("Firstname", ADOX.DataTypeEnum.adVarWChar, 50)
ADOXtable.Columns.Append("Middlename", ADOX.DataTypeEnum.adVarWChar, 50)
ADOXtable.Columns.Append("Lastname", ADOX.DataTypeEnum.adVarWChar, 50)
ADOXtable.Columns.Append("Address1", ADOX.DataTypeEnum.adVarWChar, 50)
ADOXtable.Columns.Append("Address2", ADOX.DataTypeEnum.adVarWChar, 50)
ADOXtable.Columns.Append("City", ADOX.DataTypeEnum.adVarWChar, 50)
ADOXtable.Columns.Append("State", ADOX.DataTypeEnum.adVarWChar, 50)
ADOXtable.Columns.Append("Zip", ADOX.DataTypeEnum.adVarWChar, 50)
ADOXcatalog.Tables.Append(ADOXtable)
ADOXtable = Nothing


ADOXtable.Name = "email"
ADOXtable.Columns.Append("Name", ADOX.DataTypeEnum.adVarWChar, 50)
ADOXtable.Columns.Append("Email", ADOX.DataTypeEnum.adVarWChar, 50)
ADOXcatalog.Tables.Append(ADOXtable)
ADOXtable = Nothing
ADOXindex = Nothing
ADOXcatalog = Nothing

ADOXtable = Nothing
ADOXindex = Nothing
ADOXcatalog = Nothing

End Sub

What am I missing ?

[Pirate]

Declare one instance of ADOX.Table for each table you are trying to create . So your code should look something like this :

Public Sub CREATE_MAIN_DATABASE()
    Dim ADOXcatalog As New ADOX.Catalog
    Dim ADOXtable As New ADOX.Table
    Dim SecondTable As New ADOX.Table
    Dim ADOXindex As New ADOX.Index
 
    On Error GoTo errhandler
    ADOXcatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "c:\donation.mdb")
 
    On Error Resume Next
 
 
errhandler:
    If Err.Number = -2147217897 Then
        MsgBox("Database already exists")
    ElseIf Err.Number <> 0 Then
        MsgBox("Err " & Err.Number & " " & Err.Description)
 
    End If
 
    'First Table
    ADOXtable.Name = "Addressbook"
    ADOXtable.Columns.Append("Prefix", ADOX.DataTypeEnum.adVarWChar, 50)
    ADOXtable.Columns.Append("Firstname", ADOX.DataTypeEnum.adVarWChar, 50)
    ADOXtable.Columns.Append("Middlename", ADOX.DataTypeEnum.adVarWChar, 50)
    ADOXtable.Columns.Append("Lastname", ADOX.DataTypeEnum.adVarWChar, 50)
    ADOXtable.Columns.Append("Address1", ADOX.DataTypeEnum.adVarWChar, 50)
    ADOXtable.Columns.Append("Address2", ADOX.DataTypeEnum.adVarWChar, 50)
    ADOXtable.Columns.Append("City", ADOX.DataTypeEnum.adVarWChar, 50)
    ADOXtable.Columns.Append("State", ADOX.DataTypeEnum.adVarWChar, 50)
    ADOXtable.Columns.Append("Zip", ADOX.DataTypeEnum.adVarWChar, 50)
    ADOXcatalog.Tables.Append(ADOXtable)
 
 
    'Second Table
    SecondTable.Name = "email"
    SecondTable.Columns.Append("Name", ADOX.DataTypeEnum.adVarWChar, 50)
    SecondTable.Columns.Append("Email", ADOX.DataTypeEnum.adVarWChar, 50)
    ADOXcatalog.Tables.Append(SecondTable)
 
 
    ADOXtable = Nothing
    SecondTable = Nothing
    ADOXindex = Nothing
    ADOXcatalog = Nothing
 
End Sub

[mrdutchie]

Thanks I will try that..
I was thinking about that but thought, nah that's too easy...
Knowing MS it's always the hard way

[mrdutchie]

Ok, it's working now..
Thanks a lot