Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 December 23rd, 2010, 05:50 PM
Registered User
 
Join Date: Dec 2010
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Docmd.RunSql

I am trying to run the following sub with out any luck:

Private Sub Command0_Click()

Dim SQL As String

SQL = "DELETE *" & _
"FROM Sheet1, tbl_OrderInfo" & _
"WHERE Sheet1.[Order #]=tbl_OrderInfo.fld_Order_Number And Sheet1.[Order #]<>'Inbound'"

DoCmd.RunSQL SQL

End Sub

When I run this sub I get a Run-time error '3131':
Syntax error in From clause.

This is designed to delete any information in the table "Sheet1" that is already contained in "tbl_OrderInfo" based on Order numbers. I have created the same function as a delete query with no issues, however I want to run this as a series of functions to import data, compare to existing data, then add any new information. Any Suggestions?
 
Old December 24th, 2010, 03:37 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

First thing I spotted is that you need a space before the FROM and the WHERE
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
 
Old December 24th, 2010, 03:02 PM
Authorized User
 
Join Date: Jul 2008
Posts: 38
Thanks: 1
Thanked 2 Times in 2 Posts
Default

Also, to go with Boyd's reply, you also are including too many tables in the FROM and then also should use a join on tbl_OrderInfo : (I also included the extra spaces so it is obvious)
Code:
SQL = "DELETE *" & _
" FROM Sheet1 " & _
" INNER JOIN Sheet1.[Order #] ON tbl_OrderInfo.fld_Order_Number" & _ 
" WHERE Sheet1.[Order #]<>'Inbound'"
__________________
Thanks,

Bob Larson
Access MVP (2008-2010, 2011)
Free Access tutorials, samples, tools
 
Old December 28th, 2010, 09:04 PM
Registered User
 
Join Date: Dec 2010
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default New Error

Thanks for the reply Bob. I tried what you suggested, however now I get a Syntax error in Join Operation. New code is as follows:

Code:
Private Sub Command0_Click()

Dim SQL As String

SQL = "DELETE *" & _
      " FROM Sheet1 " & _
      " INNER JOIN Sheet1.[Order #] ON tbl_OrderInfo.fld_Order_Number" & _
      " WHERE Sheet1.[Order #]<>'Inbound'"
      
DoCmd.RunSQL SQL

End Sub
 
Old December 29th, 2010, 12:53 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

Quote:
Originally Posted by Jasonhwrd View Post
Thanks for the reply Bob. I tried what you suggested, however now I get a Syntax error in Join Operation. New code is as follows:

Code:
Private Sub Command0_Click()

Dim SQL As String

SQL = "DELETE *" & _
      " FROM Sheet1 " & _
      " INNER JOIN Sheet1.[Order #] ON tbl_OrderInfo.fld_Order_Number" & _
      " WHERE Sheet1.[Order #]<>'Inbound'"
      
DoCmd.RunSQL SQL

End Sub
Had a little more time to look closer at your SQL statement.

Try:

Code:
Private Sub Command0_Click()

Dim SQL As String

SQL = "DELETE Sheet1 .*" & _
      " FROM Sheet1 " & _
      " INNER JOIN tbl_OrderInfo ON Sheet1.[Order #] = tbl_OrderInfo.fld_Order_Number " & _
      " WHERE Sheet1.[Order #]<>'Inbound'"
      
DoCmd.RunSQL SQL

End Sub
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
 
Old December 29th, 2010, 12:54 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Thumbs up

Quote:
Originally Posted by boblarson View Post
Also, to go with Boyd's reply, you also are including too many tables in the FROM and then also should use a join on tbl_OrderInfo : (I also included the extra spaces so it is obvious)
Code:
SQL = "DELETE *" & _
" FROM Sheet1 " & _
" INNER JOIN Sheet1.[Order #] ON tbl_OrderInfo.fld_Order_Number" & _ 
" WHERE Sheet1.[Order #]<>'Inbound'"
Greetings Bob.

Great to see you here.
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
 
Old January 1st, 2011, 04:24 PM
Registered User
 
Join Date: Dec 2010
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This looks like it wants to work, however now I get an error stating that Could not delete from specified tables.
 
Old January 1st, 2011, 07:12 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

That may mean that the query is not updateable.

In the table that you are trying to delete from, are you joined on the primary key Field?
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Last edited by HiTechCoach; January 4th, 2011 at 12:24 AM..
 
Old January 3rd, 2011, 08:58 PM
Registered User
 
Join Date: Dec 2010
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default primary Key

Unfortunately the Join field in not the primary key because these fields contain text, and since the "Sheet1" table is a temporary table used to import data from another source which may contain data already in the database the fields I created to be the primary keys do not match. I did get the SQL query to update as written in my first post as a query, however I cannot seem to run a Delete query with a command button. I am attempting to design this database so that the layman can use it without need knowledge of database design. If there is another method I can use I am open to alternative options.
 
Old January 4th, 2011, 12:30 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

Maybe this will help:

Why is my query read-only?

I have run an append query to add the primary keys into a temp table. I use this table to identify the records to be delete in a deelte query.
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015





Similar Threads
Thread Thread Starter Forum Replies Last Post
Using DoCmd.RunSQL to return values jscully Access VBA 10 August 21st, 2017 12:58 PM
Docmd.RunSQL Armand elia BOOK: Access 2003 VBA Programmer's Reference 0 June 17th, 2009 03:47 PM
Docmd.runsql anne.burrows VB How-To 2 October 25th, 2006 08:21 AM
docmd.runsql "select RodMead Access VBA 2 July 31st, 2004 02:55 PM
Help with DoCmd.RunSQL command ricmar Access VBA 3 July 21st, 2004 03:32 PM





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