Results 1 to 9 of 9
  1. #1
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904

    Changing Tables Front-End DB are Linked To

    We currently have a split Access database. We need to convert the back-end, so that all the data tables will be stored on SQL Server instead of in the back-end Access database directly. I was able, through the Upsizing Wizard, to move all my data tables to SQL Server.



    Now, I am trying to re-point the table links in my Access front-end database to look at the SQL server database instead of the old Access tables. I set up an ODBC connection to the new SQL database, but I cannot figure out how to re-point what those tables links are pointing to (through the Linked Table Manager). The Linked Table Manager does not seem to show the SQL server or ODBC connections.

    How do I do this?

    Thanks

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am working on doing the same thing.
    I did this on my home setup..... I think I used "ODBC Database" instead of the Linked Table Manager. I just followed the prompts (and did a lot of )
    Sorry I can't be more specific... I have slept since I did this.


    All of the tables I linked to had the prefix "dbo_". There is code in the forum to remove the "dbo_" ...search "dbo" and June7..

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Found this in another group --hope it's useful

    Convert the BE from Access to either SQL Express or MySQL.
    A company called Bullzip <
    http://www.bullzip.com> offers a
    pair of great tools that convert Access databases to either MS SQL or MySQL
    -- and they are free. Once the database is converted, set up a new instance
    of the FE that uses ODBC to connect to the new database.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I think I used "ODBC Database" instead of the Linked Table Manager.
    I know how to add new tables via ODBC (External Data -> Import -> More -> ODBC Database), but I don't think that will work (it would be fine if I was creating a new database, but not updating an existing one).

    I have lots of Queries, Forms, and Reports which use the current tables in the database. So I need to changed what the existing tables are linked to in order to ensure that all those existing objects continue working, but reflect the new data connection.

    Once the database is converted, set up a new instance
    of the FE that uses ODBC to connect to the new database.
    That is precisely what I am trying to figure out how to do. I either need to update the current links, or break the current links and establish new ones (and somehow get all my other objects to recognize/use them). Its not something I want to re-do manually on each object - we have almost 100 queries and about 50 reports.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have code that checks if the linked tables are available - if not then up pops a dialog box to select where the BE is located and relinks the tables.
    I don't think the code (as is) will work with ODBC linked tables.

    My plan is to get one table ti relink, then modify the code to match the rest of the tables (about 30).

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is how I just relinked tables:
    (You will/should delete the linked tables to the Access BE)

    OK, from the FE dB

    In the ribbon:
    ------------
    ExternalData
    ODBC Database
    Select Link to the dataSource (bottom radio button)
    Click OK
    Click on tab "Machine Data Source"
    Click New

    Select ->System Data Source (bottom radio button)
    Click Next
    goto bottom of list box - Select SQL Server
    Click Next
    Finish

    New dialog box:
    ------------------
    Name >> can be anything
    Desctiption>> can be anything
    Server - name of the instance where your database is. For me "GX620\SQLExpress" - no quotes (SQL Express installed on local computer)
    Click Next
    With Windows NT should be selected
    Click Next
    Check "Change to the default Database. For Me ->"TestData" (NOT master)
    Click Next
    Click Finish
    Click "Test Data Source"
    If successful, Click OK
    Click OK

    New dialog box
    ------------------
    Now Pick the tables to link to.


    And, Yes, I did have to call a friend for help.

  7. #7
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I didn't realize that if you have linked tables (to Access tables), you cannot easily update those linked tables to look at SQL tables. I was hoping that was possible, but that does not appear to be the case.

    I deleted the linked tables, and then re-added them (referencing the SQL tables, using the External Data menu), and that worked.

    Thanks.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Doh!
    I just Googled "vba code to relink odbc tables" and the first site was

    Relink ODBC tables from code
    http://access.mvps.org/access/tables/tbl0010.htm




    Lots of other links with other (some shorter) examples....

  9. #9
    juttt45 is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2015
    Posts
    1
    If not then up pops a dialog box to select where the BE is located and relinks the tables. I don't think the code (as is) will work with ODBC linked tables. ???

    __________________
    GuL

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

Similar Threads

  1. Replies: 4
    Last Post: 11-22-2013, 11:20 AM
  2. Load a Linked table into front end when switchboard starts
    By smeghead67 in forum Import/Export Data
    Replies: 5
    Last Post: 02-08-2013, 03:32 AM
  3. Replies: 5
    Last Post: 11-13-2012, 12:16 PM
  4. Replies: 5
    Last Post: 02-02-2012, 06:42 PM
  5. Linked Tables and changing filename
    By a2music in forum Database Design
    Replies: 1
    Last Post: 02-28-2011, 03:52 PM

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