In Access Using Intermediate SQL Now we have discussed the syntax of intermediate SQL, let's see some of our channels we can use in an Access application. Database Examples are included as the attached belt of this article, here is a database of databases called acintsql.mdb. Any of ACITSQL is based on all the themes mentioned herein, and it demonstrates the different SQL statements we discussed by queries and examples. Many of the queries used in AcintSQL are based on data existed and included in a particular worksheet, or based on other existing database objects. If you have a malfunction that is running a query due to losing data, open a worksheet to reset the form and click the worksheet to reset the button. This will re-produce the worksheet and the original default data. If you want to manually reset process through the worksheet, you need to perform the query process in the following order: Drop Table Shipping Drop Table Invoices Drop Table Customers Drop Table CreditLimit Create Table Customers Create Table Invoices Create Table Shipping Create Table CreditLimit Populate Customers Populate Invoices Populate Shipping Populate CreditLimit Query is a SQL statement stored in the Access database and calls you can also be used directly by Access User Interface or Visual Basic® For Applications (VBA). The query can be created using Access Query Designer, and an ACCESS Query Designer can easily establish a powerful visual tool for the SQL statement. Or you can also establish a query by entering the SQL statement directly in the SQL view window. As described in the "Microsoft Jet SQL for Access 2000 Basics", Access transforms all the data-oriented tasks in the database into SQL statements. To demonstrate this, let's use Access Query Designer to create a query. Open the AcintSql database. (Click here to copy the AcintSql.mdb database example.) Make sure that both forms of TBLCUSTOMERS and TBLINVOices have been created and contain data. Select the queries option from the Objects bar in the database window. Click the button New in the Database window toolbar. Select Design View in the New Query dialog and click OK. Select TBLCustomers in the Show Table dialog and click Add; then select TBLINVOICES and click Add; then click the Close button. Select the Last Name field in the TBLCUStomers domain list and drag it into the first domain in the design table. Select InvoiceDate and Amount fields in the TBLinvoices Domain Name list and drag them into your design table. Select Ascending in the INVICWDATE domain in the design form. Select View from the Access menu bar and click SQL View. This opens the SQL View window and the SQL syntax that is displayed in the query is being used. Note This query is similar to the "Join - Inner" query stored in the AcintSql database. Embedded statements embedded SQL statement means you use SQL statements in the Visual Basic for Applications (VBA) programming language. Although in-depth discussions how to use VBA beyond the scope of this article, how to use programs to run SQL statements is a simple job.
In the AcintSql database, there are two forms that use internal SQL statements to run through JET OLE DB Provider and ADO: the intermediate DDL form for presentation data definition statements, as well as the Intermediate DML form for demo data processing statements. Inter-stage DDL statement This ACITSQL database has many examples of SQL statements you can use to manage your database structure. Some Data Definition Language (DDL) statements are stored in a data definition query. Other, use the inline SQL inside the program design code. If you want to use these DDL examples, you need to delete some database objects before running it. For example, if you want to run a query for creating a current data type, you will first determine that the current data form does not exist. If not, then this query will return a message tells you that this form already exists. Before running any DDL example, check the database object it wants to create or change to determine that it has been set, so the program can run completely. In the Inline DDL statement example, the same recommendations are still applicable: check and set the database objects they will affect to ensure the smooth operation of the DDL statement. In general, the inline DDL statement is only running by simply setting an ADO Connection object and transmits the SQL statement to the operation program of the Connection object. Here is an input output from the binary data type of the intermediate DDL statement form. Private Sub cmdBinary_Click () Dim conDatabase As ADODB.ConnectionDim SQL As StringOn Error GoTo Error_HandlerSet conDatabase = Application.CurrentProject.Connection Note: 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 theJet 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 SQLMsgBox "The BINARY datatypes table has been created!" conDatabase.CloseSet conDatabase = NothingExit SubError_Handler: MsgBox Err.Description, vbInformationEnd Sub running therethrough After a DDL statement, open the affected database in the design view to view what changes. If the DDL statement affects the association between the form, open the editing association window to view its changes. For example, let's check the ALTER TABLE W / FAST Foreign Key Output Instruction and Buttons in the Intermediate DDL Stateform Form. Open the AcintSql database. Determine TBLCUSTOMERS and TBLINVoices forms have been created. Select Forms from the Objects strip in the database window.
Focus on the Form of the Intermediate DDL statement in the Database Window Toolbar and click the Design button. In the intermediate ddl statement form, right-click the ALTER TABLE W / FAST Foreign Key button and select Build Event from the pop-up menu. This will open the VBA development environment and the code window that contains the CMDFastKey_Click child process. Check the SQL statement assigned to the SQL variable. SQL = "ALTER TABLE tblInvoices" & _ "ADD CONSTRAINT FK_tblInvoices" & _ "FOREIGN KEY NO INDEX (CustomerID) REFERENCES" & _ "tblCustomers (CustomerID)" & _ "ON UPDATE CASCADE" & _ "ON DELETE CASCADE" Note DDL The statement changes the TBLINVoices form and adds a firm external keyword limit. At the same time, it also establishes a data association between TBLINVoices and TBLCustomers by using the cascading sentence. Close the VBA development environment. Turn off the Intermediate DDL statement form. From the Tools menu, select the RELATIONSHIPS ... menu item to open the associated window. Double-click the associated link between TBLCUSTOMERS and TBLINVoices to open the Edit Relationships dialog. Do not pay attention to the cascade update and delete options. Close the dialog. When the associated link is still displayed, press the DELETE button to delete the join. Close the associated window. If the interface is still displayed in the database window, click the Open button in the Database Window Toolbar. Click the Alter Table W / Fast Foreign Key button to generate a foreign key contact. Turn off the Intermediate DDL statement form. When the new relationship link is created, open the Edit Relationshipship dialog using the pro previously mentioned steps. Watch the cascade update and delete options have been set. Intermediate DML Statement This AcintSQL database has many examples of data handling language (DML) statements, where you can use them to find data, and most of them are implemented in the form of queries. The DML statement implemented only in the form of online SQL is based on the intermediate DML statement form. Three examples in the form use "_" and "%" wildcard in the Like clause, and use the SELECT INTO statement to create a form in an external database. Two queries saved in the ACINTSQL database are DML statements to perform operations similar to DDL. The SELECT INTO statement found data from the existing database and creates a new worksheet with these data. Through these examples, you will learn how to delete the target worksheet, provided that these worksheets already exist. The Create Customers Database Output Instruction and Button in the Form of the Intermediate DML statement demonstrates an interesting app for the SELECT INTO statement. This is a good example of a variety of things that tell you to use the intermediate SQL statement.
The following sub-process is about command and outputs key code: Private Sub cmdCreateDB_Click () Dim conCatalog As ADOX.CatalogDim conDatabase As ADODB.ConnectionDim SQL As StringDim sDBName As StringOn Error GoTo Error_HandlerInitialize objects & variables.Set conDatabase = Application.CurrentProject.ConnectionSet conCatalog = New ADOX.CatalogsDBName = "C: /Customers.mdb" Create the Customers database.conCatalog.Create "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source =" & sDBNameRun the DML statement to build the Customers table.SQL = "SELECT * INTO tblCustomers IN" & sDBName & _ "" & "FROM tblCustomers" conDatabase.Execute SQLMsgBox "The new CUSTOMERS database has been created" & _ "as" & sDBName & ".", vbInformationconDatabase. Closeset condatabase = NothingSet Concatalog = NothingeXIT Suberror_Handler: msgBox Err.Description, vbinformationnd Sub If the customer database already exists, the code will return a message tells you that this database cannot be created. Let us gradually run this code to see what it has done. Sticking to your C: The drive determines that the Customers.mdb file does not exist if it exists. Open the AcintSql database. Determine the TBLCUSTOMERS database has been created and this database has been loaded. Select Forms from the Objects strip in the database window. The Form of the Intermediate DM statement is youte in the Database Window toolbar and click the Open button. Click the Create Customers Database Press to create a new database. Switch to Explorer View your C drive content. Customers.mdb database files should have been created. Double-click the Customers.mdb database file to open another Access instance. Open the TBLCUSTOMERS database. Note that the data it contains and the data contained in the TBLCustomers in the ACISQL database. This code exemplary that generates a new database is created using the Adox Object Library with Jet Ole DB Provider to create an Access database. The discussion of the ADOX object library has exceeded the scope of this article. If you want to know more about it, find "Microsoft ActiveX Data Objects (ADO" in Access 2000 Online Help and open its directory until you see "Microsoft Ado Extensions For DDL and Security". Conclusion We show you how many new tasks are completed, and also tell you how to complete the same task in different ways.
