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