Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 34933
  • Last Modified:

Create and Delete a table with VBA

I know how to do this via SQL, but not VBA.
I wish to be able to delete a table and then create a new table via VBA. Can this be done or do I need to call in a delete table / make table query instead?

Thanks
0
wobbled
Asked:
wobbled
  • 5
  • 3
  • 3
  • +3
1 Solution
 
dannywarehamCommented:
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
0
 
JohnK813Commented:
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.
0
 
Simon BallCommented:
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rockiroadsCommented:
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

0
 
Simon BallCommented:
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

0
 
dannywarehamCommented:
<OT>
Hey Rocki - long time no see...
0
 
NestorioCommented:
You can use standard Sql with CurrentDb method:

Example:

CurrentDb.Execute("Create Table YourTable")

CurrentDb.Execute("Drop Table YourTable")
0
 
rockiroadsCommented:
create table syntax

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

e.g.

CREATE TABLE mytable (
        MyString        TEXT,
        MyNumber     LONG      DEFAULT -1,
    )
0
 
NestorioCommented:
Hi Rocky!!, How are you?
0
 
rockiroadsCommented:
Hows it going Danny, its been a while since I last answered summat on EE, been busy with other things

0
 
rockiroadsCommented:
Hello Nestorio, see the old gang is still here!

some new faces!
0
 
NestorioCommented:
It's no so fun without you.
0
 
wobbledAuthor Commented:
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
0
 
dannywarehamCommented:
Dim myAddress as String
Dim myTable as String
myAddress = "\\data-1\data\TestExperianMar.xls"
myTable = "ExperianAIM_Inport"

DoCmd.TransferSpreadsheet acImport, , myTable, myAddress, True
0
 
rockiroadsCommented:
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!


0
 
wobbledAuthor Commented:
Brilliant

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

0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now