|
|
Posted by Neil on 10/31/05 06:33
ODBC tracing might show something. Now sure what Profiler would yield, since
SQL Server seems to be returning the records without any problem.
Neil
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23sye%23Id3FHA.4076@TK2MSFTNGP15.phx.gbl...
> 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]
|