Establishing relationships

[gaffar]

Sir,

Below code(vb.net source code) is used to create a ms access database,
tables and assigning primary keys. now i want the code to establish
relationship between the tables.
waiting for ur reply


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 table
objTable1.Name = "new_custdetails"
objtable2.Name = "contact_note"
objtable3.Name = "contact_salesopportunity"
objtable4.Name = "contact_activities"


'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)

'Append the newly created table to the Tables Collection

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,

A very simple and quick made sample using ADONET and VBNet

\\\\
Dim Sql As String = "SELECT * from A, B Where " & _
"A.n = B.n"
Dim Conn As New OleDbConnection(connString)
Dim da As New OleDbDataAdapter(Sql, Conn)
dim ds as new DataSet
da.Fill(ds, "A")
da.Fill(ds, "B")
Conn.Close()
Dim drlA As New DataRelation _
("AA", ds.Tables("A").Columns("A.n"), _
ds.Tables("B").Columns("B.n"))
ds.Relations.Add(drlA)
DataGrid1.DataSource = ds
DataGrid1.Expand(-1)
////

 

[Nick Malik [Microsoft]]

Hi Cor,

I'm not certain, but I think the OP may have wanted to know how to create
referential integrity links in the database itself.

Your response creates a Data Relation in a dataset, but doesn't affect the
database at all.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.

 

[Nick Malik [Microsoft]]

Use the Keys collection to add a key to a table object. Make the Key a
foreign key and use the .RelatedTable property to establish the
relationship.

You can usually add the keys after you define the table.

See
http://msdn.microsoft.com/library/en-us/ado270/htm/adobjkey.asp

Good example at:
http://msdn.microsoft.com/library/en-us/ado270/htm/admsckeycreationexample.asp

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--

 

[Cor Ligthert]

Nick,

Maybe, he has dumbed a lot of messages, crossposted and individual
multiposted to at least the newsgroup languages.vb.

However only with this code or with text as "help me" while he did not give
any more explanation and everytime creates a new message.

Cor

 

[Nick Malik [Microsoft]]

Hi Cor,

One thing I've discovered... if someone keeps asking the same question over
and over, it's because he or she either doesn't understand the answers, or
the answers don't really address his or her question.

I'm assuming the latter.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.

 

[Cor Ligthert]

Nick,

I know, however I have (as far as I remember me) asked politly if he could
reply in the original thread all the time (except the last one).

And watched if there would come an reaction.

It seems for me not right to start every time new again.

Cor