took your advice hal and created a database trigger for the MASTER table to delete from related tables - works like a champ now - wasted way too much time trying to get this to work in access thru pass thru query...
--- [EMAIL PROTECTED] wrote: > John, > Oracle is a two phase commit system, after each > SQL statement you need a > COMMIT; statement or the database will ROLLBACK your > statements undone or > leave them uncommitted on your id only. You will be > much better off to get > your friendly Oracle DBA to set up a stored > procedure for you to carry out > this procedure. > Good Luck > > Hal McGee > Engineering Data Manager > Group Engineering - Process and Compliance > Seating Products Group > B/E Aerospace > Winston-Salem, NC > > > > > Bill Pfeffer > > <[EMAIL PROTECTED] > > oo.com> > To > Sent by: > AccessDevelopers@yahoogroups.com > AccessDevelopers@ > cc > yahoogroups.com > > > Subject > RE: > [AccessDevelopers] trying to > 10/13/2005 09:50 delete > multiple records from oracle > PM db with > access frontend > > > > > Please respond to > > AccessDevelopers@ > > yahoogroups.com > > > > > > > > > > there is commonality between the tables - they share > the same key - SSN - there are 8 tables that are > linked to a MASTER table - i could have the users > delete these detail records in each of these 8 > tables > one at a time, but thought it would be better to > have > them press a button from the MASTER record to clean > out the detail records - and i tried it without the > asterisk with the same results - no records > deleted... > > --- John Ruff <[EMAIL PROTECTED]> wrote: > > > First of all remove the asterisk in: > > > > > > > > sqlstr = "delete * from MEMBER where ssn=" & > sqlssn > > > > > > > > Is there a relationship between the three tables? > If > > so, you may need to > > delete the records from the GIFT table, then the > > ADDR table, then the MEMBER > > table, in that order. > > > > > > > > John V. Ruff - The Eternal Optimist :-) > > > > > > > > "Commit to the Lord whatever you do, > > > > and your plans will succeed." > > Proverbs 16:3 > > > > > > > > _____ > > > > From: AccessDevelopers@yahoogroups.com > > [mailto:[EMAIL PROTECTED] On > Behalf > > Of bill_pfeffer > > Sent: Thursday, October 13, 2005 3:05 PM > > To: AccessDevelopers@yahoogroups.com > > Subject: [AccessDevelopers] trying to delete > > multiple records from oracle db > > with access frontend > > > > > > > > i have the following code behind a command button > on > > my main_form - > > am trying to delete records based on SSN from > > multiple tables - the > > connect string that i use is also used to get > > nextval from this same > > oracle database, so i know that's correct - not > sure > > what i'm doing > > wrong here...the records are obviously not deleted > > even though the > > sql string looks correct - > > > > MsgBox "Warning - this will Delete all related > > records in the > > database for " & FIRSTNAME & " " & MINAME & " " > & > > LASTNAME & " " & > > SSN > > > > Dim mydatabase As DAO.Database > > Dim myquerydef As DAO.QueryDef > > Dim sqlstr As String > > Dim sqlssn As String > > > > Set mydatabase = CurrentDb() > > > > Set myquerydef = > mydatabase.CreateQueryDef(memberq) > > myquerydef.Connect > > = > > > "ODBC;DSN=xxxxxx.xxxx.xx;UID=xxxxxx;PWD=xxxxxx;SERVER=ADM2" > > sqlssn = "'" & SSN & "'" & ";" > > sqlstr = "delete * from MEMBER where ssn=" & > sqlssn > > MsgBox sqlstr > > myquerydef.SQL = sqlstr > > myquerydef.Close > > > > 'Set myquerydef = mydatabase.CreateQueryDef(giftq) > > 'sqlstr = "delete from GIFT where ssn=" & sqlssn > > 'MsgBox "delete from GIFT" > > 'myquerydef.SQL = sqlstr > > 'myquerydef.Close > > > > 'Set myquerydef = mydatabase.CreateQueryDef(addrq) > > 'sqlstr = "delete from ADDR where ssn=" & sqlssn > > 'MsgBox "delete from ADDR" > > 'myquerydef.SQL = sqlstr > > 'myquerydef.Close > > > > > > > > > > > > > > Please zip all files prior to uploading to Files > > section. > > > > > > > > > > SPONSORED LINKS > > > > > > Microsoft > > > < > http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+developer&w1=Microsoft > > > +access+developer&w2=Microsoft+access+help&w3=Microsoft+access+database&w4=M > > > > icrosoft+access+training&w5=Microsoft+access+training+course&w6=Microsoft+ac > > > > cess+programming&c=6&s=193&.sig=d-CjBIrYOH9NCKHYFeGZJA> > > access developer > > > > Microsoft > > > < > === message truncated === ------------------------ Yahoo! Groups Sponsor --------------------~--> Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life. http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
