Now we have discussed the syntax of intermediate SQL, so let's look at some ways we can use it in an Access application. The database example is included in this article. Here is a database example called acIntSQL. mdb. Any part of acIntSQL is based on all topics mentioned in this article, and
Now we have discussed the syntax of intermediate SQL, so let's look at some ways we can use it in an Access application. The database example is included in this article. Here is a database example called acIntSQL. mdb. Any part of acIntSQL is based on all topics mentioned in this article, and
Now we have discussed
IntermediateSQL syntax, let's look at some ways we can use it in an Access application.
Database example
As a supplement to this article, here is a database example called acIntSQL. mdb.
Any part of acIntSQL is based on all topics mentioned in this article, and it demonstrates the different SQL statements we discuss through the query and sample code.
Many of the queries used in acIntSQL are based on the data existing and contained in a specific worksheet, or other existing database objects. If a query fails due to data loss, open the worksheet reset form and click the reset button. This will re-produce the worksheet and the original default data. If you want to manually reset the process through the worksheet, You need to execute these query processes in the following order:
Drop Table Shipping
Drop Table Invoices
Drop Table MERs
Drop Table CreditLimit
Create Table MERs
Create Table Invoices
Create Table Shipping
Create Table CreditLimit
Populate MERs
Populate Invoices
Populate Shipping
Populate CreditLimit
Query
A query is an SQL statement that is stored in an Access database and can be called at any time. It can also be directly accessed by the Access user interface or Visual Basic? For Applications (VBA) Programming Language call. You can use Access Query Designer to create queries. Access Query Designer is a powerful visual tool that can easily create SQL statements. Alternatively, you can enter an SQL statement in the SQL View window to create a query.
As described in "Microsoft Jet SQL for Access 2000 basics", Access converts all data-oriented tasks in a 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 the tblCustomers and tblInvoices forms have been created and contain data.
In the database window, select the Queries option from the Objects bar.
Click New in the toolbar of the Database window.
In the New Query dialog box, select Design View and click OK.
In the Show Table dialog box, select tblCustomers and click Add. Then, select tblInvoices and click Add. Then, click Close.
In the tblCustomers Domain Name List, select the Last Name Domain and drag it to the first domain in the design table.
In the tblInvoices Domain Name List, select the InvoiceDate and Amount domains and drag them to the design table.
Select Ascending from the Sort attribute of the InvoicwDate field in the design table.
Select View from the Access menu bar and click SQL View. In this way, the SQL View window is opened and the SQL syntax used by Access in the query is displayed.
Note that this query is similar to the Join-Inner query stored in the acIntSQL database.
Embedded statement
Embedded SQL statements are SQL statements used in Visual Basic for Applications (VBA) programming languages. Although in-depth discussions on how to use VBA are beyond the scope of this article, it is a simple task to use programs to run SQL statements.
In the acIntSQL database, two forms that use internal SQL statements need to be run through Jet OLE DB provider and ADO: The Intermediate DDL form that demonstrates data definition statements, and the Intermediate DML form that demonstrates the data processing statement.
IntermediateDDL statement
This acIntSQL database has many examples of SQL statements that you can use to manage your database structure. Some Data Definition Language (DDL) statements are stored as data definition queries. Others are used as inline SQL within 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 to create the current data type, you must first confirm that the current data form does not exist. If not, the query returns a message indicating that the form already exists. Before running any DDL example, check the database object to be created or changed to make sure it has been set so that the program can run completely.
In the example of inline DDL statements, the same recommendation is still applicable: Check and set the database objects they will affect to ensure the smooth running of DDL statements.
In general, the inline DDL statement runs by simply setting an ADO Connection object and passing the SQL statement to the running program of the Connection object. The following is
IntermediateThe binary data type input and output of the DDL statement form.
Private Sub cmdBinary_Click () Dim conDatabase As ADODB. Connection
Dim SQL As String
On Error GoTo Error_Handler
Set 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
'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 pes ypes table has been created! "
ConDatabase. Close
Set conDatabase = Nothing
Exit Sub
Error_Handler:
MsgBox Err. Description, vbInformation
End Sub
After running one of the DDL statements, open the affected database in the design view to view the changes. If DDL statements affect the association between forms, open the edit Association window to view the changes. For example, let's check
IntermediateThe Alter Table w/Fast Foreign Key in the DDL Statement Form outputs commands and buttons.
Open the acIntSQL database.
Make sure that the tblCustomers and tblInvoices forms have been created.
In the database window, select Forms from the Objects bar.
In the database window toolbar, highlight the form of the Intermediate DDL statement and click the Design button.
In the Intermediate DDL statement form, right-click Alter Table w/Fast Foreign Key and select Build Event… from the pop-up menu .... This operation will open the VBA development environment and the code window containing the javasfastkey_click subprocess.
Check the SQL statements allocated to SQL variables.
SQL = "ALTER TABLE tblInvoices "&_
"Add constraint FK_tblInvoices "&_
"Foreign key no index (CustomerID) REFERENCES "&_
"TblCustomers (mermerid )"&_
"On update cascade "&_
"On delete cascade"
Note that the DDL statement changes the tblInvoices form and adds strong external keyword restrictions. It also establishes a data association between tblInvoices and tblCustomers by using cascading clauses.
Disable the VBA development environment.
Disable the Intermediate DDL statement form.
From the Tools menu, select Relationships... Menu to open the association window.
Double-click the link between tblCustomers and tblInvoices to open the Edit Relationships dialog box.
Note that the cascade update and delete options are not set.
Close the dialog box.
When the link is still displayed, press Delete to Delete the link.
Close the association window.
If the Intermediate DDL statement is still displayed in the Database window, click Open in the toolbar of the Database window.
Click Alter Table w/Fast Foreign Key to generate an external keyword contact.
Disable the Intermediate DDL statement form.
After a new link is created, use the preceding steps to open the Edit Relationships dialog box.
Note that the cascade update and delete options have been set.
IntermediateDML statements
This acIntSQL database has many examples of data processing language (DML) statements. In these examples, you can use them to find data, and most of them are implemented in the form of queries. The only DML statement implemented in the form of online SQL is based on
IntermediateDML statement form. The three examples in the form use the "_" and "%" wildcards in the LIKE clause, and use the select into statement to create a form in an external database.
The two queries saved in the acIntSQL database belong to DML statements but execute DDL-like operations. The select into statement finds data from an existing database and creates a new worksheet with the data. Through these examples, you will learn how to delete the target worksheet, provided that the worksheet already exists.
In
IntermediateThe Create MERs Database output command and buttons in the DML statement form show an interesting application of the select into statement. This is one that tells you how to use
IntermediateA good example of what SQL statements can do. The following is the code for the sub-process of output commands and buttons:
Private Sub partition createdb_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: \ MERs. mdb"
'Create the MERs database.
ConCatalog. Create "Provider = Microsoft. Jet. OLEDB.4.0 ;"&_
"Data Source =" & sDBName
'Run the DML statement to build the MERs table.
SQL = "SELECT * INTO tblCustomers IN '" & sDBName &_
"'" & "FROM tblCust
