There are three types of ODBC DSNs that can be used to create connections to SQL Server databases using CONNX: user, system, or file-based. The configuration of the DSN must be carefully considered whenever a CDD is intended for use by multiple users on multiple machines.
If the ODBC connection is made using the user or system-based DSN, every machine that uses this CONNX Data Dictionary must have the same DSN registered in its ODBC driver manager. For example, if the creator of the COMP_INVEN.CDD has a user DSN "My_inventory" that connects to the SQL Server machine warehouse, then every user of that CDD must have the same DSN "My_inventory" registered on their machine. To enable all users to access a newly added ODBC data source, it is recommended that you use a file-based DSN.
A file-based DSN is not specific to any machine or user and can be made accessible to all. The file-based DSN can be placed on a network share. However, with file-based DSNs the path entered by the user who imported the ODBC tables is stored in the CDD. For example, if the creator of the CDD file COMP_FDINV.CDD enters a file DSN of X:\ACCOUNTING\INVEN.DSN, all users with access to that CDD must be able to access X:\ACCOUNTING\INVEN.DSN.
One convention used to solve this scenario is that all user machines are given access to the same network drive. Another convention is to use a UNC (universal naming convention), such as \\MySERVER\Myshare\INDEV.DSN, which grants users access to the network drive without allocating a specific drive letter.
As with any database connection, if the tables on the database are modified, the CDD may need to be re-imported.
Regardless of DSN configuration, however, the ODBC driver or OLE DB provider must be installed on all client machines accessing the data source except in three-tier scenarios where it must be installed on the middle tier.
To import an existing Microsoft OLE DB provider for SQL Server 7.0 data objects
To import objects from an existing SQL Server 6.x or 7.0 provider data source