|
 |
activex_data_objects thread: Creating Tables with the ADOX
Message #1 by "Frank LOmbard" <FrankWeb1@H...> on Tue, 9 Oct 2001 07:02:25
|
|
Running into problems trying to create a new Table in a Catalog. When all
parameteres have been intialized and I try to append the Catalog with the
new table I get the error Invalid Type. The Type property on a Table
object is a read only property, So I'm stuck in the mud. I've looked at
the examples at the Microsoft site and I can't see anything that I'm doing
differently. I'm including my code below if anyone can see what's wrong or
have any suggestions I would appreciate it.
Dim Conn As New ADOX.Catalog, DatabaseName$, Table As ADOX.Table
Dim i%, j%, findp%, Fkname$, Key As ADOX.Key, Index As ADOX.Index, Column
As ADOX.Column
MainForm.CommonDialog1.DialogTitle = "Name New DataBase"
MainForm.CommonDialog1.ShowOpen
Select Case DataBaseType
Case "Access"
DatabaseName = MainForm.CommonDialog1.FileName
Conn.ActiveConnection = Conn.Create("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" & DatabaseName)
Case "SQL Server"
Conn.ActiveConnection = Conn.Create("Provider=SQLOLEDB.1;User ID=" &
DBLogin.txtId.Text & ";PWD=" & DBLogin.txtPassword.Text _
& ";Initial Catalog=" & DatabaseName & ";")
End Select
For i = 1 To UBound(ERD)
Set Table = New ADOX.Table
Set Table.ParentCatalog = Conn
Table.Name = Title(i).Caption
For j = 0 To UBound(ERD(i).Attributes)
With ERD(i).Attributes(j)
Set Column = New ADOX.Column
'intialize column object properties
Column.Name = .Name
Column.Type = .Data_Type
Column.DefinedSize = .length
Set Column.ParentCatalog = Conn
Table.Columns.Append Column
If .Primary_Key = adKeyPrimary Then
Set Index = New ADOX.Index
Index.Name = "Prime" & .Name
Index.Columns.Append .Name, .Data_Type
Index.PrimaryKey = True
Index.Unique = True
Index.IndexNulls = adIndexNullsDisallow
Index.Clustered = False
Table.Indexes.Append Index
Set Index = Nothing
End If
Set Column = Nothing
End With
Next
'create foreign key if neccessary
For j = 0 To UBound(ERD(i).Relation)
With ERD(i).Relation(j)
If .Id <> "" Then
Table.Columns.Append .Id, adLongVarBinary
Set Key = New ADOX.Key
Key.Name = .Id
Key.Type = adKeyForeign
Key.RelatedTable = Title(ERD(i).Relation(j).Table.Index).Caption
Key.Type = adKeyForeign
Key.UpDateRule = adRICascade
Key.DeleteRule = adRICascade
Key.Columns.Append .Id
Table.Keys.Append Key
Set Key = Nothing
End If
End With
Next
Conn.Tables.Append Table
Set Table = Nothing
Next
Conn.ActiveConnection.Close
Set Conn = Nothing
Message #2 by "Tomm Matthis" <matthis@b...> on Tue, 9 Oct 2001 09:09:36 -0400
|
|
Where do you open the connection?
-- Tomm
> -----Original Message-----
> From: Frank LOmbard [mailto:FrankWeb1@H...]
> Sent: Tuesday, October 09, 2001 7:02 AM
> To: ActiveX_Data_Objects
> Subject: [activex_data_objects] Creating Tables with the ADOX
>
>
> Running into problems trying to create a new Table in a Catalog. When all
> parameteres have been intialized and I try to append the Catalog with the
> new table I get the error Invalid Type. The Type property on a Table
> object is a read only property, So I'm stuck in the mud. I've looked at
> the examples at the Microsoft site and I can't see anything that I'm doing
> differently. I'm including my code below if anyone can see what's wrong or
> have any suggestions I would appreciate it.
>
>
> Dim Conn As New ADOX.Catalog, DatabaseName$, Table As ADOX.Table
> Dim i%, j%, findp%, Fkname$, Key As ADOX.Key, Index As ADOX.Index, Column
> As ADOX.Column
>
> MainForm.CommonDialog1.DialogTitle = "Name New DataBase"
> MainForm.CommonDialog1.ShowOpen
>
> Select Case DataBaseType
>
> Case "Access"
>
> DatabaseName = MainForm.CommonDialog1.FileName
> Conn.ActiveConnection = Conn.Create("Provider=Microsoft.Jet.OLEDB.4.0;
> Data Source=" & DatabaseName)
>
> Case "SQL Server"
>
> Conn.ActiveConnection = Conn.Create("Provider=SQLOLEDB.1;User ID=" &
> DBLogin.txtId.Text & ";PWD=" & DBLogin.txtPassword.Text _
> & ";Initial Catalog=" & DatabaseName & ";")
>
> End Select
>
> For i = 1 To UBound(ERD)
>
> Set Table = New ADOX.Table
>
> Set Table.ParentCatalog = Conn
>
> Table.Name = Title(i).Caption
>
> For j = 0 To UBound(ERD(i).Attributes)
>
> With ERD(i).Attributes(j)
>
> Set Column = New ADOX.Column
>
> 'intialize column object properties
>
> Column.Name = .Name
> Column.Type = .Data_Type
> Column.DefinedSize = .length
> Set Column.ParentCatalog = Conn
> Table.Columns.Append Column
>
> If .Primary_Key = adKeyPrimary Then
> Set Index = New ADOX.Index
> Index.Name = "Prime" & .Name
> Index.Columns.Append .Name, .Data_Type
> Index.PrimaryKey = True
> Index.Unique = True
> Index.IndexNulls = adIndexNullsDisallow
> Index.Clustered = False
> Table.Indexes.Append Index
> Set Index = Nothing
> End If
> Set Column = Nothing
> End With
> Next
>
> 'create foreign key if neccessary
>
> For j = 0 To UBound(ERD(i).Relation)
> With ERD(i).Relation(j)
> If .Id <> "" Then
> Table.Columns.Append .Id, adLongVarBinary
> Set Key = New ADOX.Key
> Key.Name = .Id
> Key.Type = adKeyForeign
> Key.RelatedTable = Title(ERD(i).Relation(j).Table.Index).Caption
> Key.Type = adKeyForeign
> Key.UpDateRule = adRICascade
> Key.DeleteRule = adRICascade
> Key.Columns.Append .Id
> Table.Keys.Append Key
> Set Key = Nothing
> End If
> End With
> Next
>
> Conn.Tables.Append Table
> Set Table = Nothing
> Next
> Conn.ActiveConnection.Close
> Set Conn = Nothing
>
> $subst('Email.Unsub')
>
Message #3 by "Frank LOmbard" <FrankWeb1@H...> on Tue, 9 Oct 2001 19:19:16
|
|
> Where do you open the connection?
>
> -- Tomm
The Create Method is an implicit open on a newly created database
Message #4 by "Frank LOmbard" <FrankWeb1@H...> on Tue, 9 Oct 2001 23:12:16
|
|
Ok I found out why I'm getting the "Type Invalid" error. Not all ADOX data
types are supported by all providers. While sorting out which provider
supports which data type I'm now incountering a new error: "Invalid Field
definition 'Id' in definition of index or relationship." I haven't changed
any of the code from my first posting. Appreciate any suggestions.
Message #5 by "Frank LOmbard" <FrankWeb1@H...> on Wed, 10 Oct 2001 04:55:07
|
|
Can lightning strike twice? Both errors due to the same issue, depending
on the provider determines what key and index types are supported.
|
|
|
View Cart
