Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Wigan, Lancashire, England
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Statement (VB6 / Access 2000)

    The following works on Windows 2000 machine, on Windows 95 but fails on WIndows ME

    SQLText = "SELECT Format([Route Date],'mmm-yy') AS [MonthsDate], Format(Sum(Routes.Distance), '#.0') AS MilesWalked, Format (Sum(Routes.Climb), '#,##0') AS FeetClimbed FROM Routes WHERE (((Routes.[Route Date])>#12/31/1998#)) GROUP BY Format([Route Date],'mmm-yy')ORDER BY Sum(Routes.Distance) DESC , Sum(Routes.Climb) DESC"

    It populates a flexgrid control, when it works, but on the ME machine I get a Run Time error "ODBC call failed". If I simply select Route Date with no formating the ODBC call succeed.

    All three machines run the same versions of access 2000, ME & 95 appear to have the same version of Access ODBC driver.

    Any ideas would be appreciated

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL Statement (VB6 / Access 2000)

    I think the issue may be related more to the version of the MDAC on the ME PC. Is you application Access based or VB based? I'm presuming it is VB based, or you would have posted your question in the Access forum, but figured I should ask. Also what backend are you going against - SQL Server, Oracle, FoxPro, ????

    "ODBC call failed messages" are a pain to troubleshoot, as the ODBC layer hides the real problem. There is some involved code in a KB article that MS provided to give a better handle on the problem, and you can do some detailed debugging (which is very tedious).
    Wendell

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Location
    Wigan, Lancashire, England
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (VB6 / Access 2000)

    Now using MDAC 2.7, VB now using SP 5, recompiled and reinstalled makes no difference. Access database being interrogated by VB Application. SQL statement pasted into SQL window in a query works as expected. Guess I better have a look for the KB article.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL Statement (VB6 / Access 2000)

    MDAC 2.7 is a pretty logical suspect, although I am surprised at the fact that VB is using an ODBC driver to talk to a Jet 4.0 database. I was under the impression that you could connect directly to a Jet MDB file from a VB project - not that I ever do it, as we stick to Access.
    Wendell

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SQL Statement (VB6 / Access 2000)

    For ADO, I think you have to choose between the Jet "native" OLE DB provider and the generic ODBC provider. Possibly they work differently under the covers?

    ConnectionObject.Provider = "Microsoft.Jet.OLEDB.4.0"

    You also can use this in the Open command:

    ConnectionObject.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
    "DATA SOURCE=C:_dataNWind.mdb;" & _
    "USER ID=admin;PASSWORD=;"

    ConnectionObject.Provider = "MSDASQL"

    Because MSDASQL is the default if not stated, the following statements have the identical result:

    ConnectionObject.Open "PROVIDER=MSDASQL;" & _
    "DRIVER={Microsoft Access Driver (*.mdb)};" & _
    "DBQ=C:_dataNWind.mdb;" & _
    "UID=sa;PWD=;"

    ConnectionObject.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & _
    "DBQ=C:_dataNWind.mdb;" & _
    "UID=sa;PWD=;"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •