Results 1 to 9 of 9
  1. #1
    hannlapp is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    4

    Access shows only #deleted fields

    Hi



    I'm linking sql database into Access 2016 via odbc.

    It shows only #deleted.

    If have deleted the link and linked again, but no success.

    But when I import the table into Access, then it shows ok. Why not working in linking?

    in the table there is over 565.000 records.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What are you linking to - is it a table, a view ?
    What driver are you using ?
    Does the table have a Primary key ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    hannlapp is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    4
    Quote Originally Posted by Minty View Post
    What are you linking to - is it a table, a view ?
    What driver are you using ?
    Does the table have a Primary key ?

    I'm trying to link a table.

    I have tested with two different driver in odbc:

    - ODBC Driver 11 for Sql Server, version 2014.120.5543.11
    - Sql Server version 10.00.16299.15 (came with windows 10, I have)

    Table have Primary key.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Are you using the same connection information for both linking and importing? Have a look at the primary key to see if it is bigint (https://stackoverflow.com/questions/...ing-as-deleted) and consider adding a timestamp field to the SQL table if it doesn't have one.
    I would also try SQL Native Client 11 as the driver, I've found it the best for Access fe/SQL be setups.
    Cheers,
    Vlad

  5. #5
    dtzanak is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    4
    Facing exactly the same problem and still haven't found any solution to it yet.

    Have you managed to solve it?

    Some links that could possibly help you.

    https://learn.microsoft.com/en-us/of...-table-deleted

    https://support.microsoft.com/en-us/...0-9851042e0024

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    try to link field by field. When linking the table you get a choice of fields you want to see. Start choosing only the primary key field(s). If it works, drop the link and try to link the table with some more fields. You can repeat the process till you get the error. This way you can find out which field(s) causes the problem.

  7. #7
    dtzanak is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2023
    Posts
    4
    Dear Noella,

    Thank you for your answer that sounds a really good advice, but I don't know how to this, I didn't even know this is an option.

    The process I follow to create a link of our ERP tables in Access is the following:

    1. External Data -> New Data Source -> From Other Sources -> ODBC Database
    2. Link to the data source by creating a linked table
    3. Select Data Source (I use a machine data source that uses SQL server driver)
    4. A list of ERP tables opens and then I can select one or more tables (entire tables however, there is no option for selecting specific fields of a table)

    Is there an alternative process I can follow to link specific fields of a selected table?

    Thanks a lot!

  8. #8
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by NoellaG View Post
    try to link field by field. When linking the table you get a choice of fields you want to see. Start choosing only the primary key field(s). If it works, drop the link and try to link the table with some more fields. You can repeat the process till you get the error. This way you can find out which field(s) causes the problem.
    I have never seen this as an option?
    You get a list of fields to select to use as an index from if the table doesn't have a primary key set, but you can't pick and choose what fields to include.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    OK, could be it's been a while I did this. What you can do is create a view on SQL server with a few fields of the table and then link the view. Steadily add fields to the view and relink the table until you pinpoint the problem.

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

Similar Threads

  1. Linked Table shows deleted for all fields
    By nancyma in forum Programming
    Replies: 3
    Last Post: 10-01-2023, 11:29 AM
  2. Table shows deleted record but it won't delete
    By khughes46 in forum Access
    Replies: 3
    Last Post: 09-19-2014, 09:35 AM
  3. Deleted Column in Access
    By Khauck in forum Access
    Replies: 4
    Last Post: 04-11-2013, 10:07 AM
  4. delete query produce #deleted in all deleted values
    By learning_graccess in forum Queries
    Replies: 2
    Last Post: 03-31-2012, 07:20 AM
  5. Replies: 4
    Last Post: 06-14-2010, 02:31 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