none
ADODB, Automation Error / Unspecified Error RRS feed

  • Question

  • I have an error that I can not make sense of. I open a database connection and recordset from table1 and then I close the recordset and reopen a new recordset from table2. The code works for table1 but fails with an automation error when i try to open table2. I have checked spell errors to see if my table names match and i did not find anything wrong.

    Private Function Fn_GetFastenersStockAlerts(ByVal strPartNumber As String, ByVal dbConn As ADODB.Connection) As FastenerClass
    
        'Dim dbConn As ADODB.Connection
        Dim dbRs As ADODB.Recordset
        Dim dbRs2 As ADODB.Recordset
        Dim dbQuery As String
        Dim dbPath As String
        Dim strFasteNerSize As String
    
        Set Fn_GetFastenersStockAlerts = New FastenerClass
    
        Set dbRs = New ADODB.Recordset
        dbQuery = "SELECT * FROM STOCKFASTENERS WHERE PARTNUMBER = '" & strPartNumber & "';"
        dbRs.Open dbQuery, dbConn
    
        If dbRs.EOF <> True And dbRs.BOF <> True Then
            Fn_GetFastenersStockAlerts.lnLength = dbRs!Length
            Fn_GetFastenersStockAlerts.lnStockQty = dbRs!QTYONHAND
        Else
            Fn_GetFastenersStockAlerts.lnLength = 0
            Fn_GetFastenersStockAlerts.lnStockQty = 0
        End If
        dbRs.Close
        Set dbRs = Nothing
    
        strFasteNerSize = Fn_GetFastenerSize(strPartNumber)
    
        Set dbRs2 = New ADODB.Recordset
        dbQuery = "SELECT MAXLENGTH FROM STOCKFASTNERMAXLENGTH WHERE SIZE = '" & strFasteNerSize & "';"
        dbRs2.Open dbQuery, dbConn
    
        If dbRs2.EOF <> True And dbRs2.BOF <> True Then
            Fn_GetFastenersStockAlerts.lnMaxLength = dbRs!Length
        Else
            Fn_GetFastenersStockAlerts.lnMaxLength = 0
        End If
        dbRs2.Close
    
    End Function

    It is successful on dbRs.Open I get the records that I want out of it. It errors out on dbRs2.Open with Automation error and Unspecified Error.

    Any help is appreciated.

    Thank you


                                      
    Monday, October 10, 2016 7:39 PM

Answers

  • STOCKFASTNERMAXLENGTH it is a table. Where Size is my primary key. I got a dent in my head from banging on the wall but still no solution. I am just unable to understand why the same line of code works for a different table but not this one.

    It's not obvious to me what the problem is, either.  I would suggest you try two approaches to debugging the problem.  First, try bracketing and fully qualifying fielding names (especially SIZE), in case there's a conflict with a reserved word:

        dbQuery = "SELECT SFL.[MAXLENGTH] FROM STOCKFASTNERMAXLENGTH SFL WHERE SFL.[SIZE] = '" & strFasteNerSize & "';"

    If that doesn't work, you might try running the query using DAO instead of ADO, like this:

         Dim rs2 As DAO.Recordset

         Set rs2 = CurrentDb.OpenRecordset(dbQuery)

    (with other changes as needed), just to see if DAO gives you a more helpful error message.  That code example assumes that the table STOCKFASTNERMAXLENGTH is in the current database where the code is running.  If the table is actually in some external database, you'd have to use the DAO OpenDatabase method to open a Database object on it, and use that object as the base for the OpenRecordset call.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by AshokMech Tuesday, October 11, 2016 8:38 PM
    Tuesday, October 11, 2016 6:31 PM

All replies

  • Is SIZE a text field or a number field? You treat it as a text field. If it is a number field instead, use

        dbQuery = "SELECT MAXLENGTH FROM STOCKFASTNERMAXLENGTH WHERE SIZE = " & strFasteNerSize

    (Are you certain that the table name is not STOCKFASTENERMAXLENGTH ?)

    And shouldn't the line

            Fn_GetFastenersStockAlerts.lnMaxLength = dbRs!Length

    be

            Fn_GetFastenersStockAlerts.lnMaxLength = dbRs2!MaxLength

    ?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, October 10, 2016 8:38 PM
  • I just checked my table name. I misspelled it in Database as well. SIZE field is set to text and Length set to Number. It is supposed to be Fn_GetFastenersStockAlerts.lnMaxLength = dbRs2!MaxLength, I fail at dsRs2.Open command with Unspecified error or automation error.


    Monday, October 10, 2016 8:51 PM
  • Length or MaxLength? You still mention both...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, October 10, 2016 9:23 PM
  • Hi AshokMech,

    According to your description, I suggest that you could use Debug.Print to print dbQuery, then you could run this query in SQL View to make sure that it is right.

    Otherwise you could firstly run dbRs2, could you get any error?

    Thanks for your understanding.

    Tuesday, October 11, 2016 3:19 AM
  • Hello David.

    I ran the query in my database and it works, but not in vba.

    Thank you


    • Edited by AshokMech Tuesday, October 11, 2016 5:31 PM Clarify statement
    Tuesday, October 11, 2016 4:56 PM
  • I ran the query in my database and it works, but not in vba.

    Is STOCKFASTNERMAXLENGTH a table, or is it a stored query?  If it's a stored query, does it get any criteria or data from a control on a form?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, October 11, 2016 5:50 PM
  • Dirk

    STOCKFASTNERMAXLENGTH it is a table. Where Size is my primary key. I got a dent in my head from banging on the wall but still no solution. I am just unable to understand why the same line of code works for a different table but not this one.

    Thank you


    • Edited by AshokMech Tuesday, October 11, 2016 6:15 PM
    Tuesday, October 11, 2016 6:14 PM
  • STOCKFASTNERMAXLENGTH it is a table. Where Size is my primary key. I got a dent in my head from banging on the wall but still no solution. I am just unable to understand why the same line of code works for a different table but not this one.

    It's not obvious to me what the problem is, either.  I would suggest you try two approaches to debugging the problem.  First, try bracketing and fully qualifying fielding names (especially SIZE), in case there's a conflict with a reserved word:

        dbQuery = "SELECT SFL.[MAXLENGTH] FROM STOCKFASTNERMAXLENGTH SFL WHERE SFL.[SIZE] = '" & strFasteNerSize & "';"

    If that doesn't work, you might try running the query using DAO instead of ADO, like this:

         Dim rs2 As DAO.Recordset

         Set rs2 = CurrentDb.OpenRecordset(dbQuery)

    (with other changes as needed), just to see if DAO gives you a more helpful error message.  That code example assumes that the table STOCKFASTNERMAXLENGTH is in the current database where the code is running.  If the table is actually in some external database, you'd have to use the DAO OpenDatabase method to open a Database object on it, and use that object as the base for the OpenRecordset call.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by AshokMech Tuesday, October 11, 2016 8:38 PM
    Tuesday, October 11, 2016 6:31 PM
  • Dirk

    Thank you. What you proposed with square brackets seems to work. No more of the error atleast in the limp mode i am running now (debugging)

    Tuesday, October 11, 2016 9:14 PM