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

[Rich P]

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 ***

[Rick Brandt]

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

[lyle fairfield]

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 <[e-mail]:

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 Developersdexhttp://www.developersdex.com***