Method 2 - Linking to PDMS Data through OLE DB

This example covers the steps necessary to create linked tables of PDMS data directly through the PDMS OLE DB Provider / SQL Server without using ODBC.

Unfortunately, linked tables created in this way cannot be used to write data back to PDMS, due to apparent limitations in Access. If write access is important to you, please use Method 1 (above).

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 (version 2002 or later), and also make sure that SQL Server is running.

2. Go to Access File menu, select New, and create a Project using new data…

3. In the database wizard presented, select the appropriate SQL Server (i.e. the one on the machine where SQL Direct is installed) and enter the SQL Server login credentials. Click Next and then Finish.

4. Go to the File menu again, and select Get External Data->Link Tables…

5. Make sure Linked Server is selected and click Next.

6. Select the data source. This is similar to the procedure described in Method 2 - Linking to PDMS Data through OLE DB for Excel. If you have a saved data source that you want to use, select it from the list, click Open and proceed to step 12. If you don't have a data source saved, click the New Source… button and proceed to step 7.

7. Make sure Other/Advanced is selected, and click Next.

8. Select PDMSProv OLE DB Provider 12.0 from the list and click Next.

9. Fill in the Data source - the connection settings name you saved in SQL Direct Configuration, your User name and Password (deselect the "blank password" option), and select "Allow saving password" if you wish. Then click OK.

10. You are now offered the option to select your database - there should only be one so just click Next.

11. Fill in a file name for the connection (if you wish, or just use the default) and again select the "save password in file" option if you wish. Then click Finish.

12. You can now select which table(s) you wish to link to the current project (or all tables if you wish). Make your selection and click Finish.

Access will now create the linked tables. Double-click on the appropriate table name to view the data in tabular form.

The linked tables created in this way are once again data views, very similar to the ones that SQL Direct Configuration creates, except that Access creates a separate database for each Access project in which to place the views.