473,320 Members | 1,958 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.

Creating relationships using VBA

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

Function CreateRelationship()

Dim catDB As ADOX.Catalog
Dim tbl As ADOX.Table
Dim key As ADOX.key

Dim strForeignTbl As String
Dim strRelName As String
Dim strFTKey As String
Dim strRelatedTbl As String
Dim strRTKey As String

strForeignTbl = "theChildTable"
strRelName = "myRelationship"
strFTKey = "foreignTableKey"
strRelatedTbl = "parentTable"
strRTKey = "parentTableKey"

Set catDB = New ADOX.Catalog
catDB.ActiveConnection = CurrentProject.Connection

Set key = New ADOX.key
With key
.Name = strRelName
.RelatedTable = strRelatedTbl
.Type = adKeyForeign
.Columns.Append strFTKey
.Columns(strFTKey).RelatedColumn = strRTKey
End With

Set tbl = New ADOX.Table
Set tbl = catDB.Tables(strForeignTbl)
tbl.Keys.Append key

Set key = Nothing
Set tbl = Nothing
Set catDB = Nothing

End Function

Nov 13 '05 #1
2 6086
CurrentProject.Connection.Execute _
"ALTER TABLE Classes " _
& "ADD CONSTRAINT ClassSchool " _
& "FOREIGN KEY (SchoolID) " _
& "References Schools (SchoolID)"

Gandalf wrote:
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).

Nov 13 '05 #2
Gandalf,
I'm with Lyle. I'd write either a CREATE TABLE statement with my
constraints declared in it or an ALTER TABLE statement to change the table
design.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS

"Gandalf" <Ga***********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
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 ----->

Function CreateRelationship()

Dim catDB As ADOX.Catalog
Dim tbl As ADOX.Table
Dim key As ADOX.key

Dim strForeignTbl As String
Dim strRelName As String
Dim strFTKey As String
Dim strRelatedTbl As String
Dim strRTKey As String

strForeignTbl = "theChildTable"
strRelName = "myRelationship"
strFTKey = "foreignTableKey"
strRelatedTbl = "parentTable"
strRTKey = "parentTableKey"

Set catDB = New ADOX.Catalog
catDB.ActiveConnection = CurrentProject.Connection

Set key = New ADOX.key
With key
.Name = strRelName
.RelatedTable = strRelatedTbl
.Type = adKeyForeign
.Columns.Append strFTKey
.Columns(strFTKey).RelatedColumn = strRTKey
End With

Set tbl = New ADOX.Table
Set tbl = catDB.Tables(strForeignTbl)
tbl.Keys.Append key

Set key = Nothing
Set tbl = Nothing
Set catDB = Nothing

End Function

Nov 13 '05 #3

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

Similar topics

2
by: Andrea | last post by:
I'm having some difficulty creating a report in Access and I need some suggestions. My company issues "Return Authorizations" when customers need to return products. A customer calls in and we...
8
by: Brian S. Smith | last post by:
Hi gang, Please help. I've been through the Access help, searched the Web, and I can't seem to get a straight answer. As the Subject line suggests, I want to run a fairly simple VB/Access Sub...
3
by: GGerard | last post by:
Hello Does anyone know if it is possible with Access 2000 to create relationships between tables using code? Thanks G.Gerard
4
by: Ronnie | last post by:
Ok let me just say first that I am a newbie in Access and I don't know much of SQL or VB programming. But I am trying to create this contact database using Access 97. I have created 2 tables,...
5
by: Mike Turco | last post by:
What is the difference between creating relationships in the front-end vs. the back-end database? I was trying to create a relationship in a database front-end and noticed that I could not check...
4
by: jmarr02s | last post by:
I am using Access 2003 and am having difficulty creating a subform that will save records associated with a particular parent record.... The error message I receive pertains to indexes,...
1
by: Kosmos | last post by:
Hi...I'm new but decided to take on learning access programming for my job and I'm having trouble building relations. Any help would be very appreciated! So here's the jist of the program...I...
5
by: Kosmos | last post by:
Hey :) hopefully someone can help me with this...I decided to take on the task of programming an access database for my legal co-op/internship...I'm studying law and music production on the...
10
by: Richard | last post by:
Hi folks, thanks for taking the time to read this (and hopefully point our where I'm going wrong). The scenario: I have a local Access2007 database which links in several read only mySql...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
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...
1
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: 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...
1
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
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.