MySQL and ODBC - Importing and Linking the Data Source
(Page 4 of 6 )
Given the initial confusion caused by connecting a MS Access database to a ODBC data source, a few clarifications are in order. There are essentially two functions provided by MS Access which are used to associate the MS Access database with the data source, each accessible via the File-->Get External Data submenu:
ImportThe Import option should be used if any data found in the data source will be subsequently used exclusively in MS Access. That is to say that no other applications will have the data source data available to them.
Link TablesThe Link Tables option should be used if the data found in the data source will be updated using applications other than the MS Access. This is likely the choice that the user will want to employ, since it is the intention that MS Access will update the data stored within the MySQL database server in addition to its own internal tables.
Therefore, choose Link Tables from the Get External Data submenu. Doing so will create a "link" between the data source tables and MS Access. Any information updated using the Access Interface (which we’ll create in the next section), will automatically update the MySQL database, making the information immediately available from any application using that data. Choosing Link Tables will result in the familiar looking navigation window to appear. However, nothing will actually be selected in the window. Instead, go to the Files of type drop-down menu located at the bottom of the window, and choose ODBC Databases(). This is illustrated in Figure 1-5.
Figure 1-5: Choosing ODBC Databases()
Doing so will cause yet another window to appear. Choosing the tab entitled Machine Data Source will produce a window very similar to that shown in Figure 1-6. Notice that the widget DSN is included in the data source name listing. Click on the widget DSN and press OK. This will produce one final window, which lists all of the tables found in the widget database, in this case only one, clients. Click on the clients table and press OK. The window will close and the original database window (Shown in Figure 1-4) will reappear, this time including the names of those tables chosen in the window. Next to each table name will be a globe with an arrow pointing to it. This signifies that these tables have been linked to those found in the data source.
Note: The client machine must be connected to the Internet, otherwise it will be impossible for MS Access to update the new database with the information found in the data source tables!
Figure 1-6: Machine Data Source window
Double-click the clients table name, and window will appear displaying the table structure. Notice that the columns are the same as those found within the MySQL clients table created earlier in this tutorial.
Congratulations. An ODBC connection to a MySQL database has been successfully carried out. In the next section, a demonstration of how inserting information into the linked tables via the Access database will cause the MySQL database to be simultaneously updated.
Next: Updating The Database >>
More MySQL Articles
More By W.J. Gilmore
