Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to distribute an Access application which uses linked tables
Access Environment
I have long puzzled over how Access resolves connecting to linked tables. I have always used an ODBC System DSN to link a table to the source. This has the disadvantage that the DSN must be set up on all client machines which will be using the Access file, a big overhead when the file itself is central.
The puzzle for me has been that the linked table contains all of the connection information it needs. This can be viewed by choosing Design mode of a linked table (ignoring the warning) and then choosing Properties. I am certain that if an attempt is made to open the table on a machine which does not have the DSN set up, the ODBC call will fail. If I change the information inside the DSN, however, the ODBC call will not necessarily use the updated information. I have not been able to find out at what point Access will verify the information.
The solution I have found is to base the linked table on a file DSN. In this case, all of the connection information from the file is stored in the table link, but there is no reference to the filename. Once the table has been linked, the file DSN is no longer used, so the table can be opened on any machine.
To achieve this:
- Start - Settings - Control Panel - Data Sources (sometimes 32 Bit ODBC or other).
- File DSN
- Add
- Select a driver
- You will be prompted for a file name for the DSN file. As mentioned, this file can be located anywhere as it is only used for the initial link.
- Based on the driver, you will be prompted for parameters for the connection. This will create the DSN file.
- In Access, create a linked table choosing the File DSN tab in the ODBC dialogue box.
As mentioned above, you can check the connection parameters for the linked table by choosing Design mode of the table (ignoring the warning) and then choosing Properties.
Danny
![[rockband]](/data/assets/smilies/rockband.gif "[rockband] [rockband]")
![[wiggle]](/data/assets/smilies/wiggle.gif "[wiggle] [wiggle]")
The puzzle for me has been that the linked table contains all of the connection information it needs. This can be viewed by choosing Design mode of a linked table (ignoring the warning) and then choosing Properties. I am certain that if an attempt is made to open the table on a machine which does not have the DSN set up, the ODBC call will fail. If I change the information inside the DSN, however, the ODBC call will not necessarily use the updated information. I have not been able to find out at what point Access will verify the information.
The solution I have found is to base the linked table on a file DSN. In this case, all of the connection information from the file is stored in the table link, but there is no reference to the filename. Once the table has been linked, the file DSN is no longer used, so the table can be opened on any machine.
To achieve this:
- Start - Settings - Control Panel - Data Sources (sometimes 32 Bit ODBC or other).
- File DSN
- Add
- Select a driver
- You will be prompted for a file name for the DSN file. As mentioned, this file can be located anywhere as it is only used for the initial link.
- Based on the driver, you will be prompted for parameters for the connection. This will create the DSN file.
- In Access, create a linked table choosing the File DSN tab in the ODBC dialogue box.
As mentioned above, you can check the connection parameters for the linked table by choosing Design mode of the table (ignoring the warning) and then choosing Properties.
Danny