There are various methods to connect to data from other databases. The following are the most common methods:
Linking to another Access Database’s Tables
You might want to use another Access database’s table that is on a shared network drive. This can be useful if you need to store your tables in a single database while maintaining reports, forms and other objects on a different database that is copied between the shared database users. Existing databases can easily be divided into back-end and front-end databases.
With a linked table opened in linked tables, you can set properties to control a field’s appearance in MS Access. The changes you make to the properties of linked tables will only affect how Access displays and handles data in the linked table. The source table is not changed.
The Visual Basic Access (VBA) language is very versatile and powerful. Usually only users with at least an intermediate understanding of programming utilize VBA. There are various methods to connect to other databases tables using VBA.
Connect to Several Different Database Programs using ODBC
Open Database Connectivity, or ODBC is a common API for database access and can be used to read and add to many database types from web-based applications. The API is needed since there are many database types such as Acces, FoxPro, SQL Server, etc. The ODBC API serves as a common language when dealing with a variety of databases.
Getting Data from an Unsupported Program
You can import data from a program with data stored in an unsupported file format or external database, if it is able to convert, save or export the data to a supported format. Also, if you link or import an external database such as Paradox, dBase or Access the indexes are usually preserved. An example would be that you cannot link or import a MS Works database, but can export MS Works data to dBase IV and import the data to MS Access in order to preserve indexes and field names.
Unsupported programs generally can export delimited file data which can then be linked or imported from Access – although the indexes won’t be preserved.