Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 2nd, 2007, 11:07 AM
Authorized User
 
Join Date: Feb 2007
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem deleting records from a table

I have two tables, tblMain and tblWorked. I want to delete records from tblMain where tblMain.loan_number = tblWorked.loan_number. When I start a new query by selecting new query, design view. I brought up the tblMain and tblWorked then joined the two tables by loan_number. In the first column, Field has tblMain.*, Table has tblMain, Delete has From. The second column, Field has loan_number, Table has tblMain, Delete has Where, Criteria has tblWorked.loan_number. When I select the view button, I see the records I want to delete. But when I run the query, it states "Cannot delete from specified tables. I've also tried to use the following SQL statement:

delete tblMain.*
from tblMain
where tblMain.loan_number = tblWorked.loan_number

This returns a box asking me to enter the parameter value. What am I doing wrong???

 
Old March 2nd, 2007, 11:43 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

What is the relationship between tblMain and tblWorked? Is Referential Integrity with cascade deletes and cascade updates enforced? If so, try deleting from the child table (suspect it's tblWorked) FIRST, then the main table.

HTH,

Loralee

 
Old March 2nd, 2007, 11:58 AM
Authorized User
 
Join Date: Feb 2007
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

currently it's just a one-to-one relationship. I want to keep the records in the tblWorked table and delete the matching records in the tblMain table (since they have not been worked). Then I want to append the worked records from tblWorked to tblMain. And the reason for this is because of multiple users working from their individual laptops without a network connection.

 
Old March 2nd, 2007, 03:22 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You need to append the records from tblWorked to tblMain FIRST,

Then delete the records from tblWorked,

Then delete the records from tblMain.

You can do this in 3 seperate queries. The first an Append Query, and the other two Delete Queries.

DoCmd.SetWarnings False
DoCmd.OpenQuery "AppendQuery"
DoCmd.OpenQuery "DeletetblWorked"
DoCmd.OpenQuery "DeletetblMain"
DoCmd.SetWarnings True

Did that work?

mmcdonal
 
Old March 2nd, 2007, 03:58 PM
Authorized User
 
Join Date: Feb 2007
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, that got it. Thanks.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting Duplicate Records BrianWren Pro VB Databases 1 February 22nd, 2008 01:30 PM
Deleting all the records in a table suthaharmca Beginning VB 6 4 January 2nd, 2008 06:14 PM
Deleting duplicate records ninel SQL Server 2000 3 June 14th, 2006 02:12 PM
Deleting multiple records from the same table jscrogg Beginning VB 6 3 June 12th, 2006 05:30 AM
Problem with adding deleting records in bounded method Access 1 July 30th, 2005 02:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.