Results 1 to 5 of 5

Thread: Deleting from 2 database tables

  1. #1

    Thread Starter
    Hyperactive Member jlt7's Avatar
    Join Date
    Jan 2006
    Posts
    413

    Deleting from 2 database tables

    this is what I have and it will delete from tblactor but I'm not sure how to add in the other table(tblactorlink) so I can delete for it also

    tables
    tblactor
    actor_id

    tblactorlink
    LactorID
    this is the code so far

    VB Code:
    1. sql = " DELETE FROM tblActor" & _
    2. " WHERE tblActor.Actor_ID = " & lblCntr3.Caption

  2. #2

    Thread Starter
    Hyperactive Member jlt7's Avatar
    Join Date
    Jan 2006
    Posts
    413

    Re: Deleting from 2 database tables

    This is my code

    VB Code:
    1. Dim cn As ADODB.Connection
    2.     Dim rs As ADODB.Recordset
    3.     Dim DbasePath As String
    4.     Dim sql As String
    5.  
    6.     DbasePath = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    7.             "Data Source= " & App.Path & "/movieguide.MDB"
    8.            
    9.            
    10.     Set cn = New ADODB.Connection
    11.         cn.Open DbasePath
    12.     Set rs = New ADODB.Recordset
    13.  
    14.  
    15. sql = "DELETE * FROM tblActor " & _
    16.       "WHERE tblActor.MovieID = " & lblCounter2.Caption & _
    17.       "AND tblActor.actor_ID = " & lblCntr3.Caption
    18.       rs.Open sql, cn
    but I want to delete the info from tblactorlink also, How would I do that.
    Last edited by jlt7; May 2nd, 2006 at 07:40 PM.

  3. #3
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: Deleting from 2 database tables

    What db are you using and do you have a relationship defined between the two tables? If so then you will need to delete the child record first and then the parent record.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4

    Thread Starter
    Hyperactive Member jlt7's Avatar
    Join Date
    Jan 2006
    Posts
    413

    Re: Deleting from 2 database tables

    It will delete from the tblactor table but I want it to delete the tblactorlink table also.
    this is my code
    VB Code:
    1. Dim cn As ADODB.Connection
    2.     Dim rs As ADODB.Recordset
    3.     Dim DbasePath As String
    4.     Dim sql As String
    5.  
    6.     DbasePath = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    7.             "Data Source= " & App.Path & "/movieguide.MDB"
    8.            
    9.            
    10.     Set cn = New ADODB.Connection
    11.         cn.Open DbasePath
    12.     Set rs = New ADODB.Recordset
    13.  
    14.  
    15. sql = "DELETE * FROM tblActor " & _
    16.       "WHERE tblActor.MovieID = " & lblCounter2.Caption & _
    17.       "AND tblActor.actor_ID = " & lblCntr3.Caption
    18.       rs.Open sql, cn
    Last edited by jlt7; May 2nd, 2006 at 07:50 PM.

  5. #5
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,725

    Re: Deleting from 2 database tables

    As mentioned by RobDog888. Have you defined relationships between the two tables? If you are using MS ACCESS then define the relationships between two tables and check the cascade delete related records checkbox.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width