need help with ADO

[EvilCabal]

Hello,

I finally decided to switch from DAO to ADO but I ran across a weird problem when trying to create a Query :

Here is the code

Sub CreateQuery()
Dim catCurr As New ADOX.Catalog
Dim cmdcurr As New ADODB.Command

catCurr.ActiveConnection = CurrentProject.Connection

cmdcurr.CommandText = "Select * From MyTable"
catCurr.Procedures.Append "qry", cmdcurr

End sub

Sub ListQuery()
Dim catCurr As New ADOX.Catalog
Dim cmdcurr As ADODB.Command
Dim rec As New ADODB.Recordset

catCurr.ActiveConnection = CurrentProject.Connection
Set cmdcurr = catCurr.Procedures("qry&quot.Command

rec.Open cmdcurr, , adOpenStatic, adLockPessimistic
Debug.Print rec.GetString
End Sub


This is very simple code but when I run the red line I get a run-time error 3265 (Item cannot be found in the collection corresponding to the requested name or ordinal).

Of course I ran the procedure CreateQuery first.

Another odd thing is that after I call CreateQuery catCurr.Procedure.Count returns 1 and after I call ListQuery it returns 0!? But if I try to re-run CreateQuery it get the error "Object Already Exists"

Thanx for the help.

 

[cmmrfrds]

Try looping through the View Collection.


''- Microsoft ADO Ext. 2.6 for DDL and Security
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

 

[EvilCabal]

Thanx cmmrfrds, it works but how come I can't access it through the procedures collection? The exemple I had was from a book so it should work.

Also, looping through the collection every time I need a query will slow down the proccessing a lot. Anyone knows how to fix this?

 

[cmmrfrds]

I suggested looping so that you could see all the queries in the views collection, but directly reference the view name and it should work.

Set vn = cg.Views("query1&quot
rs.Open vn.Name, connString, adOpenForwardOnly, adLockReadOnly

 

[EvilCabal]

Ok thanx it works.

But I'm still wondering why I can't access it the through the procedure collection... It's really annoying because I can't delete anything through the view collection. Is this a problem everyone has or am I the only one?

 

[cmmrfrds]

This works to delete select queries.

Dim vws As ADOX.Views
Set vws = cg.Views
vws.Delete ("query7&quot

I have not used Procedure so I don't know why it does not work. I could not delete update queries through the View.

 

[autocutter]

Try this:

Sub CreateQuery()
Dim catCurr As New ADOX.Catalog
Dim cmdcurr As New ADODB.Command

Set cmdcurr.ActiveConnection=CurrentProject.Connection

cmdcurr.CommandText = "Select * From MyTable"

Set catCurr.ActiveConnection = CurrentProject.Connection

catCurr.Procedures.Append "qry", cmdcurr

End sub

 

[EvilCabal]

Autocutter :

I get an error 3001 (Arguments are of the wrong type, out of acceptable range, or in conflict with one another) on the append line unless I remove the line "cmdCurr.ActiveConnection = CurrentProject.Connection"

If I remove this line I go back to the exact same code that caused the problem.

I noticed another weird thing. If the CommandText select statement refers to an unexisting table or field, everything works. I only get in trouble with "Correct" sql statement. What the hell is going on!!!

cmmrfrds:
Thanx your method works fine. But I would still like to solve my problem with procedures just for the sake of my mental heath

 

[cmmrfrds]

Here is a note from microsoft and my guess is that the syntax you are providing is considered a view by ADOX for Jet. Maybe you could use syntax for creating a parameterized stored procedure instead, otherwise, the view should do what you need.

Note When using the OLE DB Provider for Microsoft Jet, the Procedures collection Append method will allow you to specify a View rather than a Procedure in the Command parameter. The View will be added to the data source and will be added to the Procedures collection. After the Append, if the Procedures and Views collections are refreshed, the View will no longer be in the Procedures collection and will appear in the Views collection.

 

[EvilCabal]

Thanx you very much cmmrfrds, that makes sense.