|
Posted by Neil on 10/31/05 04:51
"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]
|