Posted by Trevor Best on 10/31/05 03:05

Neil wrote:
> I have an Access 2000 MDB file with a SQL 7 back end. I have a main table
> with 50,000 records; and I have a selections table with 50,000 records for
> each machine that uses the database (about 25-50). This allows each user to
> have their own set of selections.
>
> The selections table has three fields: ID (int), Sel (bit), MachName
> (varchar). ID and MachName comprise the primary key.
>
> I have a view that combines the main table and the entries for the
> selections table for the current machine (SQL below). The view works fine
> when opened in EM and QA. And if I create a pass-through query from my
> Access MDB file, the results are displayed fine.
>
> However, if I link the view to the Access MDB file, I get "#Deleted" in
> every field of every record (which seems to indicate that the records were
> there and then they were gone). However, if I hard-code the machine name
> into the same view instead of using HOST_NAME and then relink the view to
> the MDB file, the linked view opens fine. Only when I use HOST_NAME as a
> parameter in the view is there a problem with it.
>
> Anyone have any idea what's going on here, or have heard of any issues with
> HOST_NAME and ODBC linked objects? SQL for the view is below.

I've not had any problems with Host_name() as a parameter, as the only
records I retrieve are for my particular machine there is very little
point in returning that column so I never ask the server for it as I
know the value already.

A couple of things you need to know about Access and SQL Server, firstly
bit fields should be made NOT NULL and default to 0 as Access interprets
these as Jet Yes/No columns and treats them accordingly so it expects a
value to be there (In Access/Jet, a Yes/No column cannot be null and
defaults to 0). The results otherwise can be unpredictable.

Secondly, if you have any floating point columns in your data (including
datetime as these are stored as floating point numbers) then you most
probably need a timestamp column as floating point errors can cause the
#Deleted condition you describe.

[Back to original message]