Link between different Tables 1

[Genotix]

Hi Guys,

I've got a problem that has kept me busy for quite some time now.

I'm not very fimiliar with Access and a bit fimiliar with Visual Basic 6

I've got a database that holds three tables.
1. Recipe
2. Component (A recipe consists of components)
3. TT_Recipe_Component (This table holds the link between a certain Recipe and it's components)

I use the following query to fill my recordset :

SELECT * FROM ((TT_Recipe_Component 
  LEFT JOIN Component ON TT_Recipe_Component.Component_NUMBER = Component.NUMBER) 
  LEFT JOIN Recipe ON TT_Recipe_Component.Recipe_NUMBER = Recipe.NUMBER) 
  WHERE TT_Recipe_Component.Recipe_NUMBER IN 
  (SELECT NUMBER FROM Recipe WHERE CODE = '" & Recipe_List.Code & "')

Now when I delete a record from that recordset, I would like the "delete" query to delete ONLY the link from the
TT_Recipe_Component table.
Instead of just doing that the query (cascades) also
deletes the Component from the Component table and the Recipe form the Recipe table.

How can I delete a record without having to create a new
recordset with a seperate connection to TT_Recipe_Component?

Linux IS userfriendly.
It's only very selective about who it's friends are.

 

[jebenson]

Could you show the code for how you are retrieving the records (i.e., connection open, recordset open/population, etc.) and the code for how you are deleting the record?



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Genotix]

This is my entire code, note that it's still in development :
Hope it's not to big of a mess for you to read.
(I'm still new in VB so should you have any advice, I'd be pleased to hear)

Option Explicit

Public RSRecipe_Component As ADODB.Recordset
Public DBRecipe_Component As ADODB.Connection

Public RSComponent As ADODB.Recordset
Public DBComponent As ADODB.Connection

Public RSRecipe As ADODB.Recordset
Public DBRecipe As ADODB.Connection

Public RSRCCombined As ADODB.Recordset
Public DBRCCombined As ADODB.Connection


Private Sub New_Click()
Editcolor True
RSRec_List.AddNew
' LinkComponents
' RSRec_List!Recipe_NUMBER = BORA_DE.rsRecipeList!Number

End Sub

Private Sub Delete_Click()
Dim Message As String
 
 Message = MsgBox("Weet u zeker dat u deze grondstof uit het recept wilt verwijderen?", vbOKCancel)
 If Message = 1 Then
   RSRCCombined.Delete
   Components.Refresh
 End If

End Sub

Private Sub Update_Click()
 Dim Message As String
 Editcolor False
 
 If Not (RSRecipe_Component.BOF Or RSRecipe_Component.EOF) Then
   LinkComponents
   RSRecipe_Component.Update
 Else
  Message = MsgBox("Er is geen grondstof geselecteerd (Klik op Nieuw om een grondstof toe te voegen)", vbCritical)
 End If
 
End Sub

Private Sub DataGrid1_Click()
 Editcolor False
End Sub



Private Sub Form_Load()

ConnectRCCombined
GetRCCombined
LinkComponents
FillListBox_Component

End Sub

Function LinkComponents()
 Set Components.DataSource = RSRCCombined
 With Components
'         .DataSource =
         .Refresh
 End With

 Set Percentage.DataSource = RSRecipe_Component
 With Percentage
         .DataField = "Percentage"
         .Refresh
 End With
 
 Set Component.DataSource = RSRecipe_Component
 With Component
         .DataField = "Component_NUMBER"
         .Refresh
 End With

End Function

Private Sub OK_Click()
 CloseConnection
 Unload Me
End Sub

Function CloseConnection()

'RSRecipe_Component.Close
'DBRecipe_Component.Close

'RSComponent.Close
'DBComponent.Close

'RSRecipe.Close
'DBRecipe.Close

RSRCCombined.Close
DBRCCombined.Close

Set RSRecipe_Component = Nothing
Set DBRecipe_Component = Nothing

Set RSComponent = Nothing
Set DBComponent = Nothing

Set RSRecipe = Nothing
Set DBRecipe = Nothing

Set RSRCCombined = Nothing
Set DBRCCombined = Nothing

End Function

Function FillListBox_Component()
 Dim Conn As ADODB.Connection
 Dim Rs As ADODB.Recordset
 Dim strQuery As String
 
Set Conn = New ADODB.Connection
 With Conn
 .CursorLocation = adUseClient
 .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Development_Kerry\DATABASE\Bora.mdb;Persist Security Info=False"
 .Open
 End With

strQuery = "SELECT * FROM Component"

Set Rs = New ADODB.Recordset
 With Rs
 Set .ActiveConnection = Conn
     .CursorType = adOpenStatic
     .Source = strQuery
     .Open
 End With

'While Not (Rs.EOF)
'Rs.MoveFirst
'Component.AddItem Rs!Description & " - " & Rs!Code
'Component.AddItem Rs!Description
'Rs.MoveNext
'Wend

 Rs.Close
 Set Rs = Nothing
 Conn.Close
 Set Conn = Nothing
End Function

'-----------------------------------------------------------------------------------------------------
' A function that displays the editing fields blue when a new record is created
'-----------------------------------------------------------------------------------------------------

Private Sub Editcolor(isnew)
 Dim NewColBack As Long
 Dim NewColFore As Long
 
 If isnew Then
  NewColBack = &H8000000D
  NewColFore = &H8000000F
 Else
  NewColBack = &H80000005
  NewColFore = &H80000007
 End If
 
 Component.BackColor = NewColBack
 Component.ForeColor = NewColFore
 Percentage.BackColor = NewColBack
 Percentage.ForeColor = NewColFore
 
End Sub


'---------------------------------------------------
'                  The RecordSets
'---------------------------------------------------

Function GetRecipe()

  Set RSRecipe = New ADODB.Recordset
  With RSRecipe
  Set .ActiveConnection = DBRecipe
      .CursorType = adOpenForwardOnly
      .Source = "SELECT * FROM Recipe WHERE CODE = '" & Recipe_List.Code & "'"
      .CursorLocation = adUseClient
      .LockType = adLockOptimistic
      .Open
  End With
  
End Function

Function GetComponent()
  
  Set RSComponent = New ADODB.Recordset
  With RSComponent
  Set .ActiveConnection = DBComponent
      .CursorType = adOpenForwardOnly
      .Source = "SELECT * FROM Component"
      .CursorLocation = adUseClient
      .LockType = adLockOptimistic
      .Open
  End With

End Function

Function GetRecipe_Component()
  Set RSRecipe = New ADODB.Recordset
  With RSRecipe
  Set .ActiveConnection = DBRecipe
      .CursorType = adOpenForwardOnly
      .Source = "SELECT * FROM TT_Recipe_Component WHERE Recipe_NUMBER IN (SELECT NUMBER FROM Recipe WHERE CODE = '" & Recipe_List.Code & "'"
      .CursorLocation = adUseClient
      .LockType = adLockOptimistic
      .Open
  End With

End Function

Function GetRCCombined()
  Set RSRCCombined = New ADODB.Recordset
  With RSRCCombined
  Set .ActiveConnection = DBRCCombined
      .CursorType = adOpenForwardOnly
      .Source = "SELECT * FROM ((TT_Recipe_Component LEFT JOIN Component ON TT_Recipe_Component.Component_NUMBER = Component.NUMBER) LEFT JOIN Recipe ON TT_Recipe_Component.Recipe_NUMBER = Recipe.NUMBER) WHERE TT_Recipe_Component.Recipe_NUMBER IN (SELECT NUMBER FROM Recipe WHERE CODE = '" & Recipe_List.Code & "')"
      .CursorLocation = adUseClient
      .LockType = adLockOptimistic
      .Open
  End With

End Function

'---------------------------------------------------
'            The Database Connections
'---------------------------------------------------
Function ConnectRecipe()
  
  Set DBRecipe = New ADODB.Connection
  With DBRecipe
      .CursorLocation = adUseClient
      .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Development_Kerry\DATABASE\Bora.mdb;Persist Security Info=False"
      .Open
  End With
  
End Function

Function ConnectComponent()
  
  Set DBComponent = New ADODB.Connection
  With DBComponent
      .CursorLocation = adUseClient
      .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Development_Kerry\DATABASE\Bora.mdb;Persist Security Info=False"
      .Open
  End With

End Function

Function ConnectRecipe_Component()
  
  Set DBRecipe_Component = New ADODB.Connection
  With DBRecipe_Component
      .CursorLocation = adUseClient
      .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Development_Kerry\DATABASE\Bora.mdb;Persist Security Info=False"
      .Open
  End With

End Function

Function ConnectRCCombined()
  
  Set DBRCCombined = New ADODB.Connection
  With DBRCCombined
      .CursorLocation = adUseClient
      .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Development_Kerry\DATABASE\Bora.mdb;Persist Security Info=False"
      .Open
  End With

End Function

Linux IS userfriendly.
It's only very selective about who it's friends are.

 

[jebenson]

Okay, here's a few observations from a quick perusal of your code.

1) You don't need all of the different Connection objects - they all are connecting to the same database. Individual recordsets do not need a separate connection; they can all share one connection. I have applications that use several different recordsets, but only one connection. Make just one global connection and use it for all of your database access. It shouldn't be that hard to make the change...just use search and replace.

2) The reason that everything is getting deleted is because you are using the Delete method of the recordset object, and the recordset object contains Joins of the tables. So when you use the recordset object's Delete method, it delete's all associated records, in all Joined tables. To make the recordset delete from only one table, set the recordset object's UniqueTable property to the name of the table where the deletes are to occur. For example:

Function GetRCCombined()
  Set RSRCCombined = New ADODB.Recordset
  With RSRCCombined
  Set .ActiveConnection = DBRCCombined
      .CursorType = adOpenForwardOnly
      .Source = "SELECT * FROM ((TT_Recipe_Component LEFT JOIN Component ON TT_Recipe_Component.Component_NUMBER = Component.NUMBER) LEFT JOIN Recipe ON TT_Recipe_Component.Recipe_NUMBER = Recipe.NUMBER) WHERE TT_Recipe_Component.Recipe_NUMBER IN (SELECT NUMBER FROM Recipe WHERE CODE = '" & Recipe_List.Code & "')"
      .CursorLocation = adUseClient
      [b].Properties("Unique Table") = "TT_Recipe_Component"[/b]
      .LockType = adLockOptimistic
      .Open
  End With

End Function

Now I would like to qualify this by saying that I have never actually used this functionality - I never use the recordset's Delete method. There's too much going on over which you have no control. Instead of using the Delete method, I use the Connection object's Execute method to run a SQL DELETE command (with proper qualifiers of course). For example:

Private Sub Delete_Click()
Dim Message As String
 
 Message = MsgBox("Weet u zeker dat u deze grondstof uit het recept wilt verwijderen?", vbOKCancel)
 If Message = 1 Then
[b]   'note: new global connection object
   Conn.Execute "DELETE FROM TT_Recipe_Component WHERE Component_NUMBER=" & Variable_With_Component_NUMBER_To_Delete
   RSRCCombined.Requery[/b]
   Components.Refresh  
 End If

End Sub

This will delete the record(s) you want deleted - all those that share the same Component_NUMBER (it should be only one record - I assume Component_NUMBER is unique). You just need to determine the Component_NUMBER to delete. You might also need to requery the database with RSRCCombined.Requery before you call Components.Refresh.

I hope this is helpful. Let me know how it works out.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Genotix]

1. Great tip!, I'll use only one DB connection per client from now on.
2. Okay, so by declaring a Unique Table in the property's i'm allowing VB to ONLY update / delete that specific table.
3. I'm sorry for this question, but...
- How do I declare this global connection object?

As you can see, i'm also using an update command.
I think I'll use the same method for updating.

Thnx 4 your time!
You're helping me out very well!

Linux IS userfriendly.
It's only very selective about who it's friends are.

 

[jebenson]

You declare it the same way you would normally - just put the declaration in the General Declarations section of the form's code (i.e., at the top of the code page before any subs, functions, etc.). Then any sub or function in the form will be able to acces the connection. You can even make it accessible to other forms/modules by declaring it Public:

Public Conn As ADODB.Connection

Then if your form is named Form1, other forms/modules could access the connection like this:

Form1.Conn

P.S., I got a good laugh out of your little bit of Linux wisdom!

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Genotix]

Hmm.. okay.. I didn't know that it was that easy.

I'm used to programming C / Pascal / PHP / Bash / QBasic / A bit of ASM.
This stuff looks very much like CA Openroad (Unix)

Thnx 4 your reply this stuff is something i've been working on for about a week.

Unfortunately the book i've ordere that should help me pass my "exam" is on it's way for about three weeks now.

I think you can imagine why I sometimes get really P!ssed at VB6.
Some things are extremely easy to create (gui for instance)
The coupling of data is also manageble except when you're using linking tables.

I've still got one little problem with this but I'll open another threat for that.

Thnx again!

Eric

P.S. :

Q: How many hardware engineers does it take to change a light bulb?
A: None. We'll fix it in software.

Q: How many system programmers does it take to change a light bulb?
A: None. The application can work around it.

Q: How many software engineers does it take to change a light bulb?
A: None. We'll document it in the manual.

Q: How many tech writers does it take to change a light bulb?
A: None. The user can figure it out.

Linux IS userfriendly.
It's only very selective about who it's friends are.