473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 1442
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
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
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
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
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
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
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.