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

Posted on 2008-11-13
Last Modified: 2012-05-05
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

Question by:robinbernett
    LVL 39

    Expert Comment

    by:Roger Baklund
    You are joining the table VDW, bust select a column from RPW (non-existant). Change RPW to VDW.

    Author Comment

    Thank you, you are right.  I have changed it, but am getting the same error
    LVL 39

    Assisted Solution

    by:Roger Baklund
    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.

    Author Comment

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

    The error message is
    Run-time error '1004'
    General ODBC error

    Author Comment

    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


    Accepted Solution

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Title # Comments Views Activity
    Parameterised queries 4 54
    Dsum and Max 10 28
    Oracle SQL Select Statement 19 40
    Access Database - Manually changing xid value 7 29
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now