Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLAndy

I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.

MS Access Annoyance: Doesn’t Link Synonyms

I use Access when I don’t have dedicated admin tools for editing data, frequently the case for infrequently changed data in smaller projects. Linked tables work reasonably well, just have to create a DSN first.

Except…it doesn’t seem to work (at least obviously) for synonyms. Here’s one I created (viewing as sysadmin):

image

And the view when linking from Access, also with syadmin credentials:

image

In this case I just wanted to peek at a row just inserted. Not that I couldn’t run the select, but was (or would have been) convenient to do so, as often I wanted to update the row so it would re-process without having to run through the entire process again.

I get that it’s not always a table/view, would be strange to link to a proc – but isn’t that what metadata is for?

Why synonyms? I’ve a huge fan of two part syntax. Not one part, not three, not four. Make it all look local, when it’s time to move the db life is good, just update all the synonyms and move on, no code changes.

Comments

Posted by fmurphy on 10 October 2011

Any update here? I run into a similar problem.

I've got an Access user who needs to create a link to one of my SQL Server tables. He needs to link to a synonym rather than a fully-qualified table name.

I need to keep the synonym functionality. Is there a way to get it to show up when you choose to "External Data/Import More/ODBC Database/Link to the data source ..." Currently, it shows only tables and I'd like to add synonyms to it.

Thanks.

Posted by Eddie Hendrix on 13 October 2011

I have the same issue where the SYNONYM does not work but it's when using a linked server.  Even if you specify the name directly you an error.  

Any help would be great.

Leave a Comment

Please register or log in to leave a comment.