Results 1 to 8 of 8
  1. #1
    cwcadm is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    4

    Linked tables and ODBC connection strings

    Is there any way to create a linked table in Access using an ODBC connection string instead of a DSN?



    I can see the connection string when I hover over a linked table that's been created by using a DSN but I can't seem to find a way of modifying or replacing it.

    Any ideas?

    Thanks!

    Edit: I should probably mention that I'm using Access 2003.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You may find this link helpful.

  3. #3
    cwcadm is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    4
    Thanks for the response!

    Yeah I saw that link too. I was hoping not to do it using code though. I'm not really familiar with VBA, but I can be if that's the only way to do it.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    AFAIK your two choices are DSN or Code.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You may also find this link informative.

  6. #6
    cwcadm is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    4
    Quote Originally Posted by RuralGuy View Post
    AFAIK your two choices are DSN or Code.
    That's what I was afraid of.

    I'll read over that last link and see if I can come up with anything.

    Thanks!

  7. #7
    cwcadm is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    4
    I thought I would update with what I came up with. Unfortunately I couldn't find anything non-programmic, so it involves getting the hands a little dirty.

    Note: The table needs to have been created already using a DSN.

    • In Access hit Ctrl-G to open a Microsoft Visual Basic editor.
    • In the Project window (on the upper left side) right-click on Access9db and choose Insert -> Module
    • Click on the newly created module and enter the following code into the empty code section:

    Code:
    Sub ChangeConnectionString
        Dim Dbs As Database
        Dim Tdf As TableDef
        Dim Tdfs As TableDefs
        Set Dbs = CurrentDb
        Set Tdfs = Dbs.TableDefs
        
        'Loops through list of tables
        For Each Tdf In Tdfs
            
            'Criteria for selecting which table 
            'to change the connection string of
            If Tdf.SourceTableName <> "" Then
                Tdf.Connect =     'INSERT YOUR CONNECTION STRING HERE
                Tdf.RefreshLink 'Saves the changes made
            End If
        Next
    End Sub
    • In the Immediate window below type the function name (ChangeConnectionString) and hit enter
    • Verify in Access that changes have been made by hovering over the linked table to see the connection string.

    Based on this example: Change source of linked tables - Access World Forums

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That's great! Once the code is written, it is easy to automate it by calling it from a CommandButton or by some other means.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to force carriage return between strings?
    By Divardo in forum Reports
    Replies: 1
    Last Post: 05-21-2009, 10:50 AM
  2. ODBC Linked Tables Become Unupdateable
    By dollardeveloper in forum Access
    Replies: 0
    Last Post: 04-08-2009, 07:13 AM
  3. Security and Linked Tables
    By bab133 in forum Security
    Replies: 0
    Last Post: 03-13-2008, 02:11 PM
  4. Strings limited to 255 characters
    By ocordero in forum Programming
    Replies: 4
    Last Post: 08-09-2006, 09:13 AM
  5. Unable to import or link tables through odbc in Access SP2
    By Dave Jenkins in forum Import/Export Data
    Replies: 3
    Last Post: 11-09-2005, 11:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums