Databases Reference
In-Depth Information
FIGURE 7.9
Test application to establish a sample connection.
'Create a QueryTable in worksheet beginning with cell A1.
With
Workbooks(ActiveWorkbook.Name).Worksheets(ActiveSheet.Name).
QueryTables.Add(Connection:=connstring, _
Destination:=Range("A1"), Sql:=sqlstring)
DoEvents
.Refresh
End With
End Sub
The preceding sample connections can be tested by selecting ADA->Chapter 7->Access Connection.
Doing so will bring up the GUI shown in Figure 7.9. Pressing either button will establish a
connection to the sample Microsoft Access database from Excel. (For this sample application to
work on the reader's machine, the sample Microsoft Access database “MCS.mdb” must be copied
to the location “C:\temp\ADA\” from the CD-ROM.) Although pressing either button will establish
a connection and query the sample database for the Supplier names, one button utilizes a DSN to
create the connection, and the other creates a DSN-less connection. To create a connection using
a DSN, remember that the DSN must be set up on the machine running the code as described
earlier. DSN-less connection strings require no connectivity setup on the host machine and are thus
less complicated and preferred methods of connection.
7.4
QUERIES: HOW TO RETRIEVE INFORMATION
IN DATABASES USING SQL
Connecting to a database is meaningless unless the information desired can be extracted from it.
The way information is extracted from a database is through a Query. Queries utilize their own
language known as SQL or Structured Query Language. Entire texts have been written on writing
and constructing SQL Queries. Fortunately, there are a few simple constructs that will allow most
users to extract the information they require for automated data analysis and report preparation.
SQL varies depending upon the database utilized. Thus, code that works with Microsoft Access
may not function correctly (or at all) with Oracle.
Information is queried from databases utilizing some form of the Select From SQL construct.
Recall that in the previous section, when setting up database connectivity, the following SQL
command was utilized:
“SELECT NAME FROM Suppliers”
The SELECT command is followed by what fields (or columns) in the database the user wishes to
access. In this instance, the user wants to view the field named “NAME.” The FROM command is
used to identify where the query is to pull the information from (usually a table). Here, the user wishes
