473,714 Members | 2,572 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

establishing realtionships by using ADOX

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 vb.net application(cod e is below) by using
ADOX. 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.Connectio n
Dim Cat2 As ADOX.Catalog
Dim objTable1, objtable2, objtable3, objtable4 As ADOX.Table
Dim objkey1 As ADOX.Key

Cn = New ADODB.Connectio n
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("Provid er=Microsoft.Je t.OLEDB.4.0;" & _
"Data Source= " & Application.Sta rtupPath &
"\databases " & "\" & fname1 & ";" & "Jet OLEDB:Engine Type=5")

'"Provider=Micr osoft.Jet.OLEDB .4.0;Data
Source=d:\sampl e\gaffar.mdb"

'Open the Catalog
Cat2.ActiveConn ection = Cn

'Create the tables

objTable1.Name = "new_custdetail s" // MASTER TABLE
objtable2.Name = "contact_no te" // CHILD TABLE
objtable3.Name = "contact_saleso pportunity" // CHILD TABLE
objtable4.Name = "contact_activi ties" //CHILD TABLE
'Create and Append a new field to the "new_custdetail s" Columns
Collection
objTable1.Colum ns.Append("comp any", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("cont act", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("salu tation", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("titl e", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("dept ", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("phon e", DataTypeEnum.ad Integer)
objTable1.Colum ns.Append("phex t1", DataTypeEnum.ad Integer)
objTable1.Colum ns.Append("rec_ creator", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("rec_ mgr", DataTypeEnum.ad VarWChar)

'Create and Append a new field to the "new_custdetail s" Columns
Collection
objTable1.Colum ns.Append("comp any", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("cont act", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("salu tation", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("titl e", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("dept ", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("phon e", DataTypeEnum.ad Integer)
objTable1.Colum ns.Append("phex t1", DataTypeEnum.ad Integer)
objTable1.Colum ns.Append("rec_ creator", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("rec_ mgr", DataTypeEnum.ad VarWChar)

'Create and Append a new field to the "contact_no te" Columns
Collection

objTable2.Colum ns.Append("comp any", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("cont act", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("dept ", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("date ", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("Type ", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("rec_ mgr", DataTypeEnum.ad VarWChar)

'Create and Append a new field to the "
contact_activit ies " Columns Collection

objTable2.Colum ns.Append("comp any", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("cont act", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("dept ", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("Note ", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("Date ", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("Purp ose", DataTypeEnum.ad VarWChar)

'Assigning primary key

objkey1.Name = "PrimaryKey "
objkey1.Type = KeyTypeEnum.adK eyPrimary
objkey1.Columns .Append("compan y")
objkey1.Columns .Append("contac t")
objkey1.Columns .Append("dept")

Cat2.Tables.App end(objTable1)
Cat2.Tables.App end(objtable2)
Cat2.Tables.App end(objtable3)
Cat2.Tables.App end(objtable4)

' clean up objects
' objKey = Nothing

objTable1 = Nothing
objtable2 = Nothing
objtable3 = Nothing
objtable4 = Nothing
Cat2 = Nothing
Cn.Close()
Cn = Nothing
Nov 21 '05 #1
3 1708
gaffar,

Earlier I showed you a method to make a relation using Datasets.

However first of all, why are you using the old ADOX instead of the net part
ADONET?

(Using a dataset by ADOX is not impossible however a very strange way to go,
and almost curious you get a checked solution in this newsgroup because
probably nobody does that.)

Cor
Nov 21 '05 #2
gaffar,

Please do not use Sir in this newsgroups, just the firstname is enough,

It let me think on old Colonial and Emperial times for what I am glad I
never lived in.

Cor
Nov 21 '05 #3
gaffer,

I think this article will show you exactly how to do that:

http://www.vbcity.com/forums/topic.asp?tid=41536

Kerry Moorman
"gaffar" wrote:
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 vb.net application(cod e is below) by using
ADOX. 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.Connectio n
Dim Cat2 As ADOX.Catalog
Dim objTable1, objtable2, objtable3, objtable4 As ADOX.Table
Dim objkey1 As ADOX.Key

Cn = New ADODB.Connectio n
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("Provid er=Microsoft.Je t.OLEDB.4.0;" & _
"Data Source= " & Application.Sta rtupPath &
"\databases " & "\" & fname1 & ";" & "Jet OLEDB:Engine Type=5")

'"Provider=Micr osoft.Jet.OLEDB .4.0;Data
Source=d:\sampl e\gaffar.mdb"

'Open the Catalog
Cat2.ActiveConn ection = Cn

'Create the tables

objTable1.Name = "new_custdetail s" // MASTER TABLE
objtable2.Name = "contact_no te" // CHILD TABLE
objtable3.Name = "contact_saleso pportunity" // CHILD TABLE
objtable4.Name = "contact_activi ties" //CHILD TABLE
'Create and Append a new field to the "new_custdetail s" Columns
Collection
objTable1.Colum ns.Append("comp any", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("cont act", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("salu tation", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("titl e", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("dept ", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("phon e", DataTypeEnum.ad Integer)
objTable1.Colum ns.Append("phex t1", DataTypeEnum.ad Integer)
objTable1.Colum ns.Append("rec_ creator", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("rec_ mgr", DataTypeEnum.ad VarWChar)

'Create and Append a new field to the "new_custdetail s" Columns
Collection
objTable1.Colum ns.Append("comp any", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("cont act", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("salu tation", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("titl e", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("dept ", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("phon e", DataTypeEnum.ad Integer)
objTable1.Colum ns.Append("phex t1", DataTypeEnum.ad Integer)
objTable1.Colum ns.Append("rec_ creator", DataTypeEnum.ad VarWChar)
objTable1.Colum ns.Append("rec_ mgr", DataTypeEnum.ad VarWChar)

'Create and Append a new field to the "contact_no te" Columns
Collection

objTable2.Colum ns.Append("comp any", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("cont act", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("dept ", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("date ", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("Type ", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("rec_ mgr", DataTypeEnum.ad VarWChar)

'Create and Append a new field to the "
contact_activit ies " Columns Collection

objTable2.Colum ns.Append("comp any", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("cont act", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("dept ", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("Note ", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("Date ", DataTypeEnum.ad VarWChar)
objTable2.Colum ns.Append("Purp ose", DataTypeEnum.ad VarWChar)

'Assigning primary key

objkey1.Name = "PrimaryKey "
objkey1.Type = KeyTypeEnum.adK eyPrimary
objkey1.Columns .Append("compan y")
objkey1.Columns .Append("contac t")
objkey1.Columns .Append("dept")

Cat2.Tables.App end(objTable1)
Cat2.Tables.App end(objtable2)
Cat2.Tables.App end(objtable3)
Cat2.Tables.App end(objtable4)

' clean up objects
' objKey = Nothing

objTable1 = Nothing
objtable2 = Nothing
objtable3 = Nothing
objtable4 = Nothing
Cat2 = Nothing
Cn.Close()
Cn = Nothing

Nov 21 '05 #4

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

Similar topics

2
5565
by: Developer98115 | last post by:
I need help getting schema information from an existing SQL Server database. My thought was that you could use ADOX via InterOp. Has anyone done this successfully and how? I have created a Interop reference to the ADOX COM component from my C# project. I am able to open the SqlConnection successfully but it fails with an exception when I try to set the ..ActiveConnection property of the ADOX database catalog object. Another strange...
6
1464
by: gaffar | last post by:
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.
2
6116
by: Gandalf | last post by:
I'm creating relationships between tables using VBA and ADOX. I can create one-to-one relationships with an inner join, but I can't figure out how to create these relationships with an outer join (specifically a left outer join). I'm including the code that creates the relationships with the inner join. Any help or suggestions would be greatly appreciated. Thanks! <----- CODE FOLLOWS ----->
6
2704
by: Claudia Fong | last post by:
Hello, I'm using the sql statement below to create a new table from an old one. But I found a little problem with that. In my old table DEP2004, I have one field's property allow zero length is YES, but after creating the new table DEP2005, this property change to NO. It means it won't allow zero lenght.. How can I change this property in C#?
2
7845
by: Randy | last post by:
I am trying to relink some Oracle tables in an Access database via VB.NET and ADOX. I receive the following error when executing the cat.ActiveConnection link "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. Here is my code Dim cat As ADOX.Catalog = New ADOX.Catalo Dim tbl As ADOX.Tabl Dim OracleConn As String = "Provider=MSDAORA.1;Data Source=xzy;User ID=xyz;Password=xyz
3
1008
by: gaffar | last post by:
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.
2
1267
by: Tulasi | last post by:
Sir, i have created MSAccess databse by using vb.net by ADOX. now my problem in the ms access database i have one master table in which i have a master field contact_id which is a primary key and i have 3 child tables in which i want use contact_id as foreign key. and to establish relationship between tables . please help me the code. Thanks in advance.
1
3453
by: sunlight_sg | last post by:
Hello, i am using ADOX + VB .NET to create a Access Database programmatically. I plan to set some properties of the column such primary key. The code is as follows: Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim idx As ADOX.Index Dim prp As ADOX.Property
1
11591
by: Hexman | last post by:
I'm creating a new Access table using ADOX. I can add columns and indexes, but I'm baffled on how to change field properties. Can someone give me a hand? Want to change properties such as: Description (Column property?), Decimal Places, Caption, Default value, Required indicator, Format, etc.... TIA,
0
8796
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8704
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9307
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9170
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9071
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6627
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5943
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4462
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3155
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 we have to send another system

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.