Drop Table with Command object

[hbrems]

Hi all,

I'm trying to delete a table from my db.

When I try to execute this code it returns a message saying: "The query cannot be used as row source". I'd like to learn why...

Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command

     ' Catalog settings
     Set cat = New ADOX.Catalog
     cat.ActiveConnection = CurrentProject.Connection

     ' Delete table
     Set cmd = cat.Procedures("droptest").Command
     cmd.Execute

 

[MarkK]

What line causes the error? What is the SQL of the command?

 

[the_net_2.0]

are you using ADOX when the DDL query statements are readily available? I don't use them much, so I'm wondering if you could just write a ddl instead of using adox at all??

 

[hbrems]

What line causes the error? What is the SQL of the command?

Hi Lagbolt!

This line causes the error:

cmd.Execute

The SQL of the command is:

DROP TABLE TableName;

Just adding that the query works fine by itself.

 

[hbrems]

are you using ADOX when the DDL query statements are readily available? I don't use them much, so I'm wondering if you could just write a ddl instead of using adox at all??

To be honest I have no clue what you are saying. I can tell you that I'm using ADOX because the Catalog object allows me to easily browse objects present in the DB.

 

[the_net_2.0]

you said you're trying to delete a table via code. You can do that two (or more) ways, without using ADOX. One is a DDL query statement, the other is by using the "docmd.deleteobject" method.

wouldn't either of those be easier?

 

[hbrems]

What would the DDL query statement look like? Something like this?

Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
cn.Execute ("DROP TABLE SomeTable")

Does this mean I have to hard code the SQL statement in my code? I was kinda hoping to seperate the queries from the code. Maybe I am indeed making things to hard for myself haha.

 

[the_net_2.0]

DDL's have nothing to do with ADO libraries. the statement is simply:

Currentdb.execute "DROP TABLE tablename"

I have no idea why ''DDL'' is attached in Access, and you can't do it easier through the interface. although the query wizard gives you options too, but unless you know syntax, it's useless to use that tool.

 

[hbrems]

DDL's have nothing to do with ADO libraries.

Well I'm actually dropping a table in a different DB. So I need to build a connection with an ADO (or DAO) object right? Or wrong again?

 

[the_net_2.0]

I would assume that if you set an object as a database object, you could simply replace "currentdb" with the object's name. but I don't know.

right or wrong, I would do it your way, since you're already close.