Posted by Sylvain Lafontaine on 10/31/05 06:15

One possible cause for seeing things like #deleted is a missing primary key
or Access is not able to determine which field is the primary key.

You have said in your first post that you have defined a composite primary
key on two field. This is quite possibly the source of your problem: you
should try replacing it with a single field primary key. From past
experience, Access seems to have a lot of trouble with linked tables or
views having composite primary keys.

Also, take a look at the following article; in case it might help you:
http://support.microsoft.com/kb/q209123/

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Neil" <nospam@nospam.net> wrote in message
news:hFf9f.3901$yX2.1657@newsread2.news.pas.earthlink.net...
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:uQV0$Ra3FHA.2432@TK2MSFTNGP10.phx.gbl...
>> First, you shouldn't crosspost to newsgroups not relevant to your
>> problem.
>
> I wasn't aware that I had done that.
>
>>
>> Second, create a view that will simply select and display the value of
>> HOST_NAME() and you will you see if there is a problem with it and
>> SQL-Server 7 and a MDB file.
>
> Good point. I created a view that returned the value of HOST_NAME(), and
> it linked fine into the MDB. I also created a new table, gave it a
> two-field PK, and used HOST_NAME() as a parameter for one of the fields in
> a view. That view also linked and displayed records fine. (Didn't do a
> two-table test, which would be similar to what I'm working with. But it
> shows that HOST_NAME() works fine with MDB linked views.)
>
> Neil
>
>
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: http://cerbermail.com/?QugbLEWINF
>>
>>
>> "Neil" <nospam@nospam.net> wrote in message
>> news:lra9f.1731$8c5.1137@newsread3.news.pas.earthlink.net...
>>>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.
>>>
>>> Thanks!
>>>
>>> Neil
>>>
>>> SELECT INVTRY.*, InvtrySelections.Sel, InvtrySelections.MachName
>>> FROM dbo.INVTRY INNER JOIN
>>> dbo.InvtrySelections ON
>>> dbo.INVTRY.ID = dbo.InvtrySelections.ID
>>> WHERE (dbo.InvtrySelections.MachName = HOST_NAME())
>>>
>>
>>
>
>

[Back to original message]