ADO View assistance

[FoFa]

Trying to create a dynamic passthrough query using ADO.

So one of the parameters is the query name.

cat is an ADOX catalog
cmd is the ADO command

 cat.ActiveConnection = CurrentProject.Connection
on error resume next
 cat.Views.Delete SPTQueryName
on error goto errortrap
 cmd.ActiveConnection = cat.ActiveConnection
 cmd.CommandText = sSQL
 cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
 cmd.Properties("Jet OLEDB:Pass Through Query Connect String") = ConnectString
 cat.Views.Append SPTQueryName, cmd

Where to start, the properties setting the pass-through to true fails
the properties setting connection fails

if i comment those out, it fails on the append as the object already exists.
If I comment the first on error out, it fails because the delete says it does not exist.

I know it can't exist and not exist at the same time.

So where am I going astray?

 

[Banana]

Not familiar with setting up a PT query via ADO - Just to be sure - does it makes a difference if you set the connection string first then set pass through statement to true after?

Can I ask you why are you using ADO? Why not DAO which would be considerably less hassle to set up.

 

[FoFa]

DAO is suppose to be on the way out.
Of course they have said that for quite a few years now.
So I am trying to do it the current way (ADO).
I am not getting the error on the properties set now, just the it does not exist on the delete, and the already exist on the append.
I added a cat.Views.Refresh after the delete as well, but didn't change anything.

 

[Banana]

Actually, I believe it's backward now. ADO is on its way out, and DAO is alive & well. That is because Access team had updated DAO twice, in 2007 and 2010, while ADO has not been updated since 2.8 (?) and they've since moved onto ADO.NET which has nothing in common with ADO.


Anyway.

I looked at the help file:

' BeginCreateViewVB
Sub Main()
    On Error GoTo CreateViewError

    Dim cmd As New ADODB.Command
    Dim cat As New ADOX.Catalog
    
    ' Open the Catalog
    cat.ActiveConnection = _
        "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source='c:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb';"
    
    ' Create the command representing the view.
    cmd.CommandText = "Select * From Customers"
    
    ' Create the new View
    cat.Views.Append "AllCustomers", cmd

    'Clean up
    Set cat.ActiveConnection = Nothing
    Set cat = Nothing
    Set cmd = Nothing
    Exit Sub
    
CreateViewError:

    Set cat = Nothing
    Set cmd = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
' EndCreateViewVB

I think that maybe we should try and simplify the code first. Maybe start with that code and verify this does in fact work, then try your code again, but omit all the extra settings & properties, and see if this does work as well.

 

[FoFa]

Well if I pass it a name that does NOT exist, it creates the Pass Through query fine, so I would say it is the ADOX object (cat in this case) that I am having an issue with for some reason.
it is not deleting the current query (does not see it).

 

[Banana]

Could the name of query you can't delete be a reserved name? Could you delete the other query that didn't exist or do you fail on this as well?

One more thing - the help file mentions that views can "accept" a procedure but it gets added into procedures collection. However unlikely this may, does your troublesome query appear in the procedures collection instead of views?

I'll have a experiment later when I have some time.

 

[FoFa]

No, it was a unique query name, tried different versions of the names with the same luck.
Then ran into a post using BING that someone was having the same issue. They changed it to use DOCMD.DeleteObject acquery and that worked just fine (DOH slaps forehead).
We make it too hard sometimes. Forgot about DOCMD.
Seems to be working just fine now.

As Always thanks for the assistance.

 

[Banana]

I'm glad you found a solution.

 

[REvans]

I'm glad you found a solution.

Hello Banana or anyone reading this,
This is just on the off chance as I can see this thread is 10 years old, but I am having this exact same situation. Do you have any ideas? It seems most people get cat.views.delete to work somehow.
The problem is this-
The query is there.
I try and delete it- error, not found.
I try and make a new one- error, it is already there!
I can't use DoCmd because I am in Excel vba, and DoCmd works if the database is open in Access at the same time (which won't be the case for the users).

If you get this I am wondering if you see something wrong with it.

This code is in Excel Conn is connecting to the database in Access,

Public Sub CreateViewAdox(Conn As ADODB.Connection, sQueryName As String, sQuerySQLText As String)
'###############################################################
'note ADOX has an additional reference in tools
'Purpose: Create a query using ADOX.
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command

'Initialize.
cat.ActiveConnection = Conn
cat.Views.Refresh 'makes no difference

'###############################################################
'not working - error, can't find even if is there
'On Error Resume Next
cat.Views.Delete sQueryName
'On Error GoTo 0

'Assign the SQL statement to Command object's CommandText property.
cmd.CommandText = sQuerySQLText

'Append the Command to the Views collection of the catalog.
'this will fail if the query is already there
On Error Resume Next
cat.Views.Append sQueryName, cmd
On Error GoTo 0
'Clean up.
Set cmd = Nothing
Set cat = Nothing
Debug.Print "View created. Name = " & sQueryName & " Query = " & sQuerySQLText
'###############################################################

Thank you,
Rog

 

[REvans]

I found a workaround, it is really old school. I use a SQL statement and just DROP VIEW and ignore the error if the view could not be found. It does not use ADOX or DoCmd at all. I guess it helped for me to type out the problem though.