How do I create a Jet ODBC link to a SQL Server view with periods in the field names?

Question

I need to create an ODBC link from an Access 2003 (Jet) database to a SQL Server hosted view which contains aliased field names containing periods such as:

Seq.Group

In the SQL source behind the view, the field names are encased in square brackets...

SELECT Table._Group AS [Seq.Group]

...so SQL Server doesn't complain about creating the view, but when I try to create an ODBC link to it from the Jet DB (either programmatically or via the Access 2003 UI) I receive the error message:

'Seq.Group' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

Unfortunately, I cannot modify the structure of the view because it's part of another product, so I am stuck with the field names the way that they are. I could add my own view with punctuation-free field names, but I'd really rather not modify the SQL Server at all because then that becomes another point of maintenance every time there's an upgrade, hotfix, etc. Does anyone know a better workaround?

Answer 1

Just guessing here: did you try escaping the dot? Something like "[Seq\.Group]"?

Answer 2

Although I didn't technically end up escaping the dot, your suggestion actually did make me realize another alternative. While wondering how I would "pass" the escape code to the "SQL" server, it dawned on me: Why not use a "SQL Pass-Through Query" instead of an ODBC linked table? Since I only need read access to the SQL Server data, it works fine! Thanks!

Answer 3

Another proposal would be to add a new view on your sql server, not modifying the existing one. Even if your initial view is part of a "solution", nothing forbids you of adding new views:

SELECT Table._Group AS [Seq_Group]