Can't link to MS SQL Svr 2005 table (#deleted)

[Paul Kraemer]

Hi,

I have a table in a MS SQL Server 2005 database. I have created an ODBC
(system) datasource that points to this database. When I click "Test
Datasource", the test completes successfully.

I want to create a link to my table in this database in Access 2007. I go
to External Data | ODBC Datasource, and I choose "Link to data source by
creating a linked table". I select my datasource, select my table, and the
linked table seems to be created with no problem. When I double-click on my
table to open it, however, it returns the exact number of rows that exist in
my SQL server table, but every value in every column is listed as "#Deleted".

As a test, deleted my linked table and went through the same process, but
this time I chose "Import the source data into a new table in the database".
I went through the same steps of selecting my datasource and selecting my
table. Doing it this way, I get my imported table and it contains all of the
data.

If anyone can give me an idea what might be preventing me from seeing my
data when I link to this table, i would really appreciate it.

Thanks in advance,
Paul

 

[Mark A. Sam]

Paul,

I am linked to a remote SQL server (2000) without any problems. Did you
apply the
service pack to Office2007? It solved a lot of problems I was having when I
first installed Office2007.

God Bless,

Mark A. Sam

 

[BigHair101]

I'm using Access 2003 using ODBC to link to an SQL 2005 database and I
receive the same #Deleted entry in each field.

Did you ever find an answer to this? I could sure use one.

Thannks.

 

[Douglas J. Steele]

Take a look at http://support.microsoft.com/kb/128809

While it doesn't mention anything newer than Access 97, I believe it still
applies.

 

[David W. Fenton]

I'm using Access 2003 using ODBC to link to an SQL 2005 database
and I receive the same #Deleted entry in each field.

Did you ever find an answer to this? I could sure use one.

Does your SQL Server table use a BIGINT for its primary key? If so,
this is to be expected. If you need the workaround, let me know and
I'll post it (it's not all that complicated).

 

[BigHair101]

David,

Yes, as a matter of fact the SQL database does use bigint for the primary
key. I thought it may have something to do with it, but didn't know what to
do about it, other than not "linking" but using the "import" option instead.
The import works, even though the database is relatively small, I felt that
linking was a better option.

Your suggestions are greatly appreciated. Please post them.

 

[BigHair101]

Thanks Douglas,

I 'll a look at that post.

Take a look at http://support.microsoft.com/kb/128809

While it doesn't mention anything newer than Access 97, I believe it still
applies.

 

[David W. Fenton]

Yes, as a matter of fact the SQL database does use bigint for the
primary key. I thought it may have something to do with it, but
didn't know what to do about it, other than not "linking" but
using the "import" option instead. The import works, even though
the database is relatively small, I felt that linking was a better
option.

Importing won't work if you BIGINT values exceed the limits of Jet's
Long Integer field.

Anyway, the solution is:

1. create a view on the server that uses CAST to convert the value
of the BIGINT field to VARCHAR.

2. use this view editing existing data for read-only functions, such
as reporting.

3. for creating a new record, use the linked table directly. It will
not cause any problems, as long as you don't try to display the
BIGINT field itself.

 

[BigHair101]

David,

Thank you for your help. I will use your suggestions.