Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jul 2006
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Connect to SQL Server (Access 2003)

    I have connected to an SQL Server Database using Access by setting up an ODBC Data Source and using Linked Tables in Access.

    When I open up most tables in Access it is fine and I can see all data.

    However, when I open up certain tables I receive the feilds names at the top but every record is filled with "#Deleted"

    I can see the data of this table using SQL Server Enterprise Manager.

    Why am I getting #Deleted in Access? Any thoughts?

    The table I am refering to holds a large amount of data and is updated fairly often if that helps.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connect to SQL Server (Access 2003)

    This can happen if the SQL Server table doesn't have a primary key, or if it does but Access cannot handle it.

    Make sure that you have the latest version of the Jet engine (see How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine) and of the ODBC driver for SQL Server (see Download details: MDAC 2.8).

  3. #3
    Lounger
    Join Date
    Jul 2006
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server (Access 2003)

    Im pretty sure these are OK
    Are there any other possibilities?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connect to SQL Server (Access 2003)

    This is outside my field. I hope someone else can help.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Connect to SQL Server (Access 2003)

    The fact that you can see in the data in SQL Server itself eliminates corrupted records as the problem. However, you need to check that the problem tables have primary keys and don't have field names that are reserved words in Access. Don't just assume the tables are OK because you can see them in SQL Server. The problem is in the ODBC connection, so you need to be sure that the tables are legal in Access. By the way, these are tables, right, not SQL temp tables or restricted views or stored procedures?
    Charlotte

  6. #6
    Lounger
    Join Date
    Jul 2006
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server (Access 2003)

    Yes these are tables.
    What can I do if the feild names are reserved words?
    Is there any way to link the table and change the feild name - or something like that?

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server (Access 2003)

    Hi, I too am having similar problem with this.
    From the above posts I went back to it and:
    in SQL server manager created a new view based on the table I required - but I added only one of the fields I needed.
    Then went back into Access and relinked the table.
    On opening the newly linked Table I was able to see the data instead of the columns and rows of #Deleted (how frustrating is that!!!?)

    I went back into SQL manager and modified the view to include a second column and then relinked the table in Access - so far so good and I intend to continue modifying and relinking until I find the column which is giving the problem. From there - I will try and enter an Alias for the column name

    Does that sound like a plan?

    Incidentally - if I use MSQuery (MS Excel) then I get all of the data with no "'#Deleted" values.

    HTH

    Alan
    Cheshire

  8. #8
    Lounger
    Join Date
    Jul 2006
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server (Access 2003)

    Thanks for that - it worked.
    Further to what you mentioned though, I created a new view in SQL server manager and creted just a select all (SELECT * FROM Table) as the view.
    When I linked the view in Access I was able to see the data if I didnt choose a PK or 'unique identifier' as they called it.
    I tried it again and selected a PK and all I saw was #Deleted.

    Obviously it has something to do with the way Access handles the PK as someone mentioned previously in this post.
    Thanks agian.

  9. #9
    Lounger
    Join Date
    Jul 2006
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server (Access 2003)

    Something else I found is that if you create a report in Access based on the data of the table where all values show #Deleted - the report will show the values...weird!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •