Search code examples
ms-accessvb6

3 Tables in 1 using Visual Basic

So im using a Visual Basic with MS Access and trying to create a form, that i will be able to link the specific tables in to 1 Table, apparently i had an Error under this line

Private Sub Form_Load()
Set rsS = New ADODB.Recordset
Set DataGrid1.DataSource = rsS
LoadRs rsS, "SELECT tblfinal.SID AS SID, tblfinal.SN AS SN, tblmidterm.Course AS Course, 
tblprelim.PrelimGrade AS PrelimGrade, tblmidterm.MidtermGrade AS MidtermGrade, 
tblfinal.FInalGrade AS FinalGrade"
FROM ((tblfinal
INNER JOIN tblprelim ON tblfinal.SID = tblprelim.SID)
INNER JOIN tblmidterm ON tblfinal.SID = tblmidterm.SID);"


End Sub

And as mentioned above, i'm getting a Compile Error: Syntax Error at "FROM ((tblfinal and the rest below

I have the Relationships setup on my DB as well

Relationship box

Solution

  • You have a broken SQL statement because you tried to split it over several lines.

    Private Sub Form_Load()
    Set rsS = New ADODB.Recordset
    Set DataGrid1.DataSource = rsS
    LoadRs rsS, "SELECT tblfinal.SID AS SID, tblfinal.SN AS SN, tblmidterm.Course AS Course, tblprelim.PrelimGrade AS PrelimGrade, tblmidterm.MidtermGrade AS MidtermGrade, tblfinal.FInalGrade AS FinalGrade FROM ((tblfinal INNER JOIN tblprelim ON tblfinal.SID = tblprelim.SID) INNER JOIN tblmidterm ON tblfinal.SID = tblmidterm.SID);"
End Sub

    Or you could try with line continuation:

    Private Sub Form_Load()
    Set rsS = New ADODB.Recordset
    Set DataGrid1.DataSource = rsS
    LoadRs rsS, "SELECT tblfinal.SID AS SID, tblfinal.SN AS SN, " & _
        "tblmidterm.Course AS Course, tblprelim.PrelimGrade AS PrelimGrade,  " & _
        "tblmidterm.MidtermGrade AS MidtermGrade, tblfinal.FInalGrade AS FinalGrade " & _
        "FROM ((tblfinal " & _
        "INNER JOIN tblprelim ON tblfinal.SID = tblprelim.SID) " & _
        "INNER JOIN tblmidterm ON tblfinal.SID = tblmidterm.SID);"
End Sub