Is it possible to run a T-SQL query against an Access Database?
Such as a query that is not syntactically correct in Access...
select * from
OPENQUERY( anAccessLinkedServer,
'Select
[anSQLServerColumn1] = [anAccessColumn1],
[anSQLServerColumn2] = SUBSTRING([anAccessColumn2],1,1)
from [anAccessDatabaseTable]')
I have a program with large amounts of SQL code such as the above, and I do not want to have to rewrite it all in Access compliant syntax.
I can make it work only if I rewrite the above as...
select * from
OPENQUERY( anAccessLinkedServer,
'Select
[anAccessColumn1] AS [anSQLServerColumn1],
Left$([anAccessColumn2],1,1) AS [anSQLServerColumn2]
from [anAccessDatabaseTable]')
Kind Regards,
Laughton JacksonIf you are using OfficeXP (not sure about 2000 or ealier versions) you can open your database and go to the menu and click on Tools then options. Click on the Table/Queries tab and in the lower righ-hand corner check the box below the phrase "SQL Server Compatible Syntax (ANSI 92)" checkbox is marked "This database." Check the box and click OK.
This should help keep you from rewriting at least most of it.|||This option is not available in 2000.
Also, This Access dataase is being used by other legacy programs and this may affect these program's use of the database.
I was hoping this was possible by using the sp_addlinkedserver, sp_serveroption procedures.
Any other suggestions?
Thanks
Laughton Jackson|||Wish I did. Will keep you in mind and if anything comes up will post again.
Happy Hunting
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment