We have users working at various geographically separated sites. How can we share supporting data that get updated regularly like symbol catalogs and parts databases?
The short answer to this question is to keep local copies of the symbol catalogs and parts databases at each site, since accessing these items over a wide area network is known to result in poor performance.
With such a strategy, the "master" catalogs and parts database are periodically copied to the various sites, depending on how often they are updated. This is often done nightly/off hours when no user is connected to the catalog databases or parts databases that would be getting overwritten.
If multiple users create content, then a "librarian" is chosen and the creators send their content to the librarian who then updates the master catalogs and parts database. This article discusses this strategy.
*Import and Export Data is an application included with recent versions of SQL Server. This application can transfer data between various database formats, including Access (.mdb), Excel (.xls), and SQL Server.
An alternative to the above is to have remote users use Promis.e or Bentley Substation via Citrix and have all users connect to the master catalogs and parts database. Recent versions of Promis.e and Bentley Substation can be run on Citrix, but this requires knowledge of how to publish applications on Citrix and may require additional hardware (Citrix servers).
The master catalogs and parts databases would be copied to the sites periodically. Windows task scheduler could be used to schedule the execution of a batch file that would perform the copy.