Private Sub cmdBinary_Click() Dim conDatabase As ADODB.Connection Dim SQL As String On Error GoTo Error_Handler Set conDatabase = Application.CurrentProject.Connection '注意: Fields 1 through 4 can be created through both 'SQL View and the Jet OLEDB Provider. 'Fields 5 and 6 can only be created through the 'Jet OLE DB provider.
SQL = "CREATE TABLE tblCodeBinaryDataTypes (" & _ "Field1_BINARY BINARY, " & _ "Field2_BINARY250 BINARY(250), " & _ "Field3_VARBINARY VARBINARY, " & _ "Field4_VARBINARY250 VARBINARY(250), " & _ "Field5_BVARYING BINARY VARYING, " & _ "Field6_BVARYING250 BINARY VARYING(250))" conDatabase.Execute SQL MsgBox "The BINARY datatypes table has been created!" conDatabase.Close Set conDatabase = Nothing Exit Sub Error_Handler: MsgBox Err.Description, vbInformation End Sub
在acIntSQL 数据库中保存的两个查询是属于DML 语句内容却执行类似DDL的操作。其中的SELECT INTO 语句从已有的数据库中找到数据并用这些数据创建新的工作表。通过这些范例的学习,你将学会如何删除目标工作表,前提是这些工作表已经存在。
在中级DML语句的窗体中的Create Customers Database输出指令和按键展示了一个对SELECT INTO 语句的有趣应用。这是一个告诉你使用中级SQL 语句所能完成的各种事情的一个好的例子。下面就是关于输出命令和按键的子过程的代码:
Private Sub cmdCreateDB_Click() Dim conCatalog As ADOX.Catalog Dim conDatabase As ADODB.Connection Dim SQL As String Dim sDBName As String On Error GoTo Error_Handler 'Initialize objects & variables. Set conDatabase = Application.CurrentProject.Connection Set conCatalog = New ADOX.Catalog sDBName = "C:\Customers.mdb" 'Create the Customers database. conCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & sDBName 'Run the DML statement to build the Customers table. SQL = "SELECT * INTO tblCustomers IN '" & sDBName & _ "'" & "FROM tblCustomers" conDatabase.Execute SQL MsgBox "The new CUSTOMERS database has been created " & _ "as " & sDBName & ".", vbInformation conDatabase.Close Set conDatabase = Nothing Set conCatalog = Nothing Exit Sub Error_Handler: MsgBox Err.Description, vbInformation End Sub