Can't use linked-server field in join

[bobfin]

Using SQL Server 2000, I've created a linked server to an Access 2003 database. The linked server name is StatsDb and the table in it is Employees. It works fine for simple select queries of that table data, but not when I use an inner join to a field (User) in that table. The join code in the query is

ON StatsDb...Employees.User =

When checking syntax, I get the following message:

"The number name 'StatsDb...Employees' contains more than the maximum number of prefixes. The maximum is 3."

How do I get around this naming limitation so I can use linked data in joins?

 

[pbaldy]

This works for me in an SP:

...
FROM [LinkedServer].[DatabaseName].dbo.TableName AS OperArchive LEFT JOIN CarTypes ON OperArchive.car_type = CarTypes.ID

 

[bobfin]

Using an alias worked. Thanks!