Subscribe to this blog
Briefcase
Print
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):
And the view when linking from Access, also with syadmin credentials:
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.
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.