Results 1 to 9 of 9

Thread: DAO vs ADO

  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: DAO vs ADO

    How do I do the following using ADO instead of DAO?

    Code:
    Function GetDefault(strProd, strNutr) As String
      Dim strSql As String, rsRaw As dao.Recordset
      strSql = "SELECT RAWS.BASEMAT FROM (Lawn_Bom INNER JOIN RAWS ON Lawn_Bom.Raw   = RAWS.RAWMAT) " _
          & "INNER JOIN RAW_FAMILY ON RAWS.BASEMAT = RAW_FAMILY.BASEMAT " _
          & "WHERE Lawn_Bom.Product = '" & strProd & "' AND RAW_FAMILY.SOURCE = '" & strNutr & "'"
      Set rsRaw = CurrentDb.OpenRecordset(strSql)
      If Not rsRaw.BOF And Not rsRaw.EOF Then
        GetDefault = rsRaw!basemat
      Else
        GetDefault = ""
      End If
      rsRaw.Close
      Set rsRaw = Nothing
    End Function
    2 of the tables in strSql (RAWS and RAW_FAMILY) are part of my Access db. The third table,
    LAWN_BOM, is a linked SQL Server table. I'm so used to using ADO, I prefer to use it for everything - but I can't figure out how what my connection string should look like if I'm pulling from the "CurrentDB".
    Inspiration Through Fermentation

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This type of thing:

    rst.ActiveConnection = CurrentProject.Connection
    Paul

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Works perfectly! Thanks
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Maybe "perfectly" was too strong of a word...

    Not sure what the error message

    Run-Time error '-2147418113
    Catastrophic Failure

    means, but I'm sure it's not good
    Inspiration Through Fermentation

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Never seen that one before. Do you get it every time? What line causes it?
    Paul

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    It was stopping on:

    rsRaw.Open strSql, CurrentProject.Connection

    But I think it was really a stack overflow error. I found a function calling itself.

    That was a laugh out loud error message, though.
    Inspiration Through Fermentation

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That it was!
    Paul

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's typical ADO code I write using your example:

    Function GetDefault(strProd, strNutr) As String
    Dim strSql As String
    Dim rsRaw As dao.Recordset
    strSql = "SELECT RAWS.BASEMAT FROM (Lawn_Bom INNER JOIN RAWS ON Lawn_Bom.Raw = RAWS.RAWMAT) " _
    & "INNER JOIN RAW_FAMILY ON RAWS.BASEMAT = RAW_FAMILY.BASEMAT " _
    & "WHERE Lawn_Bom.Product = '" & strProd & "' AND RAW_FAMILY.SOURCE = '" & strNutr & "'"

    ......
    Set rsRaw = new adodb.recordset

    'Updatable recordset (if the strSQL is updatable) - cannot return recordcount

    rsRaw.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic

    'non-updatable but can return recordcount (ie. rsRaw.Recordcount)

    rsRaw.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly

    ....
    If Not rsRaw.BOF And Not rsRaw.EOF Then
    GetDefault = rsRaw!basemat
    Else
    GetDefault = ""
    End If
    rsRaw.Close
    Set rsRaw = Nothing
    End Function
    Last edited by pkstormy; 11-06-09 at 23:49.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by RedNeckGeek View Post
    Run-Time error '-2147418113
    Catastrophic Failure
    Mwahahahahahahahahahaaaaaaaaaaaaaaaaaaaaaaa!!!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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