Chapter 19 Working with External Databases
Chapter 19 Working with External Databases
O
ne powerful feature of Access is that you can easily make connections to external
databases and use the tables in the same way you would if they were internal
tables within your application.
You can make your code interact with any database as long as the database is ODBC
(Open Database Connectivity)-compatible and the appropriate driver is available. ODBC
allows applications to access the database in a standard way from many different languages,
including VBA. This can be extremely useful if you want to bring data from a database into
your application.
For example, you may want to import data from an accounting system into your application.
The accounting system doesn’t necessarily provide this functionality in the application, but
the database has the data you are interested in. If the database has an ODBC driver available,
you can bring the data into your application in any shape or form to use as needed.
Databases such as Microsoft Access, Microsoft SQL Server, and Oracle all support ODBC,
and provided you have the relevant permissions to the database, you can read data into your
database and even write data back if need be. Writing data back should be done with extreme
care, however, because you can easily destroy the integrity of a relational database in this
way. If you change an ID number in a field that is used for a relationship into another table,
then the database may appear to be correct, but its relationship and integrity are totally blown.
The safest way is to only use a read-only ID on the database, and this is probably all that
the database owner will grant you.
239