none
Access 2003 Linked Tables update

    Question

  • Hello,

     

    I am trying to update some SQL-linked tables in my Access database by repoiting the existing linked tables to a new datasource.  The problem is, when I go to select the machine data source where the table sits, I get an error message saying the MS Jet Database can't find the object.  This is because when Access creates the linked table, it replaces the period in the <schema>.<table_name> with an underscore.  So when I go to update the links, it is essentially looking for the new table with the wrong file name.

     

    I have about 80 linked tables to update and I haven't been able to figure a work-around.  HELP PLEASE!

     

    Cheers,

    Josh

    Thursday, June 28, 2007 1:38 PM

Answers

  • According to http://www.microsoft.com/technet/archive/office/office97/reskit/office97/027.mspx

    Renaming Linked Tables

    When Access links a remote table, it prefixes the default table owner ID of the SQL Server to each table name. The period separator between the owner ID and the table name is replaced by an underscore because periods in table names are not permitted by Access. Thus, the names of linked tables no longer correspond to the original table names in your MDB file. The simplest way to correct this is to rename your tables to their original names after linking.





    Friday, June 29, 2007 7:05 AM
    Moderator

All replies

  • According to http://www.microsoft.com/technet/archive/office/office97/reskit/office97/027.mspx

    Renaming Linked Tables

    When Access links a remote table, it prefixes the default table owner ID of the SQL Server to each table name. The period separator between the owner ID and the table name is replaced by an underscore because periods in table names are not permitted by Access. Thus, the names of linked tables no longer correspond to the original table names in your MDB file. The simplest way to correct this is to rename your tables to their original names after linking.





    Friday, June 29, 2007 7:05 AM
    Moderator
  • I am also having a problem with link table updation .......

     

    I have two (A & B) link tables in  Access2003 database to SQLserver 2000.

     

    I can update to Sql server 2000 via linke table A but not via table B ...Table A & Table B are linked to two different tables in SQL Server 2000.When I open link table A in Access I can see all the Insert options enabled.But When I open the link table B thses record insertion options are disable.

     

    Plz Help

     

    Monday, April 14, 2008 10:47 AM