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: [email protected]
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: [email protected]
> [mailto:[EMAIL PROTECTED] On Behalf
> Of bill_pfeffer
> Sent: Thursday, October 13, 2005 3:05 PM
> To: [email protected]
> 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
>
<
http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+help&w1=Microsoft+acce
>
ss+developer&w2=Microsoft+access+help&w3=Microsoft+access+database&w4=Micros
>
oft+access+training&w5=Microsoft+access+training+course&w6=Microsoft+access+
> programming&c=6&s=193&.sig=crx-d4AAhdklv_VozGVAUw>
> access help
>
> Microsoft
>
<
http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database&w1=Microsoft+
>
access+developer&w2=Microsoft+access+help&w3=Microsoft+access+database&w4=Mi
>
crosoft+access+training&w5=Microsoft+access+training+course&w6=Microsoft+acc
>
ess+programming&c=6&s=193&.sig=qg2hDuQNweByMCX0NU7cEA>
> access database
>
>
> Microsoft
>
<
http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+training&w1=Microsoft+
>
access+developer&w2=Microsoft+access+help&w3=Microsoft+access+database&w4=Mi
>
crosoft+access+training&w5=Microsoft+access+training+course&w6=Microsoft+acc
>
ess+programming&c=6&s=193&.sig=bLZHqTqWUQny609X1OkmNA>
> access training
>
> Microsoft
>
<
http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+training+course&w1=Mic
>
rosoft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+databas
>
e&w4=Microsoft+access+training&w5=Microsoft+access+training+course&w6=Micros
>
oft+access+programming&c=6&s=193&.sig=d8GQXfQW3RZ64rOfzIMo8A>
> access
> training course
>
> Microsoft
>
<
http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+programming&w1=Microso
>
ft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+database&w4
>
=Microsoft+access+training&w5=Microsoft+access+training+course&w6=Microsoft+
>
access+programming&c=6&s=193&.sig=iXDlL79-kkgjv6fLyFu3Sg>
> access
> programming
>
>
>
> _____
>
> YAHOO! GROUPS LINKS
>
>
>
> * Visit your group "AccessDevelopers
> <http://groups.yahoo.com/group/AccessDevelopers> "
> on the web.
>
> * To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
>
<mailto:[EMAIL PROTECTED]>
>
>
> * Your use of Yahoo! Groups is subject to the
> Yahoo!
> <http://docs.yahoo.com/info/terms/> Terms of
> Service.
>
>
>
> _____
>
>
Please zip all files prior to uploading to Files section.
SPONSORED LINKS
Microsoft access Microsoft access help Microsoft access
developer database
Microsoft access Microsoft access Microsoft access
training training course programming
YAHOO! GROUPS LINKS
Visit your group "AccessDevelopers" on the web.
To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
------------------------ Yahoo! Groups Sponsor --------------------~-->
Fair play? Video games influencing politics. Click and talk back!
http://us.click.yahoo.com/T8sf5C/tzNLAA/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/
