none
Run-time Error 5 "Invalid Procedure Call or Argument" when querying an Access table from Word RRS feed

  • Question

  • Hi everyone,

    In Word 2013, I query an .accdb database with two extremely simple SQL strings:

     

    Function GetDivisieID(Divisie As String) As String
     Dim strSQL As String, varResult As Variant
         strSQL = "SELECT Description, DivisionID FROM Divisions WHERE Description='" & Divisie & "'"
         varResult = StartQuery(SQL:=strSQL, SortString:="", FunctionName:="GetDivisieID", Scheidingsteken:="")
         GetDivisieID = LTrim(Str(varResult(0)))   
    End Function

     

    Function GetTaalID(TaalAfk As String) As String
     Dim strSQL As String, varResult As Variant
         strSQL = "SELECT LanguageID, Description FROM Language WHERE Description='" & TaalAfk & "'"
     '    strSQL = "SELECT Language.LanguageID FROM Language"
         varResult = StartQuery(SQL:=strSQL, SortString:="", FunctionName:="GetTaalID", Scheidingsteken:="")
         GetTaalID = LTrim(Str(varResult(0)))
     End Function

     

    ' StartQuery() merely exists to avoid duplicate lines of code
    Private Function StartQuery(SQL As String, SortString As String, FunctionName As String, Scheidingsteken As String) As Variant
     Dim conn As ADODB.Connection, strConn As String, rs As ADODB.Recordset
     Dim lngN As Long, strResult() As String, strDB As String

        Set StartQuery = Nothing
         ReDim strResult(0)
         strResult(0) = ""
         strDB = modPaden.OWDatabase ' path to the database
         Set conn = New ADODB.Connection
         Set rs = New ADODB.Recordset
         strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB & ";"
     
        With conn
             .Open (strConn)
             .CursorLocation = adUseClient
         End With
        
        With rs
             .Open SQL, conn ' <= the error occurs here
             Set .ActiveConnection = Nothing
        End With

        If Not (rs.BOF And rs.EOF) Then       
            If SortString <> "" Then
                 rs.Sort = SortString
             End If
             rs.MoveLast
             rs.MoveFirst
             lngN = 0
            
            While Not rs.EOF
                 Select Case FunctionName
                     Case "GetDivisieID"
                         strResult(lngN) = rs!DivisionID
                     Case "GetTaalID"
                         strResult(lngN) = rs!LanguageD
                     Case Else
                 End Select
                
                rs.MoveNext
                 lngN = lngN + 1
                 ReDim Preserve strResult(lngN)
             Wend
            
            If Not (UBound(strResult) = 0 And strResult(0) = "") Then
                 ReDim Preserve strResult(UBound(strResult) - 1)
                 StartQuery = strResult()
             End If
         End If
        
        rs.Close
         Set rs = Nothing
         conn.Close
         Set conn = Nothing
     
    End Function


    My code calls GetDivisieID, which works fine, and immediately thereafter calls GetTaalID, which produces run-time error 5: "Invalid Procedure Call or Argument" when executing varResult = StartQuery(.... When I use the even simpler strSQL = "SELECT Language.LanguageID FROM Language", commented out in the above code, the error occurs as well.

    In Access, all three queries work. I have copied the contents of the SQL-view in Access in the Word VBA-editor in order to avoid typing mistakes.
     
    Why does the error occur? How can I avoid it?

    Thank you,
     
    Cooz

     

    Thursday, September 5, 2013 11:48 AM

Answers

  • Hi Cooz

    Good detective work :-)

    Access (as most applications) has certain "protected terms" that it exposes in its APIs. These shouldn't be used otherwise, as they can lead to conflicts/confusion. That's apparently the case with "Language", which makes a certain amount of sense. Access is probably trying to do something with language settings which it of course cannot (and that's not what you want, anyway). The folks in Access for Developers could probably tell you more about this as they know the software better :-)

    Word has similar pitfalls when using field codes, although I'm not sure I can recall the exact terms that lead to "undesirable" results. Something about using a term that's also a Windows setting in the Registry related to numerical or date formatting... Word would pick that up instead of the one defined in the document. Just one of those things you learn by experience since they're rarely documented!


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, September 10, 2013 12:53 PM
    Moderator
  • Hi Cooz

    Not sure we can help you here. It might make more sense to put this code in your Access project, see if it fails there, as well (I would expect it to) and then ask in the Access for Developers forum...

    Obviously, we don't have any where near enough information to repro your scenario: we don't have the database; we don't know what kinds of values you're passing.

    The information you give is somewhat in conflict. Is it so that opening the recordset is causing the error? Your description is a bit vague on that point, but the comment in your code indicates that's so?

    Is the recordset a table, or another query? There are certain kinds of queries that can't be run from an OLE DB connection that the Access environment can handle: you need to make sure that the underlying dataset is something that works with OLE DB. Could there be permissions issues? Might the table or query be "locked" in Access?


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, September 5, 2013 12:36 PM
    Moderator
  • Hi Cindy,

    Thank you for your reply. I have the following additional information:

    > "It might make more sense to put this code in your Access project" - When I make the queries in Access and run them, the results are as expected.
    > "Is it so that opening the recordset is causing the error?" - Yes, it is.
    The recordset is a table, there are no permissions issues and the table is not locked. I am the only one who has access to this .accdb file.

    But.

    I found out that the error occurs because of the name of the table, "Language". I have changed its name to "Language_old", and everything works fine. To test this I have made a new database with one table "Languages" that has five records and two columns: "LanguageID" containing a number from 1 to 5, and "Description" containing the values "NL", "EN", "DU", "FR" and "SP". My code works with this database, after having changed "Languages" into "Language" of course. When I rename the table "Language" and I alter strSQL again to search this table, an error occurs. It's a different one this time though: "Fout -2147467259 (80004005) tijdens uitvoering: Methode Open van object _Recordset is mislukt" (which should translate more or less to "Run time error ... Method Open of object _Recordset failed").

    Amazing huh? Why shouldn't a table be allowed the name "Language" when queried from Word?

    Kind regards,
    Cooz

    
    
    
    
    
    
    Tuesday, September 10, 2013 12:38 PM

All replies

  • Hi Cooz

    Not sure we can help you here. It might make more sense to put this code in your Access project, see if it fails there, as well (I would expect it to) and then ask in the Access for Developers forum...

    Obviously, we don't have any where near enough information to repro your scenario: we don't have the database; we don't know what kinds of values you're passing.

    The information you give is somewhat in conflict. Is it so that opening the recordset is causing the error? Your description is a bit vague on that point, but the comment in your code indicates that's so?

    Is the recordset a table, or another query? There are certain kinds of queries that can't be run from an OLE DB connection that the Access environment can handle: you need to make sure that the underlying dataset is something that works with OLE DB. Could there be permissions issues? Might the table or query be "locked" in Access?


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, September 5, 2013 12:36 PM
    Moderator
  • Hi Cindy,

    Thank you for your reply. I have the following additional information:

    > "It might make more sense to put this code in your Access project" - When I make the queries in Access and run them, the results are as expected.
    > "Is it so that opening the recordset is causing the error?" - Yes, it is.
    The recordset is a table, there are no permissions issues and the table is not locked. I am the only one who has access to this .accdb file.

    But.

    I found out that the error occurs because of the name of the table, "Language". I have changed its name to "Language_old", and everything works fine. To test this I have made a new database with one table "Languages" that has five records and two columns: "LanguageID" containing a number from 1 to 5, and "Description" containing the values "NL", "EN", "DU", "FR" and "SP". My code works with this database, after having changed "Languages" into "Language" of course. When I rename the table "Language" and I alter strSQL again to search this table, an error occurs. It's a different one this time though: "Fout -2147467259 (80004005) tijdens uitvoering: Methode Open van object _Recordset is mislukt" (which should translate more or less to "Run time error ... Method Open of object _Recordset failed").

    Amazing huh? Why shouldn't a table be allowed the name "Language" when queried from Word?

    Kind regards,
    Cooz

    
    
    
    
    
    
    Tuesday, September 10, 2013 12:38 PM
  • Hi Cooz

    Good detective work :-)

    Access (as most applications) has certain "protected terms" that it exposes in its APIs. These shouldn't be used otherwise, as they can lead to conflicts/confusion. That's apparently the case with "Language", which makes a certain amount of sense. Access is probably trying to do something with language settings which it of course cannot (and that's not what you want, anyway). The folks in Access for Developers could probably tell you more about this as they know the software better :-)

    Word has similar pitfalls when using field codes, although I'm not sure I can recall the exact terms that lead to "undesirable" results. Something about using a term that's also a Windows setting in the Registry related to numerical or date formatting... Word would pick that up instead of the one defined in the document. Just one of those things you learn by experience since they're rarely documented!


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, September 10, 2013 12:53 PM
    Moderator
  • Hi Cindy,

    "Good detective work :-)"
    Thank you!

    "Just one of those things you learn by experience since they're rarely documented!" Yeah - I discovered that one... "rarely documented" I mean. Google wasn't any help here. At least now know what might be the cause when something completely inexplicable happens.

    Thank you for thinking along,
    Cooz

    Tuesday, September 10, 2013 1:11 PM
  • Hi Cindy,

    I just found out that enclosing "Language" in square brackets

    strSQL = "SELECT LanguageID, Description FROM [Language] WHERE Description='" & TaalAfk & "'"

    also solves the problem.

    Cooz

    Monday, October 21, 2013 1:07 PM