modifying the SQL in ADO

[rewdee]

I'm trying to modify a query (Command Object) in my current project in VBA. I'm using this code:

Dim cat        As New ADOX.Catalog
Dim qdfEvtData As ADODB.Command
Dim sSQL       As String
    
cat.ActiveConnection = CurrentProject.Connection
Set qdfEvtData = cat.Procedures("qryEvent_Data").Command
....

I keep getting error #3265 "Item cannot be found in the collection corresponding to the requested name or ordinal". The query "qryEvent_Data" is a query found in current project.

I'm pulling out hair on this one. I would be very greatful if anyone can help.

Thanks,
Rewdee

 

[rewdee]

I don't know if the file is corrupt or what but I have 23 queries (or stored procedures in XP) but when I iterate through the catalog, only 4 appear in the immediate window as :
?cat.Procedures(0).Name
qry_trigger_taglist
?cat.Procedures(1).Name
qryExpEvent_Data
?cat.Procedures(2).Name
qryTemp
?cat.Procedures(3).Name
qryTriggerTagEdit
?cat.Procedures(4).Name
==blow exception here==

Any ideas?
Rewdee

 

[cmmrfrds]

Queries are also considered views at least in Access 2000, so maybe check from the view collection. I use this to Open a query in ADO.
Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection

Dim v As View
Dim vn As View
For Each v In cg.Views
Debug.Print "views = "; v.Name
If v.Name = "query1" Then
Set vn = v
End If
Next
rs.Open vn.Name, connString, adOpenForwardOnly, adLockReadOnly

 

[rewdee]

This is closer and I modified my procedure but as:

Dim cat        As New ADOX.Catalog
    Dim qdfEvtData As ADODB.Command
    Dim sSQL       As String
    Dim v As View

    cat.ActiveConnection = CurrentProject.Connection
    
    For Each v In cat.Views
        If v.Name = "qryEvent_Data" Then
           Set qdfEvtData = v.Command
        End If
    Next v

   sSQL = "SELECT *FROM Table1 " _
         & " WHERE Field_ID = " & lngEvtID 
         
    qdfEvtData.CommandText = sSQL
    Set cat.Procedures("qryEvent_Data").Command = qdfEvtData
    Set cat = Nothing
    getRecSource = "qryExpEvent_Data"

But I blow an exception when I try to save the SQL in

Set cat.Procedures("qryEvent_Data").Command=...

Any Ideas?
Rewdee

 

[cmmrfrds]

Just a thought since I haven't used the procedure object. Isn't command a property of procedure much like name is. You already have a reference set to the object, i.e. procedure, which is qdfEvtData. I am assuming command holds the sql string. "set" is used for objects not properties.

qdfEvtData.Command = sSQL

Wouldn't you equate the new sql string to the command.

 

[rewdee]

Okay figured it out. The problem is that Procedures are action queries and Views are regular SELECT statements. Hence the code would be correct if the query is an action query be should be changed slightly as SELECT Query as :

Dim cat        As New ADOX.Catalog
Dim qdfEvtData As ADODB.Command
Dim sSQL       As String
    
cat.ActiveConnection = CurrentProject.Connection
Set qdfEvtData = cat.Views.("qryEvent_Data").Command
....

Thanks for the posts,
Rewdee