creating and deleting table using vba codes

[axism]

Hi, I have an adp file which I want to have a button to create a new table and another to delete that table base on click event. Can someone show me how to do it? thanks

 

[OnTheFly]

This is how I do it. Of course you will have to substitue your field names and types for what I have and you don't necessarily have to return a string. I do because the table is temporary and named based on the userID and I need to refer to that table when I am done.

If you look at the Error procedure you will find the method for Dropping a table. This is an SQL database. There might be some 'tweeking' involved if you are connected to another kind of database.

Function CreateTheTable() As String
Dim strTableName As String
Dim cmd As ADODB.Command
Dim strCommand As String
Dim Conn As ADODB.Connection

'Creates a table
On Error GoTo CreateTheTable_Error

'Attempt to Create a Table
strTableName = "MyTableName"

Set Conn = CurrentProject.Connection

'Build the Command Text statement
strCommand = "CREATE TABLE " & strTableName & _
" (" & _
"DetailID int IDENTITY PRIMARY KEY, ExpDate smalldatetime, " & _
"ExpAmount smallmoney DEFAULT 0, ExpDetails varchar(100) " & _
") "

'Build the table
Set cmd = New ADODB.Command

With cmd
.ActiveConnection = Conn
Restart_Here:
.CommandText = strCommand
.CommandType = adCmdText
.Execute
End With

CreateTheTable = strTableName

Exit_CreateTheTable:
Set Conn = Nothing
Set cmd = Nothing
Exit Function


CreateTheTable_Error:
If Err.Number = -2147217900 Then 'Object Exists Error
With cmd
.CommandText = "DROP TABLE " & strTableName
.Execute
End With
Resume Restart_Here
Else
MsgBox Err.Number & "-" & Error$, vbCritical, "Error...Error...Error"
Resume Exit_CreateTheTable
End If

End Function

Hope this helps.

OnTheFly

 

[LeanneGodney]

Can I just say THANKS VERY MUCH for this. I've been battling with how to delete a table, or even refer to a table. I'm a VBA developer who has replied completely on the ADO way of referring to tables, enum, etc. So this has been very helpful.