Deleting Tables

[PITBULLCJR]

I asked this question a long time ago and I didn't understand what people told me. How do you delete a table. The code I am using to creat a table is.

Dim rcdTable As Recordset, dbsData As Database, tdfTable As TableDef

Set dbsData = DBEngine.Workspaces(0).OpenDatabase(path & "\mydb.mdb", False, False, ";pwd=password")

Set tdfTable = dbsData.CreateTableDef(Text1.Text)

With tdfTable
.Fields.Append .CreateField("amount", dbCurrency)
End With

dbsData.TableDefs.Append tdfTable

rcdTable.Close
dbsData.Close

Thank you so very much!!!!

[parksie]

I usually use the DELETE TABLE SQL statement.

[PITBULLCJR]

I have no idea how to use sql I was told by someone else I could create tables this way.

Set dbsData = CreateDatabase(path, dbLangGeneral & ";pwd=password")

Set dbsData = DBEngine.Workspaces(0).OpenDatabase(path1, False, False, ";pwd=password")

dbsData.Execute "CREATE TABLE Usernames (ID AUTOINCREMENT, Name TEXT, Password TEXT)"

He also said there was a way to delete them this way but he never showed me how. Is this a SQL statement? THanks!!!

[parksie]

Yes. For example:

SELECT * FROM People WHERE LastName="Jones"

pass that as the parameter to an OpenRecordset method to get the result.

Use Database.Execute("DELETE TABLE MyTable") to delete a table, I think.

[PITBULLCJR]

Well this doesn't work. Anybody know why or have any suggestions? Thanks!!

Dim strName As String, rcdTable As Recordset, dbsData As Database, tdfTable As TableDef

Set dbsData = DBEngine.Workspaces(0).OpenDatabase(path & "\henry.mdb", False, False, ";pwd=password")

dbsData.Execute ("DELETE TABLE Currency.mdb")

[JHausmann]

Drop Table tablename

is standard SQL for removing a table.

[parksie]

I thought that the DROP statement was for deleting a whole database?

[BruceG]

JHausmann is correct. To delete an entire table using SQL, its:
db.Execute "DROP TABLE TableName"
(The DELETE statement in SQL deletes records from a table, but the table itself remains intact. DROP TABLE wipes the whole table.)

For Access databases, you can also delete a table using the following DAO code (with DAO, the method IS "Delete"):

db.TableDefs.Delete "TableName"

[parksie]

I know he's correct, but I was just confirming the details in my mind. It's just that I get confused occasionally...
My usual DB, mySQL, uses DROP DATABASE and DELETE TABLE a lot, so I'll check the details. Thanks for pointing that out, though.

[JHausmann]

Originally posted by parksie
I thought that the DROP statement was for deleting a whole database?

Depends on what you want to drop ...

Drop table
Drop index
Drop database

[parksie]

Aha. I see now .

[BruceG]

At the risk of beating a dead horse, I believe you guys when you say that other DBMS's may support DROP DATABASE, but as far as I know, MS-Jet SQL for Access DB's supports only DROP TABLE and DROP INDEX (not DROP DATABASE).

[JHausmann]

Jet's hardly standard SQL.

[BruceG]

Agreed. But since Pitbull seemed to be doing the Access/Jet/DAO thing, that's perspective I took on this question.

[PITBULLCJR]

Well I can't get the sql statement to work. This is exacltly what i have:

Dim strName As String, rcdTable As Recordset, dbsData As Database, tdfTable As TableDef

Set dbsData = DBEngine.Workspaces(0).OpenDatabase(path & "\henry.mdb", False, False, ";pwd=password")

dbsData.Execute "DROP TABLE Integer"

I get an error that says:
Run-time error '3295':
Syntax error in DROP TABLE or DROP INDEX.

I have also tried doing this:

Dim strName As String, rcdTable As Recordset, dbsData As Database, tdfTable As TableDef

Set dbsData = DBEngine.Workspaces(0).OpenDatabase(path & "\henry.mdb", False, False, ";pwd=password")

dbsData.Execute "Delete TABLE Integer"

I get an error that says:
Run-time error '3075':
Sytax error (missing operator) in query expression 'Table Integer'

and I have also tried this:

Dim strName As String, rcdTable As Recordset, dbsData As Database, tdfTable As TableDef

Set dbsData = DBEngine.Workspaces(0).OpenDatabase(path & "\henry.mdb", False, False, ";pwd=password")

dbsData.Execute "delete * from Integer"

I get an error that reads:
Run-time error '3131':
Sytax error in FROM clause.

and not one of them works. Why is that? Thanks!!

[AKA]

I think that the problem is the name of the table. Integer might be a reserved word. I am not sure and I do not have Access to test it on.

[alexn]

I'm using Access97 VB - adding to the code you started off with I have inserted a delete command (as already mentioned by BruceG above)

Sub CreateAndDeleteTable()
Dim rcdTable As Recordset, dbsData As Database, tdfTable As TableDef
Set dbsData = CurrentDb
Set tdfTable = dbsData.CreateTableDef("Text1")

With tdfTable
.Fields.Append .CreateField("amount", dbCurrency)
End With

dbsData.TableDefs.Append tdfTable
MsgBox "Table Created"
dbsData.TableDefs.Delete (tdfTable.Name)
MsgBox "Table Deleted"
End Sub

All without SQL - does this help?

Alex

[JHausmann]

Originally posted by PITBULLCJR
Well I can't get the sql statement to work. This is exacltly what i have:

Dim strName As String, rcdTable As Recordset, dbsData As Database, tdfTable As TableDef

Set dbsData = DBEngine.Workspaces(0).OpenDatabase(path & "\henry.mdb", False, False, ";pwd=password")

dbsData.Execute "DROP TABLE Integer"

I get an error that says:
Run-time error '3295':
Syntax error in DROP TABLE or DROP INDEX.

I have also tried doing this:

Dim strName As String, rcdTable As Recordset, dbsData As Database, tdfTable As TableDef

Set dbsData = DBEngine.Workspaces(0).OpenDatabase(path & "\henry.mdb", False, False, ";pwd=password")

dbsData.Execute "Delete TABLE Integer"

I get an error that says:
Run-time error '3075':
Sytax error (missing operator) in query expression 'Table Integer'

and I have also tried this:

Dim strName As String, rcdTable As Recordset, dbsData As Database, tdfTable As TableDef

Set dbsData = DBEngine.Workspaces(0).OpenDatabase(path & "\henry.mdb", False, False, ";pwd=password")

dbsData.Execute "delete * from Integer"

I get an error that reads:
Run-time error '3131':
Sytax error in FROM clause.

and not one of them works. Why is that? Thanks!!

I believe AKA is right and that "Integer" is a reserverd word. However, if it let you build the table with that name, you should be able to access the table by wrapping it in brackets, ie, [Integer] in your queries.

[PITBULLCJR]

Thank you guys but if I use what Alexn gave me it works fine and dandy. It even deletes the integer table. I have many tables and I just so happened to use that one for an example. Thank You so very much for your time!!!