Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Raleigh, NC
    Posts
    2

    Unanswered: Running T-SQL query against MS Access database

    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 Jackson

  2. #2
    Join Date
    May 2003
    Posts
    60
    If 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.

  3. #3
    Join Date
    Oct 2003
    Location
    Raleigh, NC
    Posts
    2
    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

  4. #4
    Join Date
    May 2003
    Posts
    60
    Wish I did. Will keep you in mind and if anything comes up will post again.

    Happy Hunting

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •