Deletion of views created via ADOX

G

Guest

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
 
R

RoyVidar

SysAccountant said:
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
Click to expand...

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
 
G

Guest

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

RoyVidar said:
SysAccountant said:
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
Click to expand...

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
Click to expand...
 
J

Jamie Collins

RoyVidar said:
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.
Click to expand...

I didn't know that! 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.

--
 
R

RoyVidar

Jamie Collins said:
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.
Click to expand...

I didn't know that, thanx!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ADOX catalog tables slow performance 16
Any ADOX experts? 3
How to add field properties to MDB with ADOX using VB??? 1
Programmatically changing a SQL view in a ADP 3
ADOX Delete View Problem 1
Delete a Query, or Empty it and Reload With New SQL? 2
Query to Filter by Dates, Using Form 7
Build Dynamic Query from Form 6

Top