468,765 Members | 1,233 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,765 developers. It's quick & easy.

Delete Record - DAO to ADO

I'm trying to clean up a database of mine, and I need to convert some
old DAO code over to ADO. When I was using DAO, I had no problem
running this script and deleting the record using rs.Delete. But when
I rework the code in ADO, it tells me I can't delete it because there
are related records in another table. Never had that problem in DAO
and I haven't touched the relationships. Ignore most of this code and
focus on the recordset "rs". Why can't I delete it??

Dim rs As New ADODB.Recordset
Dim criteria As String
Dim empts As Integer
empts = 0
If Not IsNull(Forms![View/EditPulls]![PullID]) Then
criteria = Forms![View/EditPulls]![PullID]
Else
Exit Sub
End If
rs.Open "SELECT * FROM ShipPull WHERE PullID = '" & criteria &
"'", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If rs.RecordCount <0 Then
rs.MoveFirst
Else
GoTo NoItems
End If

Dim prompt1 As String
Dim prompt2 As String
Dim id As String
Dim prodName As String
Dim numPulled As String
Dim ln As String
Dim inv1 As Integer
Dim inv2 As Integer
Dim totalLeft As Integer
Dim ship1 As Variant
Dim ship2 As Variant
ln = Chr(13) & Chr(10)

Do Until rs.EOF
ship1 = 0
ship2 = 0
id = rs.Fields("ProductID")
prodName = rs.Fields("ProductName")
numPulled = CStr(rs.Fields("Quantity"))
inv1 = rs.Fields("Quantity1")
inv2 = rs.Fields("Quantity2")
prompt1 = "Pull from " & loc1 & ":" & ln & Chr(10) & "Product
ID: " & id & ln & prodName & ln & "Number Pulled: " & numPulled
prompt2 = "Pull from " & loc2 & ":" & ln & Chr(10) & "Product
ID: " & id & ln & prodName & ln & "Number Pulled: " & numPulled
'
If CInt(numPulled) <= (inv1) Then '+inv2
GetShip1:
ship1 = InputBox(prompt1, "Ship Product", numPulled)
If ship1 = "" Or Not (IsNumeric(ship1)) Then
MsgBox "Invalid Entry"
GoTo GetShip1
End If

rs.Fields("Quantity1") = rs.Fields("Quantity1") -
ship1
rs.Fields("Quantity2") = rs.Fields("Quantity2") -
ship2
rs.Update

Dim strSQL
Dim Today As Variant

Dim rsHistory As ADODB.Recordset
Set rsHistory = New ADODB.Recordset
rsHistory.Open "SELECT * FROM History",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With rsHistory
.AddNew
.Fields("PullID") = "'" & rs.Fields("PullID") &
"'"
.Fields("ProductID") = rs.Fields("ProductID")
.Fields("Quantity") = ship1
.Fields("ProductName") = "'" &
rs.Fields("ProductName") & "'"
.Fields("Description") = "'" &
rs.Fields("Description") & "'"
.Fields("Category") = "'" & rs.Fields("Category")
& "'"
.Fields("ShipDate") = Date
.Update
End With
rs.Delete
Me.Refresh

Mar 31 '07 #1
3 5917
On 30 Mar 2007 19:46:20 -0700, "igendreau"
<ia**********@hermanmiller.comwrote:

You probably knew you were going to hear this from one of us, so here
goes:
Let me understand this: you rewrote a perfectly good working routine,
just because you want to "clean it up", and now it's broke? What's
wrong with turning back on that folly idea and reverting back to the
old code? ADO is not better than DAO, especially not for code that
works.

Now to the cause of this problem: I would think you could run this
EXACT same scenario using the DAO code and it would fail as well. The
reason: if a record cannot be deleted because of related records in
another table, no DAO or ADO or XYZ will make a difference. Jet will
stop any one of them in their tracks. When you try to delete the
record through the Access table interface, the exact same thing will
happen as well.
You should debug this further, find out what record is causing the
problem, and then study the database design and the records in the
related tables, and you'll have your AhaErlebniss.

-Tom.
>I'm trying to clean up a database of mine, and I need to convert some
old DAO code over to ADO. When I was using DAO, I had no problem
running this script and deleting the record using rs.Delete. But when
I rework the code in ADO, it tells me I can't delete it because there
are related records in another table. Never had that problem in DAO
and I haven't touched the relationships. Ignore most of this code and
focus on the recordset "rs". Why can't I delete it??

Dim rs As New ADODB.Recordset
Dim criteria As String
Dim empts As Integer
empts = 0
If Not IsNull(Forms![View/EditPulls]![PullID]) Then
criteria = Forms![View/EditPulls]![PullID]
Else
Exit Sub
End If
rs.Open "SELECT * FROM ShipPull WHERE PullID = '" & criteria &
"'", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If rs.RecordCount <0 Then
rs.MoveFirst
Else
GoTo NoItems
End If

Dim prompt1 As String
Dim prompt2 As String
Dim id As String
Dim prodName As String
Dim numPulled As String
Dim ln As String
Dim inv1 As Integer
Dim inv2 As Integer
Dim totalLeft As Integer
Dim ship1 As Variant
Dim ship2 As Variant
ln = Chr(13) & Chr(10)

Do Until rs.EOF
ship1 = 0
ship2 = 0
id = rs.Fields("ProductID")
prodName = rs.Fields("ProductName")
numPulled = CStr(rs.Fields("Quantity"))
inv1 = rs.Fields("Quantity1")
inv2 = rs.Fields("Quantity2")
prompt1 = "Pull from " & loc1 & ":" & ln & Chr(10) & "Product
ID: " & id & ln & prodName & ln & "Number Pulled: " & numPulled
prompt2 = "Pull from " & loc2 & ":" & ln & Chr(10) & "Product
ID: " & id & ln & prodName & ln & "Number Pulled: " & numPulled
'
If CInt(numPulled) <= (inv1) Then '+inv2
GetShip1:
ship1 = InputBox(prompt1, "Ship Product", numPulled)
If ship1 = "" Or Not (IsNumeric(ship1)) Then
MsgBox "Invalid Entry"
GoTo GetShip1
End If

rs.Fields("Quantity1") = rs.Fields("Quantity1") -
ship1
rs.Fields("Quantity2") = rs.Fields("Quantity2") -
ship2
rs.Update

Dim strSQL
Dim Today As Variant

Dim rsHistory As ADODB.Recordset
Set rsHistory = New ADODB.Recordset
rsHistory.Open "SELECT * FROM History",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With rsHistory
.AddNew
.Fields("PullID") = "'" & rs.Fields("PullID") &
"'"
.Fields("ProductID") = rs.Fields("ProductID")
.Fields("Quantity") = ship1
.Fields("ProductName") = "'" &
rs.Fields("ProductName") & "'"
.Fields("Description") = "'" &
rs.Fields("Description") & "'"
.Fields("Category") = "'" & rs.Fields("Category")
& "'"
.Fields("ShipDate") = Date
.Update
End With
rs.Delete
Me.Refresh
Mar 31 '07 #2
I know... but some of my code was realllly old and didn't work
anymore, and I have to write a bunch of new stuff for this database,
and I'm writing it in ADO, and I'd like to keep everything
consistent. Here's the crazy thing... It DID work in DAO, and if I
open the query and try deleting the record manually? It deletes
without a problem! So I am led to believe that my relationships
between tables are okay. I thought maybe it had something to do with
the way I was opening my recordset.

My recordset ("rs") opens certain records from a query called
"ShipPull". If I open the ShipPull query manually, I can delete
records, with no problems. But open it via my code, and deletes get
blocked. Any thoughts?

Mar 31 '07 #3
On 30 Mar 2007 22:28:26 -0700, "igendreau"
<ia**********@hermanmiller.comwrote:

You may need to carefully inspect the execution path and exactly what
error occurs. AFAIK, "can't delete it because there are related
records in another table" only occurs under one condition, and that is
a violation of referential integrity.
Create a temporary table not linked to any other table, and your code
will be able to delete records from it.

-Tom.

>I know... but some of my code was realllly old and didn't work
anymore, and I have to write a bunch of new stuff for this database,
and I'm writing it in ADO, and I'd like to keep everything
consistent. Here's the crazy thing... It DID work in DAO, and if I
open the query and try deleting the record manually? It deletes
without a problem! So I am led to believe that my relationships
between tables are okay. I thought maybe it had something to do with
the way I was opening my recordset.

My recordset ("rs") opens certain records from a query called
"ShipPull". If I open the ShipPull query manually, I can delete
records, with no problems. But open it via my code, and deletes get
blocked. Any thoughts?
Mar 31 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Someonekicked | last post: by
9 posts views Thread by Robert Schneider | last post: by
8 posts views Thread by Steve | last post: by
4 posts views Thread by Susan Bricker | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.