hi Kevin,
between each db.Execute, do:
db.Tabledefs.refresh
DoEvents
~crystal
VBA code you can use in your projects
http://msaccessgurus.com/code.htm
On 12/30/2018 5:38 AM, qingqinga qingqinga@yahoo.com [MS_Access_Professionals] wrote:
Dear Duane,
I'll try. Thanks a lot.
Best Regards,
Kevin
发自 WPS邮箱客戶端在 "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2018年12月30日 14:44写道：
I would try to set a break point and step through the code to check the results of each Execute.
Also, many of us don't typically delete. We set a status field to inactive or deleted.
Regards,Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of qingqinga qingqinga@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Sunday, December 30, 2018 12:33 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Some tables are not deletedDear Duane,
Yes, I did delete from the lowest level up to the top. But there are several tables in the middle level which don't necessarily connect to one another, except for sharing the same GroupID. In this case, if one of them has no data, but if it's deleting code is ahead of those tables which contain data, then these tables will not be able to start deleting. Thanks.
Best Regards,
Kevin
发自 WPS邮箱客戶端在 "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2018年12月30日 14:16写道：
Zhao LiQing,
You should be more descriptive than "won't work".
Are you deleting from the lowest level of the relationships first and then stepping up to higher levels?
Regards,Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Qingqing qingqinga@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, December 29, 2018 9:35 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Some tables are not deletedDear All,
I was trying to delete all the data from different tables with link to "GroupID". Sometimes it worked when all tables contain data, other times won't work when there's no data in some of the tables. Here's the code. Please help. Thanks.
------------------------------------------------------------------
On Error GoTo ErrHandle
Dim strPasswd
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLGroupRoom As String
Dim strSQLPart As String
Dim strSQLPartBooking As String
Dim strSQLPartItinerary As String
Dim strSQLDayCity As String
Dim strSQLCityBooking As String
Dim strSQLCityPlaceToVisit As String
Dim strSQLCityBookingRoom As String
Dim strSQLHotelRoom As String
Dim strSQLRoomingList As String
Dim strSQLSubRoomingList As String
Dim strSQLClientPayment As String
Dim strSQLCoachPayment As String
Dim strSQLHotelPayment As String
Dim strSQLHotelBooking As String
Dim strSQLCoachBooking As String
strSQLGroupRoom = "DELETE * FROM tblGroupRoom" & _
" WHERE GroupID = " & Me.GroupID
strSQLPart = "DELETE * FROM tblPart" & _
" WHERE GroupID = " & Me.GroupID
strSQLPartBooking = "DELETE * FROM tblPartBooking" & _
" WHERE PBGroupID = " & Me.GroupID
strSQLPartItinerary = "DELETE * FROM tblPartItinerary" & _
" WHERE PIGroupID = " & Me.GroupID
strSQLDayCity = "DELETE * FROM tblDayCity" & _
" WHERE DCGroupID = " & Me.GroupID
strSQLCityBooking = "DELETE * FROM tblCityBooking" & _
" WHERE CBGroupID = " & Me.GroupID
strSQLCityPlaceToVisit = "DELETE * FROM tblCityPlaceToVisit" & _
" WHERE CPTVGroupID = " & Me.GroupID
strSQLCityBookingRoom = "DELETE * FROM tblCityBookingRoom" & _
" WHERE CBRGroupID = " & Me.GroupID
strSQLHotelRoom = "DELETE * FROM tblHotelRoom" & _
" WHERE HRGroupID = " & Me.GroupID
strSQLRoomingList = "DELETE * FROM tblRoomingList" & _
" WHERE GroupID = " & Me.GroupID
strSQLSubRoomingList = "DELETE * FROM tblSubRoomingList" & _
" WHERE SRLGroupID = " & Me.GroupID
strSQLClientPayment = "DELETE * FROM tblClientPayment" & _
" WHERE ClientPaymentGroupID = " & Me.GroupID
strSQLCoachPayment = "DELETE * FROM tblCoachPayment" & _
" WHERE CPaymentGroupID = " & Me.GroupID
strSQLHotelPayment = "DELETE * FROM tblHotelPayment" & _
" WHERE HPaymentGroupID = " & Me.GroupID
strSQLHotelPayment = "DELETE * FROM tblHotelBooking" & _
" WHERE HBGroupID = " & Me.GroupID
strSQLHotelPayment = "DELETE * FROM tblCoachBooking" & _
" WHERE CBookingGroupID = " & Me.GroupID
Set db = CurrentDb
strPasswd = InputBox("Enter Temporary Password", "Restricted Form")
If strPasswd = "" Or strPasswd = Empty Then
MsgBox "No Input Provided", vbInformation, "Required Temporary Password"
Exit Sub
End If
If strPasswd = ELookup("TemporaryPass", "tblSettings", "SettingsID=" & 1) Then
If vbYes = MsgBox("Are you sure you want to delete this Group?", vbQuestion + vbYesNo + vbDefaultButton2) Then
db.Execute strSQLGroupRoom, dbFailOnError
db.Execute strSQLPart, dbFailOnError
db.Execute strSQLPartBooking, dbFailOnError
db.Execute strSQLPartItinerary, dbFailOnError
db.Execute strSQLDayCity, dbFailOnError
db.Execute strSQLCityBooking, dbFailOnError
db.Execute strSQLCityPlaceToVisit, dbFailOnError
db.Execute strSQLCityBookingRoom, dbFailOnError
db.Execute strSQLHotelRoom, dbFailOnError
db.Execute strSQLRoomingList, dbFailOnError
db.Execute strSQLSubRoomingList, dbFailOnError
db.Execute strSQLClientPayment, dbFailOnError
db.Execute strSQLCoachPayment, dbFailOnError
db.Execute strSQLHotelPayment, dbFailOnError
db.Execute strSQLHotelBooking, dbFailOnError
db.Execute strSQLCoachBooking, dbFailOnError
End If
Me.Requery
If Not (fDelCurrentRec(Me)) Then
MsgBox "An Error occurred!"
End If
Else
MsgBox "Sorry, you do not have access to this information", vbOKOnly, "Important Information"
Exit Sub
End If
ErrExit:
Exit Sub
ErrHandle:
Resume ErrExit
--------------------------------------------------------------------------------------
Zhao LiQing
Be adventurous, be bold, be careful, be a star !
------------------------------------
Posted by: Qingqing <qingqinga@yahoo.com>
------------------------------------
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)
<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
__._,_.___
Posted by: crystal 8 <strive4peace2008@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
SPONSORED LINKS
.
__,_._,___
Tidak ada komentar:
Posting Komentar