Linked servers

[srbooth]

I have created a linked server in SQL 2000 to a database on a Unix server and all the tables show in SQL enterprise manager Linked Servers. The linked server in called Kpay

Is there any way to directly link to the tables on the Kpay linked server directly with Access 2000 so querries can be run on the data from the tables on the linked server?

 

[SQL_Hell]

Hiya,

I am a little confused by the question, but I think you could try creating a SQL server view or stored procedure that references tables in the linked server and then call that view or stored procedure from Access .adp.

I dont think you will be able to refernce the linked server tables in an Access query.

 

[srbooth]

Linked server

Thanks for the reply.

Could you give me some direction how to do that. The linked server is call Kpay and the table I'm after is tblPayRollInfo and the fields are field1, fieild2, address, name.

Thanks

 

[SQL_Hell]

which part would you like directions on?

 

[srbooth]

Is it best to create a database in SQL and then create views for the data I'm looking to querry in Access. If this is the best way - I'm looking for some help how to call the data from the linked server into a SQL view.

The linked server is call Kpay and the table I'm after is tblPayRollInfo and the fields are field1, fieild2, address, name.

 

[SQL_Hell]

Hi,

The better way is to create the database in SQL server and also create the views (select queries) in SQL server, then call the SQL server views in Access. Are you using an acess project(.adp) or a .MDB?

The only trouble is... I have no idea what type of database this is, so I cant help with the syntax.

For a linked server to another SQL server the syntax would be....

SELECT * FROM <linkedservername>.<databasename>.<owner>.<tablename>

 

[srbooth]

The database sits on a UNIX server and is connected to though ODBC using a system DSN called KpaySQL and the database name is ODBC_PAY.