Is the reason for this error that I have a variable as a join?

I am getting a general ODBC error on running this with a horse name in cell A1.  Is that because I am trying to join on a variable - asking the impossible?
Dim sName As String
    Dim sSql As String
    sName = Sheets("Sheet1").Range("A1").Value
    sName = Replace(sName, "'", "''")
  sSql = _
   "SELECT DISTINCT runners.`horse name`, RPW.NumOfRuns   "
sSql = sSql & _
   "  FROM runners LEFT JOIN VDW ON runners.`horse name` = RPW.Expr1     "
sSql = sSql & _
   " WHERE runners.`horse name` = '" & sName & "'                 "
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\Robin\My Documents\\Newflatdatabase.mdb;DefaultDir=C:\Documents and Settings\Robin\My Documents;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        )), Destination:=Range("E16"))
        .CommandText = sSql
    End With

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roger BaklundCommented:
You are joining the table VDW, bust select a column from RPW (non-existant). Change RPW to VDW.
robinbernettAuthor Commented:
Thank you, you are right.  I have changed it, but am getting the same error
Roger BaklundCommented:
Try removing "DISTINCT runners.`horse name`," from the query, so that it becomes "SELECT VDW.NumOfRuns ". You allready know the name of the horse, it is in A1...

If you still get an error, post the error message.
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

robinbernettAuthor Commented:
ok, have removed "DISTINCT runners.`horse name`." from the query.

The error message is
Run-time error '1004'
General ODBC error
robinbernettAuthor Commented:
I think I need to explain:
I want to retrieve all fields from query VDW in this sql routine using the parameter runners.`horse name` in cell A1
This is my first attempt to combine in a query requests from both a table (runners) and a query (VDW) in sql.  I could well be making a fundamental error here.  I attach the sql for the query VDW in the attached code snippet.   (The table runners  is As T2)

Using the sql for the VDW query I have now got this to work:
sSql = _
   "SELECT DISTINCT T2.[horse name] AS Expr1, (SELECT COUNT(*) FROM runners T2a WHERE T2.`horse name`=T2a.`horse name`) AS NumOfRuns  "
sSql = sSql & _
   "  FROM runners AS T2    "
sSql = sSql & _
   " WHERE T2.`horse name` = '" & sName & "'                 "

however I do not know how to adapt it to include the other fields from the query VDW that I want returned also, ie NumOfWins and TotalFirstPrizeMoney
SELECT DISTINCT T2.[horse name] AS Expr1, (SELECT COUNT(*) FROM runners T2a WHERE T2.`horse name`=T2a.`horse name`) AS NumOfRuns, (SELECT COUNT(*) FROM  runners T2a WHERE T2.`horse name`=T2a.`horse name` AND T2a.`finish posn`="1") AS NumOfWins, (SELECT Sum(T3.`prize 1`) FROM races T3 INNER JOIN runners T2a ON T3.`race number` = T2a.`race number` WHERE T2a.`finish posn`="1" AND T2a.`horse name`=T2.`horse name` GROUP BY T2a.`horse name`) AS TotalFirstPrizeMoney
FROM runners AS T2;

Open in new window

robinbernettAuthor Commented:
I've worked it out at last - learning is a steep curve at times. I just needed to use the VDW query sql and the proper use of finish posn`='" & 1 & "'.
Points on their way.
Thank you so much for sticking with me

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.