INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Environment

How to distribute an Access application which uses linked tables by DannyLondon
Posted: 4 Nov 03

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

            

Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close