#DELETED in Linked SQL

Hi,
Running Access 2002 frontend, with SQL2000 backend. When I link the
tables, 3 tables appear in Access with all fields loaded with #DELETED.
The data is fine in SQL. I can *import* them back into Access out of
SQL and they are OK, but cannot link. (This data is originally being
imported into SQL from Access).
At one point, I re-created the tables using a make table, then imported
that and it was OK. But on subsequent imports (while cleaning the
data), it has gone back to #DELETED again.
These tables are large, all over 250,000 records, but surely that's not
a problem for SQL.
Any ideas?
EricEric,
It's not a SQL Server issue...you can run into that when
linking tables to other data sources as well. When it's an
entire table, It can be caused by several things such as
using a float as the index or as part of the index or
having nulls as values in part of the index. There was in
issue similar to this when using Bigints with some data
sources as well which wouldn't map correctly but was
corrected in one of the Jet service packs.
ODBC is key-set driven and fetches are generally done in two
steps based upon the unique index of the table where first
it grabs the index and then it goes back, looks for the
index and gets the rest of the row based on the index. If
it can't find the index or gets 'confused' on the index in
the second step, it will assume the record has been deleted.
So it could be a few different things. Make sure you are
using the latest Jet service packs and check what is being
used for indexes. There used to be some info on the issue in
the Access help file but I wouldn't have any idea where to
find it in there since they Answer Wizarded the help files
and made things harder to find. I usually just do a google
search with microsoft.com as the domain to find help
articles on office issues. Other than that, you may want to
post to one of the Access newsgroups.
-Sue
On Tue, 15 Feb 2005 12:09:32 -0500, elf
<eric@.northstarcc.com> wrote:

>Hi,
>Running Access 2002 frontend, with SQL2000 backend. When I link the
>tables, 3 tables appear in Access with all fields loaded with #DELETED.
> The data is fine in SQL. I can *import* them back into Access out of
>SQL and they are OK, but cannot link. (This data is originally being
>imported into SQL from Access).
>At one point, I re-created the tables using a make table, then imported
>that and it was OK. But on subsequent imports (while cleaning the
>data), it has gone back to #DELETED again.
>These tables are large, all over 250,000 records, but surely that's not
>a problem for SQL.
>Any ideas?
>Eric|||Thanks. I *am* using bigint in the identity fields of these tables,
because I've had problems in the past using just int with imported
autonumbers. (We've been upgrading several clients, some of whom were
using Access Replication (ugh!...at least prior to 2000), which can
generate some really big autonumbers) I've been careful about nulls in
keys, so bigint is where I'll look first.
BTW, we have developed in Access for years, only fairly recently started
using SQL as a backend. Access is not quite as picky about prime keys,
so when SQL complains about our Access prime, we just move it to an
alternate, and use an identity as prime.
Sue Hoegemeier wrote:

> Eric,
> It's not a SQL Server issue...you can run into that when
> linking tables to other data sources as well. When it's an
> entire table, It can be caused by several things such as
> using a float as the index or as part of the index or
> having nulls as values in part of the index. There was in
> issue similar to this when using Bigints with some data
> sources as well which wouldn't map correctly but was
> corrected in one of the Jet service packs.
> ODBC is key-set driven and fetches are generally done in two
> steps based upon the unique index of the table where first
> it grabs the index and then it goes back, looks for the
> index and gets the rest of the row based on the index. If
> it can't find the index or gets 'confused' on the index in
> the second step, it will assume the record has been deleted.
> So it could be a few different things. Make sure you are
> using the latest Jet service packs and check what is being
> used for indexes. There used to be some info on the issue in
> the Access help file but I wouldn't have any idea where to
> find it in there since they Answer Wizarded the help files
> and made things harder to find. I usually just do a google
> search with microsoft.com as the domain to find help
> articles on office issues. Other than that, you may want to
> post to one of the Access newsgroups.
> -Sue
> On Tue, 15 Feb 2005 12:09:32 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>|||Your welcome and yeah...I'd look at the bigint first.
You will find that Access is not as strict about some things
compared to SQL Server. It's just the nature of things and
happens with all different database vendors. Even SQL Server
has some things it lets you get away with that really isn't
allowed by standards. And every vendor has their own
extensions of SQL which further complicates things a bit.
-Sue
On Tue, 15 Feb 2005 14:28:42 -0500, elf
<eric@.northstarcc.com> wrote:
[vbcol=seagreen]
>Thanks. I *am* using bigint in the identity fields of these tables,
>because I've had problems in the past using just int with imported
>autonumbers. (We've been upgrading several clients, some of whom were
>using Access Replication (ugh!...at least prior to 2000), which can
>generate some really big autonumbers) I've been careful about nulls in
>keys, so bigint is where I'll look first.
>BTW, we have developed in Access for years, only fairly recently started
>using SQL as a backend. Access is not quite as picky about prime keys,
>so when SQL complains about our Access prime, we just move it to an
>alternate, and use an identity as prime.
>
>Sue Hoegemeier wrote:
>|||FYI, everyone. Changing the Bigint to int solved my problem immediately.
This may cause me some problems when I go to upsize my next client, who
was using replication id's...guess I may just have to re-number the
lookup tables and their associated tables.
That's the life, I guess.
Thanks, Sue.
Eric
Sue Hoegemeier wrote:
> Your welcome and yeah...I'd look at the bigint first.
> You will find that Access is not as strict about some things
> compared to SQL Server. It's just the nature of things and
> happens with all different database vendors. Even SQL Server
> has some things it lets you get away with that really isn't
> allowed by standards. And every vendor has their own
> extensions of SQL which further complicates things a bit.
> -Sue
> On Tue, 15 Feb 2005 14:28:42 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>|||Glad to hear it's resolved...thanks for posting back Eric.
Another thing...I'd work at testing with the latest Jet
service pack. As I posted earlier, there were issues with
mapping Bigint data types that were corrected in one of the
Jet service packs. If you can resolve that and still use
Bigints then you don't have to worry about the next client
and how to address the issue.
-Sue
On Tue, 15 Feb 2005 21:39:19 -0500, elf
<eric@.northstarcc.com> wrote:
[vbcol=seagreen]
>FYI, everyone. Changing the Bigint to int solved my problem immediately.
>This may cause me some problems when I go to upsize my next client, who
>was using replication id's...guess I may just have to re-number the
>lookup tables and their associated tables.
>That's the life, I guess.
>Thanks, Sue.
>Eric
>Sue Hoegemeier wrote:|||Yeah, I'm going to look into that, Sue. My immediate concern was to get
the conversion done and do my testing before my (tight) deadline. It
had already cost me a couple days of bloody forehead<g>.
Eric
Sue Hoegemeier wrote:
> Glad to hear it's resolved...thanks for posting back Eric.
> Another thing...I'd work at testing with the latest Jet
> service pack. As I posted earlier, there were issues with
> mapping Bigint data types that were corrected in one of the
> Jet service packs. If you can resolve that and still use
> Bigints then you don't have to worry about the next client
> and how to address the issue.
> -Sue
> On Tue, 15 Feb 2005 21:39:19 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>