Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
linked server in the Access sense, or the SQL Server sense? –  gbn Nov 29 '11 at 15:47
    
^ I mean linked tables in Access –  gbn Nov 29 '11 at 16:17
add comment

1 Answer

up vote 0 down vote accepted

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

share|improve this answer
    
I wasnt aware Access could have a linked server. So it isnt possible for me to check using SQL I have to create a VB script to do this? –  Lumpy Nov 29 '11 at 16:07
    
A linked server exists in SQL Server to connect to another data source. I assumed Access is talking to SQL Server via linked tables –  gbn Nov 29 '11 at 16:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.