Connecting to another Database

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 can link to tables from other Microsoft Access databases. For example, you may want to use a table from another Microsoft Access database that is shared on a network drive. This is particularly useful if you want to store all of your tables in one database on a network server, while keeping forms, reports, and other objects in a separate database that is copied among users of the shared database. You can easily split an existing database into two databases, called front-end and back-end databases.

If you open a linked table in design view, it will look very much like a regular Microsoft Access table. Although you can't change the way the linked table and its fields are defined in the external database, you can set the properties that control the way the fields appear in Microsoft Access. Changes that you make to properties for linked tables affect only how Microsoft Access handles and displays data from the linked table. Changes are not made to the source table.

VBA Code

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 (ODBC) is a widely accepted Application Programming Interface (API) for accessing databases. In English, that statement from Microsoft means that you can use ODBC as a medium for reading from and adding to almost any type of database for your web applications. An API is required because there are so many types of database you could be using (FoxPro, MS Access, SQL Server, Paradox...etc.) and in an ideal world you should only need one language for communicating with these databases, hence the ODBC API.

Getting Data from Unsupported Programs

If you have a program whose data is not stored in one of the supported external database or file formats, but the program can export, convert, or save its data as one of these formats, then you can import that data. Additionally, when you import or link an external database format, such as dBase or Paradox, Access usually preserves indexes. For example, you can't import or link a Microsoft Works database directly, but you can export the data from Microsoft Works to a dBase IV (.dbf) database file and then import that data into Microsoft Access to preserve field names and indexes.

In general, most unsupported programs, even those on different operating systems, can export data to a delimited file, which you can then import or link from Microsoft Access, even though you won't preserve indexes.

By Admin at 5 Apr 2016, 18:12 PM



Post a comment

Please correct the following: