By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,977 Members | 1,352 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,977 IT Pros & Developers. It's quick & easy.

how to establish a relationships between tables from the vb.net application at run time

P: n/a
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. please consider this

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

Jul 21 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Gaffar,

this message I have placed an hour ago in the languages.vb newsgroup where
you have placed the same message.
-------------------------------------------------------------------------------------------------
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


Jul 21 '05 #2

P: n/a
sorry sir,
i need the code by using ADOX
Jul 21 '05 #3

P: n/a
I asked why?

Than I can learn from it as well

I never saw a real reason and only problems with deployment and more in
these newsgroups.

However maybe you have one.

Cor
Jul 21 '05 #4

P: n/a
Gaffar,

I did not even recognize it. You answer qeustions asked in the language.vb
newsgroup in this newsgroup.

This is terrible.

Cor
Jul 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.