Deletion of views created via ADOX

[SysAccountant]

I have created a series of Views(virtual tables) in MS Acces via the ADOX
library.However when I try and delete it via the delete method of thee Views
collection ,an error code pops up stating "the requested object isn't part of
the collection".

Can anyone,please, help me out of this situation?

Code sample:

Sub Main()
On Error GoTo DeleteViewError

Dim cat As New ADOX.Catalog

' Open the catalog
cat.ActiveConnection = CurrentProject.Connection


'Delete the View
cat.Views.Delete "dmRd_CardRefExtraction_Global2"

'Clean up
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Exit Sub

DeleteViewError:
Set cat = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
' EndDeleteViewVB
===============================================
Code used to create the object:

Function CreateView()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim cat As New ADOX.Catalog
Dim sql_GLobalMerger_ExtractionAnalysis As String

Set conn = CurrentProject.Connection


cat.ActiveConnection = conn

sql_GLobalMerger_ExtractionAnalysis = "Select * from PseudoTable;"

cmd.CommandText = sql_GLobalMerger_ExtractionAnalysis

cat.Views.Append "dmRd_CardRefExtraction_Global2", cmd

sql_GLobalMerger_ExtractionAnalysis_MakeTable = "SELECT * INTO
[dmTbl_rdCardRefExtraction_Global] FROM dmRd_CardRefExtraction_Global2; "

DoCmd.SetWarnings (False)

DoCmd.RunSQL (sql_GLobalMerger_ExtractionAnalysis_MakeTable)

DoCmd.SetWarnings (True)

End Function

 

[RoyVidar]

I have created a series of Views(virtual tables) in MS Acces via the
ADOX library.However when I try and delete it via the delete method
of thee Views collection ,an error code pops up stating "the
requested object isn't part of the collection".

Can anyone,please, help me out of this situation?

Code sample:

Sub Main()
On Error GoTo DeleteViewError

Dim cat As New ADOX.Catalog

' Open the catalog
cat.ActiveConnection = CurrentProject.Connection


'Delete the View
cat.Views.Delete "dmRd_CardRefExtraction_Global2"

'Clean up
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Exit Sub

DeleteViewError:
Set cat = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
' EndDeleteViewVB
===============================================
Code used to create the object:

Function CreateView()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim cat As New ADOX.Catalog
Dim sql_GLobalMerger_ExtractionAnalysis As String

Set conn = CurrentProject.Connection



cat.ActiveConnection = conn

sql_GLobalMerger_ExtractionAnalysis = "Select * from
PseudoTable;"

cmd.CommandText = sql_GLobalMerger_ExtractionAnalysis

cat.Views.Append "dmRd_CardRefExtraction_Global2", cmd

sql_GLobalMerger_ExtractionAnalysis_MakeTable = "SELECT * INTO
[dmTbl_rdCardRefExtraction_Global] FROM
dmRd_CardRefExtraction_Global2; "

DoCmd.SetWarnings (False)

DoCmd.RunSQL (sql_GLobalMerger_ExtractionAnalysis_MakeTable)

DoCmd.SetWarnings (True)

End Function

You will probably get the advice to use DAO when working with Jet
in these NGs. I've never had any fascination of creating queries
on the fly, I'm more into either using stored queries, or use
dynamic SQL.

I'm thinking the reason for the error, is that the query isn't an
item in the views collection. I think only ordinary selects are.

Parameterized queries, action queries (as I think I'd call this
one), union queries etc, I think is found in the procedures
collection. You could use that collection, if you like.

Anyway, I think you can drop those through DDL, and I think
executing DROP TABLE <table or query name> on an ADO connection
should suffice, regardless of whether it is a query, view or proc.

currentproject.connection.execute _
"DROP TABLE dmRd_CardRefExtraction_Global2", , _
adcmdtext + adexecutenorecords

With a short test, that seems to work also with the currentdb object

currentdb.execute _
"DROP TABLE dmRd_CardRefExtraction_Global2", dbfailonerror

 

[SysAccountant]

Thanks for your help.

I agree with your point on using DAO in a jet environment.

I had thought up a workaround which involved DAO - achieved my goal in 2
lines of code.

I also tried using DDL on the open ADO connection,as you suggested - worked
marvelously!


SysAccountant

I have created a series of Views(virtual tables) in MS Acces via the
ADOX library.However when I try and delete it via the delete method
of thee Views collection ,an error code pops up stating "the
requested object isn't part of the collection".

Can anyone,please, help me out of this situation?

Code sample:

Sub Main()
On Error GoTo DeleteViewError

Dim cat As New ADOX.Catalog

' Open the catalog
cat.ActiveConnection = CurrentProject.Connection


'Delete the View
cat.Views.Delete "dmRd_CardRefExtraction_Global2"

'Clean up
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Exit Sub

DeleteViewError:
Set cat = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
' EndDeleteViewVB
===============================================
Code used to create the object:

Function CreateView()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim cat As New ADOX.Catalog
Dim sql_GLobalMerger_ExtractionAnalysis As String

Set conn = CurrentProject.Connection



cat.ActiveConnection = conn

sql_GLobalMerger_ExtractionAnalysis = "Select * from
PseudoTable;"

cmd.CommandText = sql_GLobalMerger_ExtractionAnalysis

cat.Views.Append "dmRd_CardRefExtraction_Global2", cmd

sql_GLobalMerger_ExtractionAnalysis_MakeTable = "SELECT * INTO
[dmTbl_rdCardRefExtraction_Global] FROM
dmRd_CardRefExtraction_Global2; "

DoCmd.SetWarnings (False)

DoCmd.RunSQL (sql_GLobalMerger_ExtractionAnalysis_MakeTable)

DoCmd.SetWarnings (True)

End Function

You will probably get the advice to use DAO when working with Jet
in these NGs. I've never had any fascination of creating queries
on the fly, I'm more into either using stored queries, or use
dynamic SQL.

I'm thinking the reason for the error, is that the query isn't an
item in the views collection. I think only ordinary selects are.

Parameterized queries, action queries (as I think I'd call this
one), union queries etc, I think is found in the procedures
collection. You could use that collection, if you like.

Anyway, I think you can drop those through DDL, and I think
executing DROP TABLE <table or query name> on an ADO connection
should suffice, regardless of whether it is a query, view or proc.

currentproject.connection.execute _
"DROP TABLE dmRd_CardRefExtraction_Global2", , _
adcmdtext + adexecutenorecords

With a short test, that seems to work also with the currentdb object

currentdb.execute _
"DROP TABLE dmRd_CardRefExtraction_Global2", dbfailonerror

 

[RoyVidar]

I would suggest using DROP PROCEDURE, which works
regardless of whether it is a query, view or proc but does not drop
base tables ;-)

Jamie.

I didn't know that, thanx!