473,490 Members | 2,473 Online
Bytes | Software Development & Data Engineering Community
Create Post

Home Posts Topics Members FAQ

Establishing relationships

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
Jul 21 '05 #1
6 1447
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)
////
Jul 21 '05 #2
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.
--
"Cor Ligthert" <no************@planet.nl> wrote in message
news:OF**************@TK2MSFTNGP12.phx.gbl...
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)
////

Jul 21 '05 #3
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...m/adobjkey.asp

Good example at:
http://msdn.microsoft.com/library/en...ionexample.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.
--
"gaffar" <sa********@yahoo.co.in> wrote in message
news:uj**************@TK2MSFTNGP12.phx.gbl...
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

Jul 21 '05 #4
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
Jul 21 '05 #5
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" <no************@planet.nl> wrote in message
news:uL**************@tk2msftngp13.phx.gbl...
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

Jul 21 '05 #6
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
Jul 21 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
4113
by: Max | last post by:
Hi. I really hope someone can help me. Going slowly insane with this problem. I have a two Access 2000 databases. One is the backend containing tables and some admin queries. The other is the...
7
2111
by: davegb | last post by:
I'm totally new to relational database design. My boss has asked me to create a database of information on the employees in our group. Seemed to me like a simple application to learn the ropes. A...
3
1692
by: gaffar | last post by:
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...
45
3347
by: salad | last post by:
I'm curious about your opinion on setting relationships. When I designed my first app in Access I'd go to Tools/Relationships and set the relationships. Over time I'd go into the window and see...
0
1981
by: aboutjav.com | last post by:
Hi, I need some help. I am getting this error after I complete the asp.net register control and click on the continue button. It crashed when it tries to get it calls this Profile property ...
0
6967
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
7142
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
7181
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
1
6847
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
1
4875
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...
0
3071
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1383
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
1
618
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
272
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.