none
Linked Database with Access Issues

    Question

  • I have an issue where my SQL Server 2012 Express database where when the data is synced one of my tables comes across as either #DELETED, #Name? for all fields or #DELETED for all but one row of data.  All the fields were converted to Text when it was imported.  I am using Access 2010.
    Friday, July 19, 2013 10:56 PM

Answers

All replies

  • See the following recent MSDN threads:

    Access & SQL Server Error Message

    Microsoft Office Access The data has been changed ...

    paying attention to the TimeStamp Field and Bit Field I mentioned in the above threads.  In addition, following the links in my posts in the above threads for additional details in using ODBC-linked Tables sourced from SQL Server database.


    Van Dinh


    • Marked as answer by Josh24_81 Saturday, July 20, 2013 6:43 PM
    • Edited by Van DinhMVP Tuesday, July 23, 2013 7:53 AM Typos
    Saturday, July 20, 2013 12:04 AM
  • What I ended up doing was recreating the table in question and changed some data types.  For instance I was using BigInt for my ID field and changed that to Int, and changed from nChar to Char for another field (these were the only two changes), I then imported the previous values and created the link again and the data came through fine.
    Saturday, July 20, 2013 6:42 PM
  • Yes, BigInt is another problematic data-type if you use Access client.  I tend to use varchar or nvarchar rather than char or nchar but I don't think ncjar would create any problem with Access client.

    Did you say "import"?  Once imported, you have another copy of the data independent of data already stored in the SQL Server database which gets out of synch very quickly.  The normal set-up is to use ODBC-linked Tables sourced from the SQL Server Express database.  This way, your Access Front-End has access to the SQL Server data and can update the data without needing to store the data twice and there is is no need to synch the data from SQL Server to local Access Tables regularly.

    Make sure you have the (SQK Server) TimeStamp Field in each and every SQL Server Table that is used as the source for an Access ODBC-linked Table.  This will prevent the spurious problem with "Write conflict" as well as improve the efficiency in updating data through the Access client (Front-End).


    Van Dinh


    • Edited by Van DinhMVP Saturday, July 20, 2013 10:21 PM Addendum
    Saturday, July 20, 2013 10:16 PM