Access 2000连接到SQL Server 2005

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.

我工作的公司有一个旧的Access 2000应用程序使用SQL Server 2000后端。我们的任务是将后端移动到新服务器上的SQL Server 2005数据库。不幸的是,在尝试进行任何插入或更新时,应用程序无法正常运行。我的研究发现很多论坛帖子,微软不支持Access 2000 - > SQL 2005,但我找不到任何Microsoft文档来验证。

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.

不确定是否重要,但应用程序是ADP编译成ADE。

7 个解决方案

#1

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.

在使用ODBC链接表连接到Sql Server之前,我遇到过类似的问题。解决方案是重新链接表并指定表的主键。如果Access不知道主键,则无法执行插入或更新。

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

我没有任何ADP经验,但它可能是类似的东西,这里有关于它的知识库文章http://support.microsoft.com/?scid=kb%3Ben-us%3B235267&x=15&y=13

#2

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... :)

我会说检查宏中的VBA以了解它是如何做到的。它可能在后面使用某种形式的VB连接到数据库。我喜欢数据库正在联系数据库以获取数据... :)

#3

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

我读过的所有关于Access 2000 - > SQL Server 2005的内容都是不支持升迁向导。

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.

如果只有插入和更新不起作用,则听起来像权限问题。确保您在连接字符串中使用的sql server登录具有对数据库的读/写权限。

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

为此,请避免使用“sa”帐户!

#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.

如果只有插入和更新不起作用,则听起来像权限问题。确保您在连接字符串中使用的sql server登录具有对数据库的读/写权限。

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

为此,请避免使用“sa”帐户!

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.

我们想使用通用应用程序帐户,但登录“无法找到”任何存储过程,即使它们已存在且登录具有运行它们的显式权限(并且还在SQL Management Studio中成功测试了该用户) 。直到我们授予登录“sa”权限,我们才能通过应用程序实际访问数据库。

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

但您是否尝试将数据库的兼容模式设置为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.

另外值得注意的是,如果我们将应用程序升级到Access 2003,一切正常。不幸的是,我们的IT部门不希望将每个人从Office 2000升级到2003,所以这不是一个选择。

Thanks for your help.

谢谢你的帮助。

#5

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.

我不确定支持的特定组合,但您是否尝试将数据库的兼容模式设置为sql server 2000.也许这将解决您的问题。

Edit: To do this run the following SQL:

编辑:要执行此操作,请运行以下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/

更多细节:http://blog.sqlauthority.com/2007/05/29/sql-server-2005-change-database-compatible-level-backward-compatibility/

#6

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

但您是否尝试将数据库的兼容模式设置为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.

我刚检查了2005数据库,我选择了数据库,然后单击了Properties-> Options,它说db已经处于2000兼容模式。

#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).

访问ADP与SQL Server版本密切相关,MS在已发布的3个主要版本(2000,2002和2003)中修复和破坏ADP的工作非常糟糕。

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.

如果您正在尝试使用已编译的ADE,我建议您首先找到原始ADP并查看是否可以使其工作。在创建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.

警告:我不做ADP,很高兴我决定不这样做,因为微软现在不赞成使用MDB => ODBC => SQL Server。