SQL Query works in MS-Access but cannot implement it in vb.net form

Question

The block of code immediately following (given to me by a stackoverflow solver) works perfectly in MS-Access. I'm trying to convert it to work in a vb.net form accessing the very same MS-Access database. I get an error and cannot see my mistake. Are there any vb.net coders that can see what I'm doing wrong. The first block of code works in MS-Access and is the code I'm trying to convert. And the second block of code is my conversion attempt.

SELECT at.animalID, amt.milestoneType
FROM
animals_Table at
LEFT JOIN
(
    SELECT animalID, milestoneType
    FROM animalMilestones_Table
    WHERE milestoneType = 'Intake'
) amt
    ON at.animalID = amt.animalID

Now, my conversion attempt:

dim selectAnimal as string
selectAnimal = "SELECT at.animalID, amt.milestoneType" & _
               " FROM animals_Table at" & _
               " LEFT JOIN" & _
               " (" & _ 
               " SELECT animalID, milestoneType" & _
               " FROM animalMilestones_Table" & _
               " WHERE milestoneType = '" & "Intake" & "'" & _
               " ) amt" & _
               " ON at.animalID = amt.animalID"

The error code I get is !ErrorInfo.GetDescription failed with E_FAIL(0x80004005)

Gord Thompson · Accepted Answer · 2014-07-05 14:11:35Z

2

It appears that ACE.OLEDB doesn't like at as a table alias. Try this instead

Dim selectAnimal As String
selectAnimal = "SELECT atbl.animalID, amtbl.milestoneType" & _
               " FROM animals_Table atbl" & _
               " LEFT JOIN" & _
               " (" & _
               " SELECT animalID, milestoneType" & _
               " FROM animalMilestones_Table" & _
               " WHERE milestoneType = '" & "Intake" & "'" & _
               " ) AS amtbl" & _
               " ON atbl.animalID = amtbl.animalID"

answered Jul 5, 2014 at 14:11

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

1 Comment

user2076963 Over a year ago

Yay!! You did it again Gord. Works like a champ. You're a genius. Thank you.

Collectives™ on Stack Overflow

SQL Query works in MS-Access but cannot implement it in vb.net form

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related