How to establish realtionships betweeen the tables through my vb.net application at run time.

G

gaffar

Sir,

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). 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
 
C

Cor Ligthert

gaffar,

How many times have I answered you while you did not give a reaction in many
newsgroups.

When you start every time a new thread where you add some code.
We cannot get after your problem.

More people have tried to become to know what your problem really was.
The problem can be

You want to use an access database with 2 related tables in VBNet
You want to set a realation inside a existing database with AdoDB, however
without to use the tables in your program.

My idea was from the beginning the first however I can be wrong.

You never gave any answer.

Cor
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

establishing realtionships by using ADOX 3
Establishing relationship 3
establishing the relationship between the tables in the ms acess database 1
how to establish a relationships between tables from the vb.net application at run time 4
Establishing relationships 10
establishing relationships with in the ms access database 1
cannot contain a null value because the required property for this field is set to true. 1
cannot contain a null value because the required property for this field is set to true 1

Top