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

Posted on 2007-08-02
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
   'End With

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

End Sub

Question by:dj88
    LVL 65

    Accepted Solution

    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
    LVL 10

    Author Comment

    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.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now