Convert to ADO from DAO Method

[n3wkid]

Dear All,
Good Morning first.

I have code in DAO Method such as

Function delete_tb(name_of_table)
Dim rs1 As Recordset
Dim db As DAO.Database

Set rs1 = CurrentDb.OpenRecordset("SELECT MSysObjects.Name" _
    & " FROM MSysObjects WHERE MSysObjects.Type= 1 And MSysObjects.Flags=0" _
    & " and MSysObjects.Name='" & name_of_table & "'")
    
    If Not rs1.EOF Then
        Set db = CurrentDb
        db.TableDefs.Delete name_of_table
        db.Close
        Set db = Nothing
    End If
    
    rs1.Close
    Set rs1 = Nothing

End Function

How to write the function above in ADODB Method?
thanks

[twinnyfo]

n3wkid,

You could accomplish this rather easily using the following code:

    If Not rs1.EOF Then 
        DoCmd.DeleteObject acTable, name_of_table 
    End If

This might solve the problem.

Please let me know if that hepps!

[n3wkid]

twinnyfo,

I have face error..and I don't understand full to change it
with ADO

maybe you can totally re write if you want to help me?

[twinnyfo]

You could also do this without using a record set of any kind. Just use a dlookup function to find the name of the table in MySysObjects. I'll take a look at this and try to send a quick sub that you could use. I am sure it's fairly simple.

[n3wkid]

thanks so much..I waiting this..

[twinnyfo]

n3wkid,

You actually have everything you need, you just need to put it all together:

Public Sub DeleteTable(TableName)
    Dim strTable As String
    strTable = Nz(DLookup("[Name]", "MSysObjects", _
        "[Type] = 1 And Flags = 0 And Name='" & TableName & "'"), "")
    If Not strTable = "" Then
        DoCmd.DeleteObject acTable, TableName
    End If
End Sub

Hope this hepps!

[n3wkid]

Thank you so much... :)

[twinnyfo]

Glad I could help. Let us know if you come across any more stumbling blocks and we'll do what we can to help!