Access 2003 error from VB6

[OneTJN]

I use access to build my SQL statements. However, I just ran into a situation were the statement runs fine in access but gives me an error in VB6. The error message paraphrased is 'Microsoft Jet Engine can't find the table'. The error number is 80040e37. I have tried solving this problem in different ways but nogo. The error always is triggered by whichever table is in the FROM statemtent. In the example below it was the SlillLevels table

How do I know the statement is good...

I build the SQL statement in the variable SQL and run it in vb6 I get the error. I then to a ?SQL in the immiediate window, I do a copy of the print. I go to ACCESS 2003 go to build query goto sql view do a paste change to database view and the statement executes fine.

Here is the statement as copied for the immediate window.

?sql
SELECT MEN.ExerciseKey, SEQ.OrgPracTitle, MEN.WhatVaries, SEP.Angle, SEP.ExerChildKey, SkillLevels.SkillLevel, PracticeType.PracticeType, SEP.NormalizedDifficulty, SEP.QODistance, SEP.OtoPocketDistance FROM SkillLevels INNER JOIN (PracticeType INNER JOIN (MemberSequences AS SEQ INNER JOIN (MemberExerciseNames AS MEN INNER JOIN SpecificExerciseParms AS SEP ON MEN.ExerciseKey = SEP.ExerciseKey) ON SEQ.BookKey = MEN.BookKey) ON PracticeType.PracticeTypeKey = MEN.PracticeTypeKey) ON SkillLevels.SkillSequence = MEN.SkillLevel WHERE ((TableSize = 1) AND (OBallLocKey = 'bB'));

 

[Golom]

Just a wild guess but Access processes it's own SQL while in VB you are relying on DAO or ADO to interpret the SQL statement. Try unwrapping your FROM clause so that the inner-most join (in your statement) is the first one processed by the parser.

SELECT MEN.ExerciseKey, SEQ.OrgPracTitle, MEN.WhatVaries, SEP.Angle, SEP.ExerChildKey, S.SkillLevel, P.PracticeType, SEP.NormalizedDifficulty, SEP.QODistance, SEP.OtoPocketDistance 

FROM ((((MemberExerciseNames AS MEN INNER JOIN SpecificExerciseParms AS SEP 
      ON MEN.ExerciseKey = SEP.ExerciseKey)
      INNER JOIN 
      MemberSequences AS SEQ ON SEQ.BookKey = MEN.BookKey)
      INNER JOIN
      PracticeType As P ON P.PracticeTypeKey = MEN.PracticeTypeKey)
      INNER JOIN
      SkillLevels As S ON S.SkillSequence = MEN.SkillLevel)

 

[SBerthold]

OneTJN, I would really double check that your db connection is pointing to the right db and the right db version (all tables and fields are present).

The query looks find with respect to the error number, if all tables are indeed present in the db that the connection in the VB app is pointing to.