Access 2000 connecting to SQL Server 2005

Question

The company I work for has an old Access 2000 application that was using a SQL Server 2000 back-end. We were tasked with moving the back-end to a SQL Server 2005 database on a new server. Unfortunately, the application was not functioning correctly while trying to do any inserts or updates. My research has found many forum posts that Access 2000 -> SQL 2005 is not supported by Microsoft, but I cannot find any Microsoft documentation to verify that.

Can anyone either link me to some official documentation, or has anyone used this setup and can confirm that this should be working and our problems lie somewhere else?

Not sure if it matters, but the app is an ADP compiled into an ADE.

Thank you for your help in advance.

Answer 1

I'd say check the VBA in the Macros to see how it is doing it. It is probably using some form of VB connection to the Database in the back. I love the fact a Database is contacting a Database for it's data... :)

Answer 2

All I've read about Access 2000 -> SQL Server 2005 is that the upsizing wizard isn't supported.

If only the inserts and updates aren't functioning, it sounds like a permissions issue. Make sure the sql server login you are using in your connection string has read/write permission on your database.

Please avoid using the "sa" account for this purpose!

Answer 3

I'm not sure about that particular combination being supported, but have you tried setting the compatibilty mode for the database to sql server 2000. Maybe that will resolve your issues.

Edit: To do this run the following SQL:

EXEC sp_dbcmptlevel Name_of_your_database, 80;

More details here: http://blog.sqlauthority.com/2007/05/29/sql-server-2005-change-database-compatible-level-backward-compatibility/

Answer 4

If only the inserts and updates aren't functioning, it sounds like a permissions issue. Make sure the sql server login you are using in your connection string has read/write permission on your database.

Please avoid using the "sa" account for this purpose!

We wanted to use a generic apps account but that login "could not find" any of the stored procedures even though they existed and the login has explicit permissions to run them (and was also tested successfully, as that user, in SQL Management Studio). It wasn't until we granted that login "sa" privileges that we could actually access the database at all through the application.

but have you tried setting the compatibilty mode for the database to sql server 2000.

I'm not really sure how this is done. Could you explain?

Also of note, if we upgrade the app to Access 2003, everything works fine. Unfortunately, our IT dept does not want to upgrade everyone from Office 2000 to 2003, so this is not an option.

Thanks for your help.

Answer 5

but have you tried setting the compatibilty mode for the database to sql server 2000.

I just checked the 2005 database, I selected the database, and clicked Properties->Options, and it says the db is already in 2000 compatibility mode.

Answer 6

I've had a similar problem before when using ODBC linked tables to connect to an Sql Server. The solution was to relink the tables and specify the primary key to the table. If Access doesn't know the primary key it cannot perform inserts or updates.

I haven't any experience with ADPs but it could be a similar thing, theres a knowledge base article about it here http://support.microsoft.com/?scid=kb%3Ben-us%3B235267&x=15&y=13

Answer 7

Access ADPs are very closely tied to SQL Server versions, and MS has done a really poor job of fixing and breaking ADPs in the 3 major versions that have been released (2000, 2002 and 2003).

If you are trying to use the compiled ADE, I'd suggest that first you find the original ADP and see if you can get it to work. You may need to do some work there before creating your ADE.

Caveat: I don't do ADPs, and am glad I made the decision not to, as Microsoft is now deprecating them in favor of MDB=>ODBC=>SQL Server.