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?
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

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
Cloud Class® Course: 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.

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.
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.