Full Version: Runtime error 3265, ADO reference
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
Sammy5
Oct 30 2009, 02:46 PM
CODE
Run-time error '3265':

Item cannot be found in the collection corresponding to the requested name or ordinal.


Hello,

I am running into a runtime error over and over when trying to set up a multilistbox and taking the values from the listbox and plugging them into a SQL statement to filter a query. I am not sure why it is happening but it happens when referencing the ADOBX command

Here's my code, would you happen to see any faults? I have the reference set up for both ADO Ext. 6.0 for DLL and Security and ActiveX Data Objects 2.1 Library

CODE
Private Sub BuildString()



    Dim blnQueryExists As Boolean

    Dim cat As New ADOX.Catalog

    Dim xt As New ADODB.Command

    Dim qry As ADOX.View

    Dim varItem As Variant

    Dim strCodes As String

    Dim strSQL As String

    

    blnQueryExists = False

    Set cat.ActiveConnection = CurrentProject.Connection

    For Each qry In cat.Views

        If qry.Name = "qryMultiCompSettings" Then

            blnQueryExists = True

            Exit For

        End If

    Next qry

    

    Application.RefreshDatabaseWindow

    

    DoCmd.Echo False

    

    For Each varItem In Me.lstMulti.ItemsSelected

        strCodes = strCodes & ", '" & Me.lstMulti.ItemData(varItem) & "'"

    Next varItem

    If Len(strCodes) = 0 Then

        MsgBox "No Item codes selected."

        Exit Sub

    Else

        strCodes = Right(strCodes, Len(strCodes) - 1)

        strCodes = "IN(" & strCodes & ")"

    End If

    

    strSQL = "SELECT tblComponentSettings.ProductID, tblComponentSettings.ComponentNameID, " & _

                "tblComponentSettings.TargetValue, tblComponentSettings.UpperValue, tblComponentSettings.LowerValue " & _

                "FROM tblComponentSettings " & _

                "WHERE tblComponentSettings.[ComponentNameID] [forms]![frmMultiTarget]![cboMultiComp] " & _

                "tblComponentSettings.[ProductID] " & strCodes & ";"

                

    MsgBox strCodes

    cat.ActiveConnection = CurrentProject.Connection

   [color="red"]  Set xt = cat.Views("qryMultiCompSettings").Command [/color]

    xt.CommandText = strSQL

    Set cat.Views("qryMultiCompSettings").Command = xt

    Set cat = Nothing

    

    

End Sub


Thank you very much in advanced!!!

Sam


Edit: Decreased blowout width of code
Edited by: LPurvis on Fri Oct 30 19:56:37 EDT 2009.
Sammy5
Oct 30 2009, 02:58 PM
I was wondering, would it be because I have a DAO reference also be the problem? I remember hearing some references use the same commands sometimes and this can cause problems.
LPurvis
Oct 30 2009, 06:54 PM
Hi

No DAO will be no problem at all here.
IMHO they compliment each other (in Access anyway) rather than do any harm.
(Declarations should really include a library prefix in any case where there's any chance of ambiguity).

I'd say your code is failing because the query literally doesn't exist - just as the error message is saying.
In your code if the query exists - you set the value of blnQueryExists at the start, but don't use it to subsequently check how to behave.
If you need to create the query then you can do so.

It's true, this kind of local Access manipulation is much simpler with DAO. (For which you wouldn't even require the library to be present).

CurrentDb("qryMultiCompSettings").SQL = strSQL

And you're done.
FWIW you seem to be missing an equality assignment operator (=) in your SQL.

"WHERE tblComponentSettings.[ComponentNameID] = [forms]![frmMultiTarget]![cboMultiComp] "

Cheers.
Sammy5
Nov 1 2009, 02:53 AM
Ahh I see, thank you very much sir!

Works good, sort of, I am running into a problem or two now, though.

Some reason it isn't finding the query if it already exists in the

CODE
    For Each qry In cat.Views

        If qry.Name = "qryMultiCompSettings" Then

            blnQueryExists = True

            Exit For

        End If

    Next qry


as the boolean will remain false and still run the

CODE
     If blnQueryExists = False Then

        cmd.CommandText = "SELECT * FROM tblComponentSettings"

        cat.Views.Append "qryMultiCompSettings", cmd

    End If


giving me the message "the query already exists" which I know it does, but that boolean should have been set the true and the if statement should never have ran so I have been having to manually delete the query every time to run it "successfully" else it will consider that boolean to be false all the time, if I don't recreate it everytime, though it will give me that 3265 error again saying the query does NOT exist.


I guess the easiest way of asking is what would you suggest to use to check to see if the query exists, if does delete it and create it again, if not just create it? I am looking up DAO syntaxes but I am very unfamiliar with this :(
LPurvis
Nov 1 2009, 06:58 AM
It depends. Are you sure this query is a view?
Though the definition is blurred in Access (Jet) into querydefs, ADOX uses Views and Procedures (to account for server databases).
ADO allows for this with Jet too - categorising the queries into Views and Procedures depending upon the definition they meet.
Views are unparameterized selections - procedures perform actions or accept parameters.
If the query exists and you're using parameters - check the Procedures collection instead of Views.

(You should append it to that collection too).

Cheers.
Sammy5
Nov 2 2009, 01:46 AM
Haha, sorry sir but I am unable to understand what a "view" even is :( I have looked up their definitions on the MSDN site but I am still somewhat confused what they are. This is my first time using any ADO/JET so this is very much a learning experience for me (as has been my entire project! I started at 0 when I first started this, I didn't even know what normalization was).

I looked around for a alternative and found one, again on the microsoft's MSDN site, this is to look for object exists which I make a module using their code and use a very simple function in my script, it worked it exactly how I needed it to, though I am sure there are more efficient methods out there that I hope to master one day.

Here's my code now for reference incase someone else who is in the learning stage may need to look. The basic thing the script does is it filters a query in a multi select listbox, in it it will delete it if it exists, and then create it.


CODE
Private Sub BuildString()
    On Error GoTo BuildString_Err
    Dim blnQueryExists As Boolean
    Dim cat As New ADOX.Catalog
    Dim cmd As New ADODB.Command
    Dim qry As ADOX.View
    Dim varItem As Variant
    Dim strCodes As String
    Dim strSQL As String
    Dim db As dao.Database
    Dim q As dao.QueryDef
    Dim hit As Byte
    
    
    
    blnQueryExists = False
    Set cat.ActiveConnection = CurrentProject.Connection
    
    If ObjectExists("Query", "qryMultiCompSettings") Then
        DoCmd.DeleteObject acQuery, "qryMultiCompSettings"
    End If

    If blnQueryExists = False Then
        cmd.CommandText = "SELECT * FROM tblComponentSettings"
        cat.Views.Append "qryMultiCompSettings", cmd
    End If
    
    Application.RefreshDatabaseWindow

    DoCmd.Echo False

    If SysCmd(acSysCmdGetObjectState, acQuery, "qryMultiCompSettings") = acObjStateOpen Then
        DoCmd.Close acQuery, "qryMultiCompSettings"
    End If
    
    For Each varItem In Me.lstMulti.ItemsSelected
        strCodes = strCodes & ", " & Me.lstMulti.ItemData(varItem) & ""
    Next varItem
    If Len(strCodes) = 0 Then
        MsgBox "No Item codes selected."
        Exit Sub
    Else
        strCodes = Right(strCodes, Len(strCodes) - 1)
        strCodes = "IN(" & strCodes & ")"
    End If
    
    strSQL = "SELECT tblComponentSettings.* FROM tblComponentSettings " & _
             "WHERE (((tblComponentSettings.ProductID) " & strCodes & ") AND ((tblComponentSettings.ComponentNameID)=[forms]![frmMultiTarget]![cboMultiComp]));"
    
    
    
                
    MsgBox strCodes
    cat.ActiveConnection = CurrentProject.Connection
    Set cmd = cat.Views("qryMultiCompSettings").Command
    cmd.CommandText = strSQL
    Set cat.Views("qryMultiCompSettings").Command = cmd
    Set cat = Nothing
    
    DoCmd.OpenQuery "qryMultiCompSettings"
    
    Application.RefreshDatabaseWindow
BuildString_Exit:
    DoCmd.Echo True
    Exit Sub
BuildString_Err:
    MsgBox "An unexpected error hass occurred." _
        & vbCrLf & "Procedure: BuildString()" _
        & vbCrLf & "Error Number: " & Err.Number _
        & vbCrLf & "Error Description:" & Err.Description _
        , vbCritical, "Error"
    Resume BuildString_Exit
End Sub


A lot of the script also was inspired from a example of Martin Green's

Here's the module that I use for the ObjectExists function (literally copy and paste from microsoft)



CODE
Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
    
     Set db = CurrentDb()
     ObjectExists = False
    
     If strObjectType = "Table" Then
          For Each tbl In db.TableDefs
               If tbl.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next tbl
     ElseIf strObjectType = "Query" Then
          For Each qry In db.QueryDefs
               If qry.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next qry
     ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
          For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     ElseIf strObjectType = "Macro" Then
          For i = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     Else
          MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End If
    
End Function



Thank you very much again sir! I hope to understand ADOX better someday, I need to do a lot more reading
LPurvis
Nov 2 2009, 08:56 AM
There are many ways to perform a check for object.
For example:

CODE
Function fAccessObjectExists(intType As Access.AcObjectType, strObjectName As String) As Boolean
    
    Dim strIn As String
    
    Select Case intType
        Case acTable
            strIn = "(1,4,6)"
        Case acQuery
            strIn = "(5)"
        Case acForm
            strIn = "(-32768)"
        Case acReport
            strIn = "(-32764)"
        Case acMacro
            strIn = "(-32766)"
        Case acModule
            strIn = "(-32761)"
    End Select
    
    With CurrentDb.OpenRecordset("SELECT COUNT(*) FROM mSysObjects WHERE Name = '" & strObjectName & "'" & _
                            IIF(Len(strIn) > 0, " AND Type In ", "") & strIn, dbOpenDynaset)
        fAccessObjectExists = .Fields(0) > 0
        .Close
    End With
    
End Function


What I was refering to from an ADOX point of view was that the collection in which you were working wasn't accurate. The Procedures collection would then be appropriate.

I wouldn't recomment using ADOX for working with Access objects anyway really unless necessary.
It can become a minefield of connection contention - whereas the DAO methods offer substantially faster performance with local objects without any noteworthy issues.

Cheers.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.