0

I am making Excel so that I can use it like a DB. (When I press Select button, My Select statement is executed.)

enter image description here

However, an error occurs when I use a double join statement. Please review my code and tell me about my problem.

---------------- My Visual Basic code -----------------------------------

Sub mySelect()
    Dim myQuery As String
     
    Dim conn As New ADODB.Connection
    Dim mrs As New ADODB.Recordset
    
    Dim DBPath As String, sconnect As String
    
    'Delete all previously searched
    ActiveSheet.Range("B9").CurrentRegion.ClearContents
    
    DBPath = ThisWorkbook.FullName
     
    'HDR=Yes --> First row of data sheet is used as field name
    sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
     
    conn.Open sconnect
    myQuery = Cells(3, 2).Value
    mrs.Open myQuery, conn
    
    'print to screen
    ActiveSheet.Range("B9").CopyFromRecordset mrs
    
    'close DB recordset
    mrs.Close
    'close DB Connection
    conn.Close
End Sub

------------------ my Query ---------------------------------

SELECT req.IssueNo, req.RequestMemo FROM [IssueBasicInfo$] info

Left Join [IssueRequest$] req ON info.IssueNo = req.IssueNo

Left Join [IssueFile$] file On info.IssueNo = file.IssueNo

------------------ DB Structure -----------------------------

IssueBasicInfo

enter image description here

IssueFile

enter image description here

IssueRequest

enter image description here

2

1 Answer 1

0

This query will produce duplicate rows for req, and this is the problem. You're right that if you remove second join everything is OK. Try to change sql select to:

SELECT DISTINCT req.IssueNo, req.RequestMemo FROM [IssueBasicInfo$] info

Left Join [IssueRequest$] req ON info.IssueNo = req.IssueNo

Left Join [IssueFile$] file On info.IssueNo = file.IssueNo

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.