My problem is that I am trying to find a way to connect to an access database on a 64 bit system (there is no 64 Jet OleDb provider) and I have to run in a 64 bit process so no WoW solutions will work for me 
I am looking into trying to use a Sql Linked server but am trying to find a workaround without having to re-write all our queries to use four-part naming.
Is there someway to configure a connectionstring to default to executing against a linked server?
Any help or suggestions would be welcomed.
You can create a synonym (if your server is SQL 2005) for a four-part name.
See CREATE SYNONYM.|||Brilliant thanks for that Anton,
Had a bit of a play and it seems to work nicely. I guess I will run into trouble if any of the queries contain VBA expressions but that gets me a long way.
I am going to make a dumby Sql Database that contains no objects only the synonyms for the tables and views that I need in the Access database, then I can create a connection to that dumby database for any of the Jet work I need to do.
As a thought: I guess given that the synonyms are entities of the Sql database then preprossing will occur at the Sql Server, therefore it is unlikely that you could configure seemless passthrough style execution (in terms of the Sql dialect to use) to the Jet Linked server because Sql will always want its own dialect rather than the Jet dialect.
For example if I wanted a PIVOT/Cross Tab query, I would need to decide to send a request to a precompiled Jet query that contained a 'TRANSFORM' statement or send a Sql String with the Sql Server Style 'PIVOT....FOR...' Statement. Would this be right?
Thanks
Simon
No comments:
Post a Comment