[AccessD] Finding & Deleting A Table Using ADO

Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset
Set cnn=new ADODB.Connection
Cnn.Provider="Microsoft.Jet.OLEDB.4.0"
Cnn.Open "D:\Test.mdb" 'Change this to the path of the database you want to
use this for
Set rs = Cnn.OpenSchema(adSchemaTables)
If rs.EOF = False Then rs.MoveFirst
Do Until rs.EOF = True
    If rs.Fields("TABLE_NAME").Value = "schednew" Then
	  Cnn.Execute "DROP TABLE schednew"
	End if
	Rs.movenext
Loop
Rs.close
Set rs=nothing
Cnn.close
Set cnn=nothing


This will work from outside the database.  To use this inside, change it to
this:

Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset
Set cnn=CurrentProject.Connection
Set rs = Cnn.OpenSchema(adSchemaTables)
If rs.EOF = False Then rs.MoveFirst
Do Until rs.EOF = True
    If rs.Fields("TABLE_NAME").Value = "schednew" Then
	  Cnn.Execute "DROP TABLE schednew"
	End if
	Rs.movenext
Loop
Rs.close
Set rs=nothing
Set cnn=nothing

Drew

-----Original Message-----
From: paul.hartland at fsmail.net [mailto:paul.hartland at fsmail.net] 
Sent: Friday, April 21, 2006 4:27 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Finding & Deleting A Table Using ADO
Importance: High

To All,
I am without success trying to write some code using ADO (Access XP) in a
function which will loop through all the tables I have and if a particular
table (schednew) exists then delete it.
Can anyone please provide some sample code or point me in the right
direction to do this, as I am suffering from a Friday brain dead moment I
think.
Thanks for any help in advance.
Paul Hartland
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com