Databases Reference
In-Depth Information
When using an ACCESS Database, it is best to try to avoid using a system DSN. They are
much slower because they go through ODBC, which then uses the Jet Drivers to access the database.
They also have to do a registry lookup that further adds to the performance gap. When DSN-less
connections are utilized, the Jet Drivers are accessed directly, and the performance increase can be
dramatic.
Because all the connection requirements are specified within the code, DSN-less connections
require no setup when using the ODBC Data Source Administrator as shown earlier. All require-
ments are specified at run time in code. Here is an example DSN-less connection string for
Microsoft access:
connstring = “ODBC;DRIVER={Microsoft Access Driver(*.mdb)};DBQ= C:\temp\
ADA\MCS. mdb”
where:
ODBC — Specifies an interface to the ODBC drivers.
DRIVER — Specifies which database application driver needs to be utilized; in this case,
Microsoft Access. Other popular database applications such as Oracle and Filemaker
Pro have their own specific drivers as well.
DBQ — The path to the database file.
A DSN-less connection string to an Oracle database would look something like this:
“Driver={Microsoft ODBC for Oracle};Server=Clock_Store.world; Uid=myUserID;
Pwd=myPassword;”
Notice that, in the above example, if the database is password protected, the developer may specify
the user ID and password for access to the database without the user being prompted for them.
Further notice that Oracle DSN-less connections utilize a server name (specified by SERVER =)
as opposed to a filename. Although the center of focus here is MS Access, Oracle is so popular it
is worth mentioning how to create a DSN-less connection string for Oracle databases.
Some important limitations are worth mentioning. The Excel 2000 implementation of OLE DB
does not support background refresh, parameter queries, or page fields that retrieve data for each
item separately. The most egregious offender in this category is the lack of support for background
refreshing. In practical terms, what this means is that if a query is built from or triggered from a
form, the form must be hidden and unloaded prior to executing the query. Otherwise an error will
result.
Now some code to support use of the above concepts using VBA. A DSN connection using
the previously defined MCSDSN DSN can be made utilizing the following code:
Sub Test_Get_Data()
'Will NOT work when subroutine invoked from a Forms button press
'OR when query is to be Executed as a Background Process
DoEvents
'Define SQL query string
sqlstring = "SELECT NAME FROM Suppliers"
'Define connection string and reference File DSN.
connstring = "ODBC;DSN=MCSDSN"
'Create a QueryTable in worksheet beginning with cell A1.
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("A1"), Sql:=sqlstring)
