#DELETED#

I know this is poor practice but I wanted to append 3 records of data to a SQL table. I have a link to that table in MS Access 2000. I open up the table in Access , scroll to the end, and paste the 3 records into the table. Immediatly the results of that
process are 3 lines of #DELETED# until I refrexh the table ( close then re-open). My question is why does it do this as I expect to see the new record immediatly after I have pasted them?
Any help/ explanation would be greatly appreciated
Does the table have a primary key?
Rand
This posting is provided "as is" with no warranties and confers no rights.

#DELETED#

I know this is poor practice but I wanted to append 3 records of data to a S
QL table. I have a link to that table in MS Access 2000. I open up the tabl
e in Access , scroll to the end, and paste the 3 records into the table. Imm
ediatly the results of that
process are 3 lines of #DELETED# until I refrexh the table ( close then re-o
pen). My question is why does it do this as I expect to see the new record
immediatly after I have pasted them?
Any help/ explanation would be greatly appreciatedDoes the table have a primary key?
Rand
This posting is provided "as is" with no warranties and confers no rights.

#DELETED#

I know this is poor practice but I wanted to append 3 records of data to a SQL table. I have a link to that table in MS Access 2000. I open up the table in Access , scroll to the end, and paste the 3 records into the table. Immediatly the results of that process are 3 lines of #DELETED# until I refrexh the table ( close then re-open). My question is why does it do this as I expect to see the new record immediatly after I have pasted them?
Any help/ explanation would be greatly appreciatedDoes the table have a primary key?
Rand
This posting is provided "as is" with no warranties and confers no rights.

#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?
Eric
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:

>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:
>
>

#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:
>
>

#DELETED displayed in all cells

I have a SQL 2000 database and I'm using Access 2003 to gain access to the
tables. All cells contain #Deleted.
What am I doing wrong?
TIA
Jon
Jon Paskett wrote:
> I have a SQL 2000 database and I'm using Access 2003 to gain access to the
> tables. All cells contain #Deleted.
> What am I doing wrong?
> TIA
> Jon
>
Does it show fine when you use Access 2000 with the database?
Anyway, most probably you have problems with primary keys - i had it couple times when i was using a
table from ODBC connection and primary key was set to the column of UNIQUEIDENTIFIER mssql type.
When i added an INT index column and set it as a primary key, everything works fine!
Also try to link those access 2000 table to a blank database created in Access 2003.
Let me know how it works!
Also if you post what columns (types) do you have in the table, i can have a look.
WYGL,
Andrey

#DELETED displayed in all cells

I have a SQL 2000 database and I'm using Access 2003 to gain access to the
tables. All cells contain #Deleted.
What am I doing wrong?
TIA
Jon
Jon Paskett wrote:
> I have a SQL 2000 database and I'm using Access 2003 to gain access to the
> tables. All cells contain #Deleted.
> What am I doing wrong?
> TIA
> Jon
>
Does it show fine when you use Access 2000 with the database?
Anyway, most probably you have problems with primary keys - i had it couple times when i was using a
table from ODBC connection and primary key was set to the column of UNIQUEIDENTIFIER mssql type.
When i added an INT index column and set it as a primary key, everything works fine!
Also try to link those access 2000 table to a blank database created in Access 2003.
Let me know how it works!
Also if you post what columns (types) do you have in the table, i can have a look.
WYGL,
Andrey

#Deleted appears in all SQL2000 linked tables in Access2003

I have a database on a SQL2000 server that I am trying to connect for viewin
g
in Access 2003 with the client running Windows 2000. I have created the
connection, but when I try to view the tables, I get "#Deleted" in all the
rows, for both tables. I installed the latest MS Jet Driver to no avail. I
also stumbled upon a posting stating that Access does not recognize BigInt a
s
a UID. Is this correct? Is there a workaround without having to mod the SQ
L
tables? I can open the tables fine with Sun's OpenOffice. Any help greatly
appreciated.It's an Access issue that 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. And there
was an issue when using Bigints with some data sources as
well which wouldn't map correctly but that was corrected in
one of the Jet service packs. Make sure you have the latest
service pack for Jet.
-Sue
On Tue, 11 Apr 2006 05:39:01 -0700, Seth
<Seth@.discussions.microsoft.com> wrote:

>I have a database on a SQL2000 server that I am trying to connect for viewi
ng
>in Access 2003 with the client running Windows 2000. I have created the
>connection, but when I try to view the tables, I get "#Deleted" in all the
>rows, for both tables. I installed the latest MS Jet Driver to no avail.
I
>also stumbled upon a posting stating that Access does not recognize BigInt
as
>a UID. Is this correct? Is there a workaround without having to mod the S
QL
>tables? I can open the tables fine with Sun's OpenOffice. Any help greatl
y
>appreciated.