Method 1 - Linking to PDMS Data through ODBC (recommended)

This example covers the steps necessary to create a linked table of PDMS data through a previously created ODBC resource. Linked tables thus created can be used to write data back to PDMS as well as fetch data.

The ODBC resource must point to the SQL Server database in which our table definitions have been configured (in our case the MASTER database, in which the PDMS data views have been created by SQLDirectConfig). Refer to Setting up SQL Server for information on how to create the ODBC resource.

Note:
It is also possible to create a copied (unlinked) table. The procedure is similar to that given below, but start with File>Get External Data>Import…

1. Start Microsoft Access.

2. Open an existing database, or create a new database, as appropriate. Select File>Get External Data>Link Tables… The Link dialog box will appear.

3. From the "Files of type:" pull-down list, select ODBC Databases( ). The Select Data Source dialog box will appear:

   

   The list of data provider applications may differ from that shown above.)

4. Select the PDMS ODBC data source you created earlier (refer to Setting up SQL Server). If you created a System DSN (rather than a File DSN) it will be on the "Machine Data Source" tab (rather than the "File Data Source" tab). Click OK; the Link Tables dialog box will appear:

   

5. The list in the Tables tab will show the available PDMS tables (as defined using the SQL Direct Table Designer) as well as a number of SQL Server system tables which can be ignored. Select the required table(s), then click OK.

6. The Link Tables dialog box will change its appearance and the Select Unique Record Identifier dialog box will appear:

   

7. The Select Unique Record Identifier dialog box asks you to select a field (or fields) from the selected table that will uniquely identify each record. Choose REFNO, and click on OK.

The requested table(s) will now appear within the Tables tab of the Database dialog box in Access. Double-click on the appropriate table name to view the data in tabular form.

Note:
Data in the output grid corresponding to updatable attributes in PDMS can be updated by changing the value(s) and moving the text cursor off the row. In order for this to work, however, you must have started the Microsoft Distributed Transaction Coordinator (DTC) through the SQL Server Service Manager tool.