Drop table

[element2013]

Hello Programmers,

1) I want to figure out how I can drop a table using "if argument". If the table exists then drop table, if not exists do something else.

2) I want to figure out (ALSO) how I can drop a column using "if argument". If the column exists then drop column, if not exists do something else.



I wrote something but is not enough..I can't find more clear information about that.

Function first()
On Error GoTo Macro1_Err
Dim BD1 As Recordset
DoCmd.SetWarnings False

Set BD1 = CurrentDb.OpenRecordset("BD_example table")

If BD1.EOF Then

DoCmd.RunSQL "drop table [BD_example table];"
Else

'Do Something

End If

End Function

 

[pr2-eugin]

Few days/weeks back I helped another user to do similar thing i.e. Check if a Query exists, the code is quiet similar, I have modified it for looking for Table..

Public Function checkExistance(objectName As String) As Boolean
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
On Error GoTo handleMe
    checkExistance = IsObject(CurrentDb.TableDefs(qryName))
exitOnErr:
    Exit Function
handleMe:
    If Err.Number = 3265 Then
        checkExistance = False
    Else
        MsgBox "Error (" & Err.Number & ") - Encountered", vbCritical
    End If
    Resume exitOnErr
End Function

The code will check if the Table exisits in the Database.. So you can use the Function to test.. Something like..

Sub firstTest()
    If checkExistance("[BD_example table]") Then
        DoCmd.RunSQL "drop table [BD_example table];"
    Else
        [COLOR=Green]'Do Something[/COLOR]
    End If
End Sub

For Field existence, check the code by Dave in THIS thread.

 

[element2013]

Thank you very much Paul. But I have another question.

Why the first code doesn't execute? I think this line change something:

Public Function checkExistance(objectName As String) As Boolean


Sorry I'm a newcomer!

 

[pr2-eugin]

Copy the code into a common Module not a Form Module.. Then Save it by giving a name anything other than checkExistance. Compile the CODE.

When you say it does not execute what exactly happens?

 

[element2013]

When I press F8 do nothing, just a sound from the pc but when I press the execute button (green button) it show me the Macros Box.
I mean the list of Macros (like Excel) but empty.

 

[pr2-eugin]

Not F8, Go to the VBA Menu, in the Menu bar there should be a Menu called "Debug", under which you can find the Compile option, click that (If there are any errors correct them), Once the Code has been compiled use that method in the FirstTest Sub.

 

[element2013]

A ha! I understand now. Very interesting, works perfectly.

Thank you for you valuable help!!

 

[pr2-eugin]

Most Welcome.. Good Luck.

 

[Galaxiom]

BTW I am curious about your reasons for scripting these changes. It is pretty sophisticated stuff for a "new-comer".

If it is for rolling out new versions of your application then that makes sense. However if you are taking such actions as part of day to day operation of the database then you are on the wrong track entirely.