|
|
Posted by Sylvain Lafontaine on 10/31/05 06:19
Also, if possible, that a look on the SQL-Server with the Profiler or
activate the ODBC tracing option. This will give you the possibility of
seeing what Access is trying to do.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uTA0rGd3FHA.2640@TK2MSFTNGP09.phx.gbl...
> 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())
>>>>
>>>
>>>
>>
>>
>
>
Navigation:
[Reply to this message]
|