none
INNER JOIN in Recordset RRS feed

  • Question

  • Hi, I want to generate excel file according to SQL query, but i got runtime error 2147217900 that JOIN is not supproted. Here is my code: Thank you.

    Dim rs As New Recordset
    Dim C As Connection
    Set C = CurrentProject.Connection
    rs.Open "SELECT TabulkaReprofilaciKotouce.GroupNum, RodnyListKotouce.[Seriové číslo], RodnyListKotouce.[Číslo kotouče], TabulkaReprofilaciKotouce.[Průměr beta], TabulkaReprofilaciKotouce.[Tloušťka kotouče], TabulkaReprofilaciKotouce.[Provozující linka]" & _
    "FROM CislaSkupinReprofilaci INNER JOIN (RodnyListKotouce INNER JOIN TabulkaReprofilaciKotouce ON (RodnyListKotouce.[Číslo kotouče] = TabulkaReprofilaciKotouce.[Číslo kotouče]) AND (RodnyListKotouce.[Seriové číslo] = TabulkaReprofilaciKotouce.[Seriové číslo])) ON CislaSkupinReprofilaci.GroupNo = TabulkaReprofilaciKotouce.GroupNum" & _
    "WHERE (((RodnyListKotouce.AttrGrouped)=True) AND ((TabulkaReprofilaciKotouce.Active)=True) AND ((CislaSkupinReprofilaci.Selected)=True));", C
    
    'Dim ExApp As New Excel.Application 'x
    Dim ExApp As Excel.Application 'x
    Dim WBk As Workbook 'w
    Dim WSh As Worksheet 's
    Dim Rng As Range 'r
    Dim Addr As String 'd
    
    Dim DatumCas As String
    DatumCas = Format(Now, "yyyy-mm-dd_hh-mm")
    
    Addr = "C:\Users\Tomas\Documents\database\etapa2\"
    Set WBk = workbooks.Open(Addr & "excel\tabulka.xlsx")
    
    Set WSh = WBk.Sheets("List1")
    Set Rng = WSh.Range("A2")
    
    Rng.CopyFromRecordset rs
    
    WSh.Columns("A:F").EntireColumn.AutoFit
    WSh.Columns("A:F").Font.Size = 10
    
    rs.Close
    Set rs = Nothing
    
    WBk.SaveAs Addr & "excel\BetaSeznam_" & DatumCas & ".xlsx", , , , False
    WBk.Close
    ExApp.Quit
    
    Set Rng = Nothing
    Set WSh = Nothing
    Set WBk = Nothing
    Set ExApp = Nothing
    

    Tuesday, November 14, 2017 8:11 PM

Answers

  • rs.Open "SELECT TabulkaReprofilaciKotouce.GroupNum, RodnyListKotouce.[Seriové číslo], RodnyListKotouce.[Číslo kotouče], TabulkaReprofilaciKotouce.[Průměr beta], TabulkaReprofilaciKotouce.[Tloušťka kotouče], TabulkaReprofilaciKotouce.[Provozující linka]" & _
    "FROM CislaSkupinReprofilaci INNER JOIN (RodnyListKotouce INNER JOIN TabulkaReprofilaciKotouce ON (RodnyListKotouce.[Číslo kotouče] = TabulkaReprofilaciKotouce.[Číslo kotouče]) AND (RodnyListKotouce.[Seriové číslo] = TabulkaReprofilaciKotouce.[Seriové číslo])) ON CislaSkupinReprofilaci.GroupNo = TabulkaReprofilaciKotouce.GroupNum" & _
    "WHERE (((RodnyListKotouce.AttrGrouped)=True) AND ((TabulkaReprofilaciKotouce.Active)=True) AND ((CislaSkupinReprofilaci.Selected)=True));", C
    


    It appears to me that you are missing some essential spaces between the clauses of your SQL statement.  Try this modification:

    rs.Open "SELECT TabulkaReprofilaciKotouce.GroupNum, RodnyListKotouce.[Seriové číslo], RodnyListKotouce.[Číslo kotouče], TabulkaReprofilaciKotouce.[Průměr beta], TabulkaReprofilaciKotouce.[Tloušťka kotouče], TabulkaReprofilaciKotouce.[Provozující linka]" & _
    " FROM CislaSkupinReprofilaci INNER JOIN (RodnyListKotouce INNER JOIN TabulkaReprofilaciKotouce ON (RodnyListKotouce.[Číslo kotouče] = TabulkaReprofilaciKotouce.[Číslo kotouče]) AND (RodnyListKotouce.[Seriové číslo] = TabulkaReprofilaciKotouce.[Seriové číslo])) ON CislaSkupinReprofilaci.GroupNo = TabulkaReprofilaciKotouce.GroupNum" & _
    " WHERE (((RodnyListKotouce.AttrGrouped)=True) AND ((TabulkaReprofilaciKotouce.Active)=True) AND ((CislaSkupinReprofilaci.Selected)=True));", C



    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, November 15, 2017 5:00 PM

All replies

  • Hi,

    For troubleshooting, you could copy and paste your SQL in the query designer and make sure it runs first. If it doesn't, you can easily fix the problem from the designer rather than in VBA. Once it's fixed, you can paste the working SQL back into your code.

    Just my 2 cents...

    Tuesday, November 14, 2017 9:03 PM
  • Hi ,

    I find one similar kind of issue, In which other user getting same kind of error.

    you can try to refer the link below and try to make changes in your query.

    Error: “Join expression not supported”

    it may help you to solve your issue.

    let us know about your testing results.

    so that we can try to provide further suggestions , if needed.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 15, 2017 5:32 AM
    Moderator
  • Hi, I made SQL from query designer than I paste it to vba. I am sure, that SQL works. Previous SQL was with one INNER JOIN, than I add second INNER JOIN and it stopped working.
    Wednesday, November 15, 2017 9:09 AM
  • rs.Open "SELECT TabulkaReprofilaciKotouce.GroupNum, RodnyListKotouce.[Seriové číslo], RodnyListKotouce.[Číslo kotouče], TabulkaReprofilaciKotouce.[Průměr beta], TabulkaReprofilaciKotouce.[Tloušťka kotouče], TabulkaReprofilaciKotouce.[Provozující linka]" & _
    "FROM CislaSkupinReprofilaci INNER JOIN (RodnyListKotouce INNER JOIN TabulkaReprofilaciKotouce ON (RodnyListKotouce.[Číslo kotouče] = TabulkaReprofilaciKotouce.[Číslo kotouče]) AND (RodnyListKotouce.[Seriové číslo] = TabulkaReprofilaciKotouce.[Seriové číslo])) ON CislaSkupinReprofilaci.GroupNo = TabulkaReprofilaciKotouce.GroupNum" & _
    "WHERE (((RodnyListKotouce.AttrGrouped)=True) AND ((TabulkaReprofilaciKotouce.Active)=True) AND ((CislaSkupinReprofilaci.Selected)=True));", C
    


    It appears to me that you are missing some essential spaces between the clauses of your SQL statement.  Try this modification:

    rs.Open "SELECT TabulkaReprofilaciKotouce.GroupNum, RodnyListKotouce.[Seriové číslo], RodnyListKotouce.[Číslo kotouče], TabulkaReprofilaciKotouce.[Průměr beta], TabulkaReprofilaciKotouce.[Tloušťka kotouče], TabulkaReprofilaciKotouce.[Provozující linka]" & _
    " FROM CislaSkupinReprofilaci INNER JOIN (RodnyListKotouce INNER JOIN TabulkaReprofilaciKotouce ON (RodnyListKotouce.[Číslo kotouče] = TabulkaReprofilaciKotouce.[Číslo kotouče]) AND (RodnyListKotouce.[Seriové číslo] = TabulkaReprofilaciKotouce.[Seriové číslo])) ON CislaSkupinReprofilaci.GroupNo = TabulkaReprofilaciKotouce.GroupNum" & _
    " WHERE (((RodnyListKotouce.AttrGrouped)=True) AND ((TabulkaReprofilaciKotouce.Active)=True) AND ((CislaSkupinReprofilaci.Selected)=True));", C



    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, November 15, 2017 5:00 PM
  • Yesss! Thats it! Now it works, thank you!
    Wednesday, November 15, 2017 6:42 PM
  • Hi Seeter,

    I can see that your issue is solved now but you did not mark the answer.

    as a result , this thread is still open and will remain open until you mark the answer.

    I suggest you to mark the suggestion given by Dirk Goldgar as an answer.

    it will help us to close this thread and it will help other community members in future who will face same kind of issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 16, 2017 8:08 AM
    Moderator