Create and Delete a table with VBA

To lose a table:

CurrentDb.Execute "DROP TABLE MyTable"

To lose it's contents:

Dim mySQL as String
mySQL = "DELETE * FROM yourtable"
DoCmd.RunSQL mySQL

To make a table from a query:

Dim mySQL as String
mySQL = "INSERT INTO mynewtable FROM yourtable"
DoCmd.RunSQL mySQL

The easiest way, in your case at least, would be to use one of the following two lines:

DoCmd.RunSQL "SQL statement to create or drop table"

or

CurrentDB.Execute "SQL statement to create or drop table"

Both commands can be used for most SQL "action" queries.

docmd.deleteobject actable, "tblname".....

just looking to see if there is a create command similar...otherwise its a ddq or createtdf option.

I usually keep a template table which i use to make a copy of.

docmd.copyobject?

Hello People, long time no eSee

Danny has told u what to do with deleting, with regards to creating, do u wish to create a brand new table?

run sql like CREATE TABLE, that should do it

needs dao3.6 reference from the tools, references option in vb
dim dbs as database
set dbs = currentdb
dim tdfNew as tabledef
Set tdfNew = dbs.CreateTableDef("Contacts")

    With tdfNew
        ' Create fields and append them to the new TableDef
        ' object. This must be done before appending the
        ' TableDef object to the TableDefs collection of the
        ' Northwind database.
        .Fields.Append .CreateField("FirstName", dbText)
        .Fields.Append .CreateField("LastName", dbText)
        .Fields.Append .CreateField("Phone", dbText)
        .Fields.Append .CreateField("Notes", dbMemo)
end with

dbs.TableDefs.Append tdfNew

<OT>
Hey Rocki - long time no see...

You can use standard Sql with CurrentDb method:

Example:

CurrentDb.Execute("Create Table YourTable")

CurrentDb.Execute("Drop Table YourTable")

create table syntax

CREATE TABLE <tablename> ( <fieldname> <datatype> <default value> )

e.g.

CREATE TABLE mytable (
        MyString        TEXT,
        MyNumber     LONG      DEFAULT -1,
    )

Hi Rocky!!, How are you?

Hows it going Danny, its been a while since I last answered summat on EE, been busy with other things

Hello Nestorio, see the old gang is still here!

some new faces!

It's no so fun without you.

Excellent, thanks.

I still have one issue though, I wish to delete the table (works fine) then import data from a spreadsheet to a new table and call that table ExperainAIM_Input.  I have tried this usind the TransferSpreadsheet function, but once the table is deleted how would I create a new one from the import spreadsheet?


CurrentDb.Execute "DROP TABLE ExperianAIM_Input"


DoCmd.TransferSpreadsheet acImport, 3, _
    "ExperianAIM_Inport", "\\data-1\data\TestExperianMar.xls", True

Hey Nestorio, I need to find a good joke to cause a few laughs, but the last lot I got were a bit too rude for this forum!

Brilliant

Thanks to everyone for their help, Danny Wareham gets the points as it is his two replies that I have used.