locked
Linked table. RRS feed

  • Question

  • So I have a linked table called 'clients'. The table is supposed to be linked to a MSSQL database which also holds a table called 'clients'. I go into my Access 2007 database and open the table 'clients' and change the first entry of the first field from '1ST' to '2ND'. I go back to the linked table manager and synchronize the table 'clients'. Well when I go into the MSSQL database and open 'clients' I am still seeing '1ST'. In other words nothing seemed to have synchronized. I think maybe there must be something that I have not understood yet about how this process works. Does this sound strange to anyone?

     

    Thank you!

    Sunday, June 12, 2011 7:17 AM

Answers

  • It sounds like you can change a value from the Access side, close the
    table, reopen it and see your new value.  But on the SQL Server side,
    you still see the old value.
     
    The simplest explanation is that your linked table in Access is not
    pointing the same server/database/table you are looking at in SQL
    Server.
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     

    Armen Stein, Access MVP J Street Technology www.JStreetTech.com
    • Marked as answer by S.e.p.y Sunday, June 12, 2011 6:50 PM
    Sunday, June 12, 2011 6:29 PM
  • Hi,
    also, be sure that you are making changes in a linked tabled. It should have a ‘globe’ image near its name in the Access Navigation Pane rather than a standard
    table image. And if you didn’t make any changes while upszing to SQL Server, it should be named as dbo_clients.
    You wrote:
    which also holds a table called ‘clients’
    It is exactly SQL Server database which holds this table. In Access you just have a link to it. So, ‘also’ is not a very correct word here.
    Also, when you are saying ‘open MSSQL database’, do you mean you are using SQL Server Management Studio?

    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by S.e.p.y Sunday, June 12, 2011 6:50 PM
    Sunday, June 12, 2011 6:43 PM

All replies

  • Be sure that the table, in MS SQL Server, has a primary key.

     

    To update a value from a linked (and updateable) table, just open the link table into data view, change the data, and change of record. NO NEED to resynch anything, bu be sure you change of record, or somehow explicitly save the modification, as you would do for a standard natvie Jet table.

    Sunday, June 12, 2011 12:54 PM
  • I have made sure that the table 'clients' has a primary key and it does. The primary key is call 'ctlClientCd' and is a navchar(50). I also made sure that the changes I made in Access 2007 were saved and they were. Still though the MSSQL is not reflecting any changes. The primary key is not a unique index though. Does that matter?
    • Edited by S.e.p.y Sunday, June 12, 2011 5:50 PM new information
    Sunday, June 12, 2011 5:45 PM
  • It sounds like you can change a value from the Access side, close the
    table, reopen it and see your new value.  But on the SQL Server side,
    you still see the old value.
     
    The simplest explanation is that your linked table in Access is not
    pointing the same server/database/table you are looking at in SQL
    Server.
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     

    Armen Stein, Access MVP J Street Technology www.JStreetTech.com
    • Marked as answer by S.e.p.y Sunday, June 12, 2011 6:50 PM
    Sunday, June 12, 2011 6:29 PM
  • Hi,
    also, be sure that you are making changes in a linked tabled. It should have a ‘globe’ image near its name in the Access Navigation Pane rather than a standard
    table image. And if you didn’t make any changes while upszing to SQL Server, it should be named as dbo_clients.
    You wrote:
    which also holds a table called ‘clients’
    It is exactly SQL Server database which holds this table. In Access you just have a link to it. So, ‘also’ is not a very correct word here.
    Also, when you are saying ‘open MSSQL database’, do you mean you are using SQL Server Management Studio?

    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by S.e.p.y Sunday, June 12, 2011 6:50 PM
    Sunday, June 12, 2011 6:43 PM
  • That is exactly what happened. I thought my table was a linked table but it was not. Now I realize there is a globe image for linked tables.
    Sunday, June 12, 2011 6:50 PM