Docmd.RunSql

Jasonhwrd · December 23rd, 2010, 05:50 PM

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?

HiTechCoach · December 24th, 2010, 03:37 AM

First thing I spotted is that you need a space before the FROM and the WHERE

boblarson · December 24th, 2010, 03:02 PM

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'"

Jasonhwrd · December 28th, 2010, 09:04 PM

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

HiTechCoach · December 29th, 2010, 12:53 AM

Quote:

Originally Posted by Jasonhwrd

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

HiTechCoach · December 29th, 2010, 12:54 AM

Quote:

Originally Posted by boblarson

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.

Jasonhwrd · January 1st, 2011, 04:24 PM

This looks like it wants to work, however now I get an error stating that Could not delete from specified tables.

HiTechCoach · January 1st, 2011, 07:12 PM

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?

Jasonhwrd · January 3rd, 2011, 08:58 PM

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.

HiTechCoach · January 4th, 2011, 12:30 AM

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.