ایجاد جدول با دستور sql در اکسس
برای اجرای دستورات DDL مربوط به SQL میتوان از کد نویسی VBA در برنامه Microsoft Access استفاده نمود.
Option Compare Database
Option Explicit
Sub CreateTableDDL()
'Purpose: Create two tables, their indexes and relation using DDL.
Dim cmd As New ADODB.Command
Dim strSql As String
'Initialize
cmd.ActiveConnection = CurrentProject.Connection
'Create the Contractor table.
strSql = "CREATE TABLE tblDdlContractor " & _
"(ContractorID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"Surname TEXT(30) WITH COMP NOT NULL, " & _
"FirstName TEXT(20) WITH COMP, " & _
"Inactive YESNO, " & _
"HourlyFee CURRENCY DEFAULT 0, " & _
"PenaltyRate DOUBLE, " & _
"BirthDate DATE, " & _
"EnteredOn DATE DEFAULT Now(), " & _
"Notes MEMO, " & _
"CONSTRAINT FullName UNIQUE (Surname, FirstName));"
cmd.CommandText = strSql
cmd.Execute
Debug.Print "tblDdlContractor created."
'Create the Booking table.
strSql = "CREATE TABLE tblDdlBooking " & _
"(BookingID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"BookingDate DATE CONSTRAINT BookingDate UNIQUE, " & _
"ContractorID LONG REFERENCES tblDdlContractor (ContractorID) " & _
"ON DELETE SET NULL, " & _
"BookingFee CURRENCY, " & _
"BookingNote TEXT (255) WITH COMP NOT NULL);"
cmd.CommandText = strSql
cmd.Execute
Debug.Print "tblDdlBooking created."
End Sub
Sub CreateFieldDDL()
'Purpose: Illustrates how to add a field to a table using DDL.
Dim strSql As String
Dim db As DAO.Database
Set db = CurrentDb()
strSql = "ALTER TABLE MyTable ADD COLUMN MyNewTextField TEXT (5);"
db.Execute strSql, dbFailOnError
Set db = Nothing
Debug.Print "MyNewTextField added to MyTable"
End Sub
Function CreateFieldDDL2()
'Purpose: Add a field to a table in another database using DDL.
Dim strSql As String
Dim db As DAO.Database
Set db = CurrentDb()
strSql = "ALTER TABLE Table IN 'C:\Data\junk.mdb' ADD COLUMN MyNewField TEXT (5);"
db.Execute strSql, dbFailOnError
Set db = Nothing
Debug.Print "MyNewField added"
End Function
Function CreateViewDDL()
'Purpose: Create a new query using DDL.
Dim strSql As String
strSql = "CREATE VIEW qry1 as SELECT tblInvoice.* from tblInvoice;"
CurrentProject.Connection.Execute strSql
End Function
Sub DropFieldDDL()
'Purpose: Delete a field from a table using DDL.
Dim strSql As String
strSql = "ALTER TABLE [MyTable] DROP COLUMN [DeleteMe];"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub
Sub ModifyFieldDDL()
'Purpose: Change the type or size of a field using DDL.
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyText2Change TEXT(100);"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub
Function AdjustAutoNum()
'Purpose: Set the Seed of an AutoNum using DDL.
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN ID COUNTER (1000,1);"
CurrentProject.Connection.Execute strSql
End Function
Function DefaultZLS()
'Purpose: Create a field that defaults to a zero-length string using DDL.
Dim strSql As String
strSql = "ALTER TABLE MyTable ADD COLUMN MyZLSfield TEXT (100) DEFAULT """";"
CurrentProject.Connection.Execute strSql
End Function