Results 1 to 14 of 14
  1. #1
    CBALL2014 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2014
    Posts
    7

    Exclamation Run-time error '3131' Syntax error in FROM clause: PLEASE HELP!!


    I have been pulling my hair out trying to figure out why I'm getting this error from my Access application. I have 2 linked SQL Server tables (dbo.UDS_MAINTENANCE, dbo.ESTATE_MASTER) and a I'm trying to join a linked excel [UEP_SUMMARY$] to one of them in an INSERT INTO SELECT statement. My vba code is below:

    GlobalPED = objCMD.Parameters("PED").Value

    Dim InsUEPSQL As String




    InsUEPSQL = "INSERT INTO dbo.UDS_MAINTENANCE(PED,ENTITY,ESTATE_ID,NAIC,ESTA TE_NAME,UDS_STATE,UDS_TYPE) " & _
    "SELECT DISTINCT UEP.COMPANY_IGA AS ESTATE_ID, " & _
    "'" & GlobalPED & "' AS PED, " & _
    "UEP.FUND AS ENTITY, " & _
    "EM.NAIC_GROUP AS NAIC, " & _
    "EM.ESTATE_NAME AS ESTATE_NAME, " & _
    "EM.UDS_STATE AS STATE, " & _
    "'UEP' AS UDS_TYPE " & _
    "FROM [UEP_SUMMARY$] UEP " & _
    "JOIN dbo.ESTATE_MASTER EM ON UEP.COMPANY_IGA = EM.FIGA_ID " & _
    "WHERE UEP.FUND = 'FIGA' "

    DoCmd.RunSQL InsUEPSQL

    When executed the debugger shows the error occurring at the DoCmd.RundSQL line. I don't see anything wrong with the SQL statement and so I can't figure out what is causing the issue. Any help is greatly appreciated.

    Thanks!!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Here's a commonly used method to debug the SQL:

    BaldyWeb-Immediate window
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    CBALL2014 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2014
    Posts
    7
    Thanks pbaldy! When I ran it again the Debug.Print gave me the following statement:

    INSERT INTO dbo.UDS_MAINTENANCE(PED,ENTITY,ESTATE_ID,NAIC,ESTA TE_NAME,UDS_STATE,UDS_TYPE)
    SELECT DISTINCT UEP.COMPANY_IGA AS ESTATE_ID, '4/30/2014' AS PED, UEP.FUND AS ENTITY, EM.NAIC_GROUP AS NAIC, EM.ESTATE_NAME AS ESTATE_NAME, EM.UDS_STATE AS STATE, 'UEP' AS UDS_TYPE
    FROM UEP_SUMMARY$ UEP
    JOIN dbo.ESTATE_MASTER EM ON UEP.COMPANY_IGA = EM.FIGA_ID
    WHERE UEP.FUND = 'FIGA'

    I still don't see anything wrong with it....could it be the linked excel??

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters the Excel table with the inadvisable symbol probably needs to be bracketed. I think the fields in the SELECT have to be in the same order as in the INSERT INTO. If PED is a date/time field, the ' need to be #. Also, not sure you can leave out the INNER/RIGHT/LEFT portion of the JOIN. You can try running in a blank query as mentioned in the link, where you might get a better error message.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    CBALL2014 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2014
    Posts
    7
    Okay now I'm getting this error which is even stranger:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	16 
Size:	31.8 KB 
ID:	16616
    The SQL statement returned is as follows:
    INSERT INTO dbo.UDS_MAINTENANCE(PED,ENTITY,ESTATE_ID,NAIC,ESTA TE_NAME,UDS_STATE,UDS_TYPE)
    SELECT DISTINCT UEP.COMPANY_IGA AS ESTATE_ID, '4/30/2014' AS PED, UEP.FUND AS ENTITY, EM.NAIC_GROUP AS NAIC, EM.ESTATE_NAME AS ESTATE_NAME, EM.UDS_STATE AS STATE, 'UEP' AS UDS_TYPE
    FROM [UEP_SUMMARY$] UEP
    INNER JOIN dbo.ESTATE_MASTER EM ON UEP.COMPANY_IGA = EM.FIGA_ID
    WHERE UEP.FUND = 'FIGA'

    The excel is linked just like the other tables and so I don't why it's saying it can't find it.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's the name of the linked table? Are you sure it isn't a space rather than an underscore?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    When you linked the Excel file, did you leave the name of the linked table as [UEP_SUMMARY$] in Access? If not, then that's the problem - you have to use the MS Access name. If you left it as it was, then you could try renaming it (in Access) to remove the $, though I doubt that is the problem.

    John

  8. #8
    CBALL2014 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2014
    Posts
    7
    Yes that's the name of it...I figured out what the issue was regarding that error...the way I was referencing the linked excel was incorrect. The brackets and $ were not necessary. Now when I run it I'm getting a type mismatch error in criteria expression error on the following code:

    InsUEPSQL = "INSERT INTO dbo_UDS_MAINTENANCE(PED,ENTITY,ESTATE_ID,NAIC,ESTA TE_NAME,UDS_STATE,UDS_TYPE) " & _
    "SELECT DISTINCT UEP.COMPANY_IGA AS ESTATE_ID, " & _
    "'" & GlobalPED & "' AS PED, " & _
    "UEP.FUND AS ENTITY, " & _
    "EM.NAIC_GROUP AS NAIC, " & _
    "EM.ESTATE_NAME AS ESTATE_NAME, " & _
    "EM.UDS_STATE AS STATE, " & _
    "'UEP' AS UDS_TYPE " & _
    "FROM UEP_SUMMARY UEP " & _
    "INNER JOIN dbo_ESTATE_MASTER EM ON UEP.COMPANY_IGA = EM.FIGA_ID " & _
    "WHERE UEP.FUND = 'FIGA' "

    I don't see why I'm getting that error when the datatypes are the same.

  9. #9
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    The error message suggests (as do your field names) that the data types of UEP.COMPANY_IGA and EM.FIGA_ID are different (maybe you used a wrong field name?), or that UEP.FUND is not Text type (I would suspect the former).

  10. #10
    CBALL2014 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2014
    Posts
    7
    No... the table is named correctly in Access...I think I figured it out...the two fields I'm joining on (UEP.COMPANY_IGA & EM.FIGA_ID) are of type Number in Access, but one is set to double and the other is set to long integer.

  11. #11
    CBALL2014 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2014
    Posts
    7
    Is there a way for me to make them match using vba??

  12. #12
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Just use the Int function on the one that is double, e.g. int(UEP.COMPANY_IGA), which truncates the value to the integer portions (integer & long should not give that error, but...). Hopefully you don't have any values in the "double" fields that have a non-zero secimal part!

    John

  13. #13
    CBALL2014 is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2014
    Posts
    7
    No those values are all 3 digits in that column...still the same error when it executes the statement....this is driving me crazy lol

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Want to provide the Excel file for analysis and testing?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Syntax error on VB Select Where Clause
    By FrustratedAlso in forum Programming
    Replies: 3
    Last Post: 01-30-2014, 02:57 AM
  2. Replies: 3
    Last Post: 01-23-2014, 07:49 AM
  3. syntax error in from clause
    By jskamm in forum Access
    Replies: 2
    Last Post: 09-18-2012, 09:13 AM
  4. Syntax Error in FROM clause
    By cjohnston in forum Queries
    Replies: 2
    Last Post: 02-15-2012, 07:33 AM
  5. Incomplete Syntax Clause (syntax error)
    By ajetrumpet in forum Programming
    Replies: 4
    Last Post: 09-11-2010, 10:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums