Re: Loads of errors when querying MS SQL Server Express 2005 from Access 2007

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Rich P
    #1

    Re: Loads of errors when querying MS SQL Server Express 2005 from Access 2007

    The problem is with the ODBC connection - in general. If you are going
    to be using Access as a front end for a Sql Server backend - you will
    have more consistent/better results using ADO instead of ODBC. ADO
    requires using VBA. If you want to use the Access Query Tool you will
    have to create a temp table in the Access mdb into which you would write
    an ADO datapull from the server, and then you can query that. The
    alternative is to write the queries directly in the ADO command object
    and execute it. You will write the results of the ADO query into a temp
    table in the Access mdb the same as above.

    Rich

    *** Sent via Developersdex http://www.developersdex.com ***
    Tags: None
    • Rick Brandt
      #2
      Re: Loads of errors when querying MS SQL Server Express 2005 from Access 2007

      Rich P wrote:
      The problem is with the ODBC connection - in general. If you are
      going to be using Access as a front end for a Sql Server backend -
      you will have more consistent/better results using ADO instead of
      ODBC. ADO requires using VBA. If you want to use the Access Query
      Tool you will have to create a temp table in the Access mdb into
      which you would write an ADO datapull from the server, and then you
      can query that. The alternative is to write the queries directly in
      the ADO command object and execute it. You will write the results of
      the ADO query into a temp table in the Access mdb the same as above.
      >
      Rich
      >
      *** Sent via Developersdex http://www.developersdex.com ***
      I have no significant ODBC problems with any version of Access connecting to
      any version of SQL Server.

      Would you really create a front end that did all of its database operations
      from code?

      --
      Rick Brandt, Microsoft Access MVP
      Email (as appropriate) to...
      RBrandt at Hunter dot com


        Comment

        • lyle fairfield
          #3
          Re: Loads of errors when querying MS SQL Server Express 2005 fromAccess 2007

          I don't use ODBC connections by personal preference. But there is NO
          problem with ODBC connections "in general". The cleverness of the ODBC
          connection technology is quite amazing. The notion of using a temp
          table in an MDB to interact with an SQL-Server table and populating
          and maintaining that table through code is bizarre and naive.
          The OP could, I suppose, correct his query; an alternative would be
          just to use an ADP. There is little wrong with ADPs that is not also
          wrong with ODBC connected MDBs.

          On May 30, 3:38 pm, Rich P <rpng...@aol.co mwrote:
          The problem is with the ODBC connection - in general.  If you are going
          to be using Access as a front end for a Sql Server backend - you will
          have more consistent/better results using ADO instead of ODBC.  ADO
          requires using VBA.  If you want to use the Access Query Tool you will
          have to create a temp table in the Access mdb into which you would write
          an ADO datapull from the server, and then you can query that.  The
          alternative is to write the queries directly in the ADO command object
          and execute it.  You will write the results of the ADO query into a temp
          table in the Access mdb the same as above.  
          >
          Rich
          >
          *** Sent via Developersdexht tp://www.developersd ex.com***

            Comment

            Previous template Next
            Working...