column name of link server table table

[jamert]

Hi i have an access database linked to the sql2k as a linked server. I would like to get the column names from the table "clients" in the access db from sql. How is that accomplished?

Thanks again

 

[jamert]

sry, i would like to use the column names in a stored procedure

thanks

 

[CaptainD]

I got this from Books on Line.

Access databases do not have catalog and schema names. Therefore, tables in an Access-based linked server can be referenced in distributed queries using a four-part name of the form linked_server...table_name.

This example retrieves all rows from the Employees table in the linked server named Nwind.

SELECT *
FROM Nwind...Employees

 

[philhege]

There is no direct method to list an Access table's columns using SQL syntax. You could load the schema into a temp table, and then use INFORMATION_SCHEMA.columns to query the schema of the temp table. This method is probably not foolproof, but can get you close to what you need.

select top 0 * into #temp_WHAT from openquery(MyAccessLinkedDB,'select * from theAccessTable')

select * from tempdb.INFORMATION_SCHEMA.columns where table_name like '%temp_WHAT%'

DROP TABLE #temp_WHAT

HTH,

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.