Private Sub MakeTable() Dim objCat As ADOX.Catalog Dim objTbl As ADOX.Table Dim objCol As ADOX.Column Dim objKey As ADOX.Key Set objCat = New ADOX.Catalog Set objTbl = New ADOX.Table Set objCol = New ADOX.Column Set objKey = New ADOX.Key
On Error GoTo MakeTable_EH
'------------------------------------------------ ' A0 开启目录。 '------------------------------------------------ ' Open the Catalog. ' Engine Type = 4 creates an Access database in 3.5 format. ' Engine Type = 5 creates an Access database in 4.0 format (default). objCat.ActiveConnection = gstrConnectionString_Dst & ";Jet OLEDB:Engine Type=5;"
'------------------------------------------------ ' B0 数据表处理。 '------------------------------------------------ ' 检查数据表存在时,予以删除。 If CheckTableExist_ADOX(gstrConnectionString_Dst, "Orders") Then objCat.Tables.Delete "Orders" End If
' 文字(130)、文字(202)、备忘(203)。 Case adWChar, adVarWChar, adLongVarWChar objCol.Properties("Jet OLEDB:Allow Zero Length").Value = True End Select End Select Next objCol
' C4: 新增各字段数据至数据表。 objCat.Tables.Append objTbl
'------------------------------------------------ ' D0 设定索引。 '------------------------------------------------ Set objKey = New ADOX.Key
With objKey .Name = "PrimaryKey" .Type = adKeyPrimary .RelatedTable = "Orders" .Columns.Append "OrderID" End With
objCat.Tables("Orders").Keys.Append objKey
'------------------------------------------------ ' E0 释放对象所占空间。 '------------------------------------------------ Set objKey = Nothing Set objTbl = Nothing Set objCol = Nothing Set objCat = Nothing
'------------------------------------------------ ' Z0 显示完成。 '------------------------------------------------ If CheckTableExist_ADOX(gstrConnectionString_Dst, "Orders") Then With txtField(2) .Alignment = vbCenter .BackColor = vbGreen .Text = "完成" End With End If
MakeTable_EH: ' 处理错误代码为3265的错误处理程序,只有当所要删除的对象不在对象集合中时,才会产生作用。 If Err.Number = 3265 Then Resume Next Else Debug.Print Err.Number, Err.Description Exit Sub End If End Sub
Dim objCat As ADOX.Catalog
Dim objTbl As ADOX.Table
Dim objCol As ADOX.Column
Dim objKey As ADOX.Key Set objCat = New ADOX.Catalog
Set objTbl = New ADOX.Table
Set objCol = New ADOX.Column
Set objKey = New ADOX.Key
On Error GoTo MakeTable_EH
'------------------------------------------------
' A0 开启目录。
'------------------------------------------------
' Open the Catalog.
' Engine Type = 4 creates an Access database in 3.5 format.
' Engine Type = 5 creates an Access database in 4.0 format (default).
objCat.ActiveConnection = gstrConnectionString_Dst & ";Jet OLEDB:Engine Type=5;"
'------------------------------------------------
' B0 数据表处理。
'------------------------------------------------
' 检查数据表存在时,予以删除。
If CheckTableExist_ADOX(gstrConnectionString_Dst, "Orders") Then
objCat.Tables.Delete "Orders"
End If
'------------------------------------------------
' C0 字段处理。
'------------------------------------------------
With objTbl
' C1: 资料表命名。
.Name = "Orders"
' C2: 新增字段。
' 在对字段设定17项属性之前,必须指明数据表的 parent catalog。
' (MS)Must set before setting properties and append column!
' (DS)identifies the parent catalog for the table.
' --------------------------------------------------------
' 自动编号字段值: Autoincrement.
' 默认值 : Default.
' 验证规则 : Jet OLEDB:ColumnValidation Rule.
' 验证文字 : Jet OLEDB:Column Validation Text.
' 必须有数据 : Nullable.
' 允许零长度字符串: Jet OLEDB:Allow Zero Length.
' --------------------------------------------------------
Set .ParentCatalog = objCat
.Columns.Append "OrderID", adInteger
.Columns("OrderID").Properties("Autoincrement") = True
.Columns.Append "CustomerID", adVarWChar, 5
.Columns("CustomerID").Properties("Nullable") = True
.Columns("CustomerID").Properties("Jet OLEDB:Allow Zero Length") = True
.Columns("CustomerID").Properties("Jet OLEDB:Column Validation Text") = "TEST"
.Columns.Append "EmployeeID", adInteger
.Columns.Append "OrderDate", adDate
.Columns.Append "RequiredDate", adDate
.Columns.Append "ShippedDate", adDate
.Columns.Append "ShipVia", adInteger
.Columns.Append "Freight", adCurrency
.Columns.Append "ShipName", adVarWChar, 40
.Columns.Append "ShipAddress", adVarWChar, 60
.Columns.Append "ShipCity", adVarWChar, 15
.Columns.Append "ShipRegion", adVarWChar, 15
.Columns.Append "ShipPostalCode", adVarWChar, 10
.Columns.Append "ShipCountry", adVarWChar, 15
End With
' C3: 设定各字段默认值及允许零长度字符串属性。
For Each objCol In objTbl.Columns
Select Case (UCase(objCol.Name))
Case "OrderID"
Case Else
Select Case objCol.Type
' 整数(2)、长整数(3)、单精准数(4)、双精准数(5)、货币(6)。
Case adSmallInt, adInteger, adSingle, adDouble, adCurrency
objCol.Properties("Default").Value = 0
' 文字(130)、文字(202)、备忘(203)。
Case adWChar, adVarWChar, adLongVarWChar
objCol.Properties("Jet OLEDB:Allow Zero Length").Value = True
End Select
End Select
Next objCol
' C4: 新增各字段数据至数据表。
objCat.Tables.Append objTbl
'------------------------------------------------
' D0 设定索引。
'------------------------------------------------
Set objKey = New ADOX.Key
With objKey
.Name = "PrimaryKey"
.Type = adKeyPrimary
.RelatedTable = "Orders"
.Columns.Append "OrderID"
End With
objCat.Tables("Orders").Keys.Append objKey
'------------------------------------------------
' E0 释放对象所占空间。
'------------------------------------------------
Set objKey = Nothing
Set objTbl = Nothing
Set objCol = Nothing
Set objCat = Nothing
'------------------------------------------------
' Z0 显示完成。
'------------------------------------------------
If CheckTableExist_ADOX(gstrConnectionString_Dst, "Orders") Then
With txtField(2)
.Alignment = vbCenter
.BackColor = vbGreen
.Text = "完成"
End With
End If
mstrBackup = txtMessage & "2.在数据库SWind.MDB,建立数据表Orders。" & vbCrLf & vbCrLf
txtMessage = mstrBackup
Exit Sub
MakeTable_EH:
' 处理错误代码为3265的错误处理程序,只有当所要删除的对象不在对象集合中时,才会产生作用。
If Err.Number = 3265 Then
Resume Next
Else
Debug.Print Err.Number, Err.Description
Exit Sub
End If
End Sub
Str= "Create Table T1( _
Column1 int primary key,
Column2 char(20),
Column3 char(20))"然后调用Connection/Command/Recordset对象实例来执行或打开即可:
Cnn.Execute str
Cmm.Execute str
Rs.Open str
---------------------------------去试一下吧
& "(" _
& "ID int identity(1,1) PRIMARY KEY," _
& "FAsouce varchar(20) not null," _
& "FAtype varchar(4) not null" _
& ")"
conn.Execute upSql
cnn.execute sqlstrsqlstr="create table newtable (field1 int,field2 varchar(10))"
cnn.execute sqlstr