none
OleDb on Access 97 RRS feed

  • Question

  • Hi folks. I'm having a real hard time working with an access database using OleDb. Is there a way for me to use SQL Server Syntax on the OleDB command? I say this because a few features I need are not directly supported and I'm having problems adapting the syntax I built with SQL Server to work on Access 97 DB. I even enabled SQL Server Compatibility syntax on the access database, but still can't get it to work.

    The query works perfectly if done through SQL Server linked server, but not using OleDB provider. Do you have any suggestion on which I aproach I should take?

     

    Thanks and Regads,

    Fábio


    "To alcohol! The cause of and solution to all of life's problems." - Homer Simpson
    Wednesday, February 23, 2011 5:29 PM

Answers

  • I do not think there is any standard approach for it. Jet engine has its own syntax in addition to ANSI basic support. If you are trying to create something that would work for both, SQL Server and Access 97 then most likely you would need to stick to basic syntax of SQL statements without using any functions, but even in that case joins will not work, since syntax is different for those two engines.
    Val Mazur (MVP)

    http://www.xporttools.net

    • Marked as answer by Fábio Franco Thursday, February 24, 2011 1:54 PM
    Wednesday, February 23, 2011 11:38 PM
    Moderator
  • Not sure what tools you are using and I haven't seen any code, but it sounds like the SQL Server tool is pulling in all the data via Jet OLEDB and then using TRANSACT SQL to perform the queries on the data (as you said). I don't believe there is any conversion between TRANSACT and Jet SQL since there is no direct translation between keywords.

    Microsoft Access works similarly on external databases (linked tables they call them). That is, unless you specify pass-through execution and allow the server to process the SQL statement.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Fábio Franco Thursday, February 24, 2011 1:54 PM
    Thursday, February 24, 2011 1:46 PM

All replies

  • I do not think there is any standard approach for it. Jet engine has its own syntax in addition to ANSI basic support. If you are trying to create something that would work for both, SQL Server and Access 97 then most likely you would need to stick to basic syntax of SQL statements without using any functions, but even in that case joins will not work, since syntax is different for those two engines.
    Val Mazur (MVP)

    http://www.xporttools.net

    • Marked as answer by Fábio Franco Thursday, February 24, 2011 1:54 PM
    Wednesday, February 23, 2011 11:38 PM
    Moderator
  • Hi Val, thanks for your response. I don't really need to work on both SQL Server and Access 97. I actually need only Access 97 and Jet Engine. The problem is that Jet Engine has some very weird behaviour and the errors I get are usually of no help. I'm not expert on the Jet syntax, but some things I'd expect to have ANSI support on Jet doesn't seem to.

    I already changed all CASEs to IIFs, date conversions, etc... But still get weird behaviour. So, I'm not sure what to do yet. One thing, do you know how SQL Server linked server queries using Jet provider? I say this because I got the query working on SQL Server syntax, targeting the Access 97 database. Does SQL Server get all data and use its own engine to perform the query? Or does it translate SQL Server query to Jet syntax? If it's the second I'd like to get my hands on that if possible and all my problems would go away.

     

    Thanks again.

    Regards,

    Fábio


    "To alcohol! The cause of and solution to all of life's problems." - Homer Simpson
    Thursday, February 24, 2011 1:00 PM
  • Not sure what tools you are using and I haven't seen any code, but it sounds like the SQL Server tool is pulling in all the data via Jet OLEDB and then using TRANSACT SQL to perform the queries on the data (as you said). I don't believe there is any conversion between TRANSACT and Jet SQL since there is no direct translation between keywords.

    Microsoft Access works similarly on external databases (linked tables they call them). That is, unless you specify pass-through execution and allow the server to process the SQL statement.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Fábio Franco Thursday, February 24, 2011 1:54 PM
    Thursday, February 24, 2011 1:46 PM
  • I see... I suspected of that also (specially because of the existance of the openquery function of SQL Server), but I had some hope for the second case... Oh well, I'll bash my head against the wall a little more with this limited Jet capabilities.

    Thank you both for the help.

    Regards,

    Fábio


    "To alcohol! The cause of and solution to all of life's problems." - Homer Simpson
    Thursday, February 24, 2011 1:54 PM