HOWTO: Add/Delete Table from database 
Author Message
 HOWTO: Add/Delete Table from database

Hi all,

How can I add/delete a table to database (A 2000) using ASP/ADO and how can I
add/delete field from table.

Thanks!



Mon, 01 Mar 2004 01:31:57 GMT  
 HOWTO: Add/Delete Table from database
Hi Sebastian,

By using Data Definition Language (DDL), you can easily add/delete a table,
and add/delete a field from the table. Following is a sample ASP code using
ADO to handle Access 2000 database:

Here, I use the sample database shipped with Access 2000. Please change the
connection string according to your settings.


<%
'Open the Database, please change the connection string to according to
your settings.
set conn = Server.CreateObject ("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Program
Files\Microsoft " _
      & "Office\Office10\Samples\Northwind.mdb"

'Create a new table called TESTTABLE with two text fields
strSQL = "CREATE TABLE TESTTABLE (FirstName CHAR, LastName CHAR)"
conn.Execute strSQL

'Add a new text field called MiddleName to the table
strSQL = "ALTER TABLE TESTTABLE ADD COLUMN MiddleName CHAR"
conn.Execute strSQL

'Drop the MiddleName field from the table
strSQL = "ALTER TABLE TESTTABLE DROP COLUMN MiddleName"
conn.Execute strSQL

'Drop the TESTTABLE table from the database
strSQL = "DROP TABLE TESTTABLE"
conn.Execute strSQL

conn.Close
set conn = nothing
%>

You can read Microsoft Jet SQL Reference to learn more about Data
Definition Language in Microsoft Access Help.

You can also use the ADO Extensions for DDL and Security (ADOX) object to
create and modify the structure of tables of an Access database. For more
information on using ADOX to create and modify the structure of Access
database, please read the following article:

Creating, Modifying, and Viewing the Structure of an Access Database by
Using ADO from Microsoft Office 2000/Visual Basic Programmer's Guide

http://msdn.microsoft.com/library/en-us/odeopg/html/deovrcreatingmodi...
ewingstructureofaccessdatabasebyusingado.asp

Sincerely,
Kally Wang
Microsoft Support Engineer



Tue, 02 Mar 2004 12:57:58 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Cannot add record to Visual Foxpro table after deleting all records in table

2. HowTo: Get pictures out of Access database table

3. Help deleting/adding columns to MDB tables

4. deleting records in a table and then adding new records

5. Add/Delete from Multiple Tables

6. Help deleting/adding columns to MDB tables

7. HOW i can create a form to make add,edit,delete,etc fro any table

8. Add, Delete, Move ... in in a related table

9. How to change add and delete String Table in Res

10. How do I add and delete fields in a table

11. Having trouble adding/deleting records from a database

12. Having trouble adding/deleting records from a database

 

 
Powered by phpBB® Forum Software