Importing from SQL 2005 server

[Guest]

I have a stored procedure that takes data from a sql 2005 table and loads it
into a Access 2003 table utilizing ODBC.
We recently upgraded from SQL 2000 to 2005 and since then we have been
unable to import the data
It gives an ODBC connection failure. I have tried reloading the ODBC and
during the tests it shows that the connection is fine but once I run the
query it gives me the error.
We used to run the query into Access 97 but I have upgraded the database to
Access 2003, however it will not work in either now with the data coming from
SQL2005.
Any ideas, I can run the stored proc and manually load the data but that
defeats the purpose and adds a lot of steps.
Nothing has changed with the query or either database in terms of tables and
fields etc. I have noticed that the query results are in a different order
from the database but it worked prior to the upgrade.

 

[Guest]

btw this is all over our intranet and we use Mcaffee

 

[Rick Brandt]

btw this is all over our intranet and we use Mcaffee

Must be something in your SQL Server setup (security or other). I have Access
apps that have seen their SQL Server back ends go from SQL Server 6.5 to 7 to
2000 then 2005 and the applications have never behaved any differently.

 

[Rick Brandt]

Access 2003 <---> not support SQL2005
Try Access 2007 works like magic

Incorrect. As long as you are using ODBC ANY version of Access can work with
SQL Server 2005.

Using an ADP file you do lose some functionality if the server is changed to
2005, but that should have no affect on an MDB using ODBC to connect.