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