Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS Access - SQL - "Syntax Error in FROM Clause"

Posted on 2007-08-02
2
Medium Priority
?
887 Views
Last Modified: 2013-12-25
I'm using Microsoft Access 2003.  I have a form with a button.  When I click the button I want to find a particular record and then delete it.  I started with an ADODB Recordset open function using a select * from myTable where....  However, I had a syntax error.  To try to eliminate my syntax problem, I removed the where clause, and simply using a select * from myTable.  However, I'm still getting the error.  The code is below.  Since I'm trying to get the base SQL statement to work, I've commented out the delete command.

I have verified that I have a table named PropertyWorkOrder_JunctionTable in my database.  I've also tried encasing my table name with brackets, but that didn't help either.
Private Sub RemoveFromWOBtn_Click()
   On Error GoTo RemoveFromWOBtn_Click_Err
   Dim rst As New ADODB.Recordset
   Dim CurDB As Database
   
   Set CurDB = CurrentDb
     
   Set rst = New ADODB.Recordset
   rst.CursorType = adOpenDynamic
   rst.LockType = adLockOptimistic
   
   rst.Open "SELECT * FROM PropertyWorkOrder_JunctionTable", CurrentProject.Connection, , , adCmdTable
   'rst.Open "SELECT * FROM PropertyWorkOrder_JunctionTable WHERE [WorkOrderID] = '" & _
   '          Forms!WO_Mainform!WorkOrderID & "'" & " AND [PropertyID] = '" & _
   '          Forms!RemovePropertyForm!PropertyID & "'", CurrentProject.Connection, , , adCmdTable
   'With rst
        '.Delete
   'End With
   rst.Close

   DoCmd.Close acForm, "RemovePropertyForm", acSaveYes
     
RemoveFromWOBtn_Click_Exit:
    Exit Sub
RemoveFromWOBtn_Click_Err:
    MsgBox Err.Description
    Resume RemoveFromWOBtn_Click_Exit

End Sub

0
Comment
Question by:dj88
2 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 19620619
rst.Open "SELECT * FROM PropertyWorkOrder_JunctionTable", CurrentProject.Connection

Offhand this looks fine, assuming you didn't mis-spell the table name.  You can lose the rest after the CurrentProject.Connection
0
 
LVL 10

Author Comment

by:dj88
ID: 19620687
Removing everything past the CurrentProject.Connection worked like a charm.  Since the SQL error kept coming up, I didn't think to check any of the other "Open" arguments.  Thanks.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question