I am trying to set up an ODBC connection between an Access 2003 (Jet) database and a SQL Server view. The view has field names with periods, such as:
Index.Category
In the SQL view definition, these names are wrapped in brackets:
SELECT SourceTable.Category AS [Index.Category]
This configuration does not cause issues in SQL Server; however, when I attempt to connect through the Jet database, whether by code or using the Access UI, I get an error:
'Index.Category' is an invalid identifier. Ensure it lacks invalid characters or punctuation and isn't excessively lengthy.
Regrettably, I cannot change the view's structure as it is integral to another system. While I could create a separate view without punctuation, I prefer not to alter the SQL Server schema to avoid added maintenance burdens during upgrades or patches. Does anyone have a practical solution for this issue?
Hey! One crazy but often overlooked idea is to create a passthrough query in Access. They run directly against SQL Server, so field name restrictions are bypassed. It’s a bit more manual since it doesn’t show fields in design view, but it might be the workaround you need. hope that helps!
hey there! you’ve got some interesting challenges with those field names. Have you thought about using a VBA script in Access? VBA can manipulate how queries are built, might be ace to tailor something that’s viewable in both systems. Do others have more experiences with Jet database quirks?
Handling field names with dots in Access via ODBC can be tricky. While directly renaming fields in SQL Server isn’t feasible, consider using aliasing in Access. Create a local query that references the Jet-connected view and assign new alias names to the fields within Access itself. This way, Jet will use the alias instead of the problematic field names. This method avoids the need to alter the SQL schema and allows you to seamlessly work within Access without errors.