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(code 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.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
3  1689 
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
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
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(code 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.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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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:...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
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...
|
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,...
|
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...
| |
