从文本移动到varchar(MAX):MS Access是否有任何麻烦? [英] Moving from text to varchar(MAX): Are there any troubles to expect with MS Access?

问题描述

众所周知，使用SQL Server后端的MS Access应用程序(MDB)在某些数据类型上存在问题.例如，

It is well-known that MS Access applications (MDBs) using SQL Server backends have trouble with certain data types. For example,

• 位字段支持已损坏: NULL值会导致奇怪的错误，
• 十进制字段支持已断开:排序无法正常工作，
• 等

• bit field support is broken: NULL values lead to strange errors,
• decimal field support is broken: Sorting does not work correctly,
• etc.

我们现在正在考虑从 text/ntext 字段移至 varchar(MAX)/nvarchar(MAX)字段，

We are now considering to move from text/ntext fields to varchar(MAX)/nvarchar(MAX) fields, as recommended by Microsoft:

ntext，text和image数据类型将在Microsoft SQL Server的将来版本中删除.避免在新的开发工作中使用这些数据类型，并计划修改当前使用它们的应用程序.请改用nvarchar(max)，varchar(max)和varbinary(max).

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

我们这样做会遇到麻烦吗?

Are we going to run into trouble doing that?

推荐答案

我知道这是一篇较旧的文章，但我认为它仍然与某些人相关.我处理大量的旧数据，这些数据从Access Memo字段扩展到SQL，然后在Access中变成链接表.

I know this is an older post, but I think it is still relevant to some folks. I deal a lot with legacy data that is scaled up from Access Memo fields to SQL and then turned into a link table in Access.

我发现缩放到NVARCHAR(max)确实会导致链接表出现问题.根据您使用哪个驱动程序构建Access Link表，问题会有所不同.

I have found that scaling to NVARCHAR(max) does cause issue within the link tables. Depending on which driver you are building the Access Link table with, the problem varies.

使用SQL Native Client 10，我的第一个发现是Access将字段视为NVARCHAR(4000).使用SQL Server作为驱动程序确实可以解决问题时，仍然存在问题.使用此较旧的驱动程序，似乎很难找到问题，但确实会发现.通常存在类似的尺寸问题.

Using SQL Native Client 10 my first finding is that Access treats the field as a NVARCHAR(4000). While using SQL Server as the driver does change the issues, there are still issues. With this older driver the issues seem to be harder to track down but do show up. Usually with a similar sizing problem.

当心，似乎运行正常，实际上可能只是在正确运行，因为尚未遇到正确的情况.

Beware, what seems to be running ok, may in fact just be running correctly because the right circumstance has not been hit yet.

如果发现字段数据不需要超过4000个字符，则将其设置为NVARCHAR(4000).如果您只需要4000，则将MAX设置为过大.

If you find that your field data never requires more than 4000 characters, then make it a NVARCHAR(4000). To set at MAX is over kill if you only need 4000 anyway.

这篇关于从文本移动到varchar(MAX):MS Access是否有任何麻烦?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！