establishing realtionships by using ADOX

[gaffar]

Sir, Using ADOX

I am developing an application in vb.net and the backend database is
ms-access. i have created ms-access databse and tables and assigned primary
keys to the tables through the vb.net application(code is below) by using
ADOX. Now my problem is i want delete one record from master table, that
deleted record automatically deleted from child tables. so, i want the code
how to establish relation ships between the tables and cascade property
through my application. i am posting this question from so many days. but i
am not getting the exact answer. i am getting the answer in different ways
(1) create a dataset in which establish the relation ships. (2) directly
establishing the relation ships with in the ms-access database. these are
not my expected answers. i want to establish the relationships between the
tables through my application at the runtime.

It is very urgent.

Thanking u sir.

'creating tables in the database

'
' This code adds a single-field Primary key
'
Dim Cn As ADODB.Connection
Dim Cat2 As ADOX.Catalog
Dim objTable1, objtable2, objtable3, objtable4 As ADOX.Table
Dim objkey1 As ADOX.Key

Cn = New ADODB.Connection
Cat2 = New ADOX.Catalog
objTable1 = New ADOX.Table
objtable2 = New ADOX.Table
objtable3 = New ADOX.Table
objtable4 = New ADOX.Table

objkey1 = New ADOX.Key

'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & Application.StartupPath &
"\databases" & "\" & fname1 & ";" & "Jet OLEDB:Engine Type=5")

'"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=d:\sample\gaffar.mdb"

'Open the Catalog
Cat2.ActiveConnection = Cn

'Create the tables

objTable1.Name = "new_custdetails" // MASTER TABLE
objtable2.Name = "contact_note" // CHILD TABLE
objtable3.Name = "contact_salesopportunity" // CHILD TABLE
objtable4.Name = "contact_activities" //CHILD TABLE
'Create and Append a new field to the "new_custdetails" Columns
Collection
objTable1.Columns.Append("company", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("contact", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("salutation", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("title", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("dept", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("phone", DataTypeEnum.adInteger)
objTable1.Columns.Append("phext1", DataTypeEnum.adInteger)
objTable1.Columns.Append("rec_creator", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("rec_mgr", DataTypeEnum.adVarWChar)

'Create and Append a new field to the "new_custdetails" Columns
Collection
objTable1.Columns.Append("company", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("contact", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("salutation", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("title", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("dept", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("phone", DataTypeEnum.adInteger)
objTable1.Columns.Append("phext1", DataTypeEnum.adInteger)
objTable1.Columns.Append("rec_creator", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("rec_mgr", DataTypeEnum.adVarWChar)

'Create and Append a new field to the "contact_note" Columns
Collection

objTable2.Columns.Append("company", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("contact", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("dept", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("date", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("Type", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("rec_mgr", DataTypeEnum.adVarWChar)

'Create and Append a new field to the "
contact_activities " Columns Collection

objTable2.Columns.Append("company", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("contact", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("dept", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("Note", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("Date", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("Purpose", DataTypeEnum.adVarWChar)

'Assigning primary key

objkey1.Name = "PrimaryKey"
objkey1.Type = KeyTypeEnum.adKeyPrimary
objkey1.Columns.Append("company")
objkey1.Columns.Append("contact")
objkey1.Columns.Append("dept")

Cat2.Tables.Append(objTable1)
Cat2.Tables.Append(objtable2)
Cat2.Tables.Append(objtable3)
Cat2.Tables.Append(objtable4)

' clean up objects
' objKey = Nothing

objTable1 = Nothing
objtable2 = Nothing
objtable3 = Nothing
objtable4 = Nothing
Cat2 = Nothing
Cn.Close()
Cn = Nothing

[Cor Ligthert]

gaffar,

Earlier I showed you a method to make a relation using Datasets.

However first of all, why are you using the old ADOX instead of the net part
ADONET?

(Using a dataset by ADOX is not impossible however a very strange way to go,
and almost curious you get a checked solution in this newsgroup because
probably nobody does that.)

Cor

[Cor Ligthert]

gaffar,

Please do not use Sir in this newsgroups, just the firstname is enough,

It let me think on old Colonial and Emperial times for what I am glad I
never lived in.

Cor

[Kerry Moorman]

gaffer,

I think this article will show you exactly how to do that:

http://www.vbcity.com/forums/topic.asp?tid=41536

Kerry Moorman
"gaffar" wrote:

Sir, Using ADOX

I am developing an application in vb.net and the backend database is
ms-access. i have created ms-access databse and tables and assigned primary
keys to the tables through the vb.net application(code is below) by using
ADOX. Now my problem is i want delete one record from master table, that
deleted record automatically deleted from child tables. so, i want the code
how to establish relation ships between the tables and cascade property
through my application. i am posting this question from so many days. but i
am not getting the exact answer. i am getting the answer in different ways
(1) create a dataset in which establish the relation ships. (2) directly
establishing the relation ships with in the ms-access database. these are
not my expected answers. i want to establish the relationships between the
tables through my application at the runtime.

It is very urgent.

Thanking u sir.

'creating tables in the database

'
' This code adds a single-field Primary key
'
Dim Cn As ADODB.Connection
Dim Cat2 As ADOX.Catalog
Dim objTable1, objtable2, objtable3, objtable4 As ADOX.Table
Dim objkey1 As ADOX.Key

Cn = New ADODB.Connection
Cat2 = New ADOX.Catalog
objTable1 = New ADOX.Table
objtable2 = New ADOX.Table
objtable3 = New ADOX.Table
objtable4 = New ADOX.Table

objkey1 = New ADOX.Key

'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & Application.StartupPath &
"\databases" & "\" & fname1 & ";" & "Jet OLEDB:Engine Type=5")

'"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=d:\sample\gaffar.mdb"

'Open the Catalog
Cat2.ActiveConnection = Cn

'Create the tables

objTable1.Name = "new_custdetails" // MASTER TABLE
objtable2.Name = "contact_note" // CHILD TABLE
objtable3.Name = "contact_salesopportunity" // CHILD TABLE
objtable4.Name = "contact_activities" //CHILD TABLE
'Create and Append a new field to the "new_custdetails" Columns
Collection
objTable1.Columns.Append("company", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("contact", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("salutation", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("title", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("dept", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("phone", DataTypeEnum.adInteger)
objTable1.Columns.Append("phext1", DataTypeEnum.adInteger)
objTable1.Columns.Append("rec_creator", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("rec_mgr", DataTypeEnum.adVarWChar)

'Create and Append a new field to the "new_custdetails" Columns
Collection
objTable1.Columns.Append("company", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("contact", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("salutation", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("title", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("dept", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("phone", DataTypeEnum.adInteger)
objTable1.Columns.Append("phext1", DataTypeEnum.adInteger)
objTable1.Columns.Append("rec_creator", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("rec_mgr", DataTypeEnum.adVarWChar)

'Create and Append a new field to the "contact_note" Columns
Collection

objTable2.Columns.Append("company", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("contact", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("dept", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("date", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("Type", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("rec_mgr", DataTypeEnum.adVarWChar)

'Create and Append a new field to the "
contact_activities " Columns Collection

objTable2.Columns.Append("company", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("contact", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("dept", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("Note", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("Date", DataTypeEnum.adVarWChar)
objTable2.Columns.Append("Purpose", DataTypeEnum.adVarWChar)

'Assigning primary key

objkey1.Name = "PrimaryKey"
objkey1.Type = KeyTypeEnum.adKeyPrimary
objkey1.Columns.Append("company")
objkey1.Columns.Append("contact")
objkey1.Columns.Append("dept")

Cat2.Tables.Append(objTable1)
Cat2.Tables.Append(objtable2)
Cat2.Tables.Append(objtable3)
Cat2.Tables.Append(objtable4)

' clean up objects
' objKey = Nothing

objTable1 = Nothing
objtable2 = Nothing
objtable3 = Nothing
objtable4 = Nothing
Cat2 = Nothing
Cn.Close()
Cn = Nothing