check if linked server table column exists

Question

I have several access databases that I'm copying over to the sql server, connecting to as a linked server, and then pulling data from each night.

I need to be able to identify if the current access db the linked server is pointing too has a particular column. If not I'll need to create the column before copying the next access db over.

Is it possible to check if an access column exists through a linked server connection?

Answer 1

You'll have to make a connection first using VBA and then check with sys.columns

SELECT COUNT(*)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('MyTable') AND c.name = 'ExpectedCol'

Or use COLUMNPROPERTY

That is, check the metadata separately trying to query the column