Ms Access前端的日期格式& Sql server后端 [英] Date format for Ms Access frontend & Sql server backend

问题描述

大家好！我新建了ms访问程序前端和sql server后端。当
进行迁移时，我的日期数据类型格式变为（yyyy-mm-dd）。任何人都知道如何更改我的前端日期格式 它成为（ms访问表单）（dd-mm-yyyy）。或者永久地将我的sql date数据类型更改为 （dd-mm-yyyy）。我希望有人能帮助我。谢谢

推荐答案

在开始搞乱应用程序之前检查和修复的一个非常重要的细节是在DESIGN模式下打开一个表。由于是一个链接表，然后忽略有关正在读取的表的消息。

One VERY important detail to check and fix BEFORE you start messing with the application is to open a table in DESIGN mode. Due to being a linked table, then ignore the message about the table being read only.

检查日期列并确保它们被视为日期时间。我要强调你仔细检查这个问题。如果列被视为字符串/文本，那么您需要重新链接表以解决此问题。

Check the date columns and ensure they are seen as date time. I going to STRESS that you double check this issue. If the columns are seen as string/text, then you need to re-link the tables to fix this.

SSMA迁移向导将列大小为datetime2和STANDARD SQL来自Access的驱动程序将返回字符串/文本等列。结果是您拥有的日期数据。您不希望尝试对您的应用程序进行任何修复，直到您解决此问题
。

The SSMA migration wizard will up-size columns as datetime2, and the STANDARD SQL driver from Access will THEN return such columns as string/text. The result is date data like you have. You do NOT want to attempt any fix to your application until such time you resolve this issue.

推荐修复上述内容：

将datetime2中的列重命名为SQL Server中的日期时间类型。现在重新链接访问表，您的日期格式问题将消失。如果您有一个或两个表和一些日期时间列，我只推荐这个想法。否则，如果你有很多表，这可以
很多工作。

Rename the columns from datetime2 back to the type of datetime in SQL server. Now re-link the access tables and your date formatting issue will go away. I only recommend this idea if you have 1 or two tables and a few datetime columns. Otherwise this can be LOTS of work if you have lots of tables.

使用更新的SQL odbc河重新链接表（本机11驱动程序是一个不错的选择） 。如果使用默认的"旧版"ODBC驱动程序进行链接，则datetime2列将显示为文本。我看到有人对他们的应用程序进行了3天的杂乱更改然后
AFTER意识到问题不是日期格式化，而是访问是看到并查看这些列的事实 作为文字。因此，尝试使用本机11重新链接表.Native 11是一个不错的选择，但您必须在每个工作站上安装本机11。因为在每个工作站上安装这个"麻烦"
，所以我现在继续使用标准SQL驱动程序，以避免在每个工作站上安装本机11 SQL驱动程序。这也意味着我可以选择避免sql server中的datetime2数据列。

Re-link the tables using a newer SQL odbc river (the native 11 driver is a good choice). If you link using the default "legacy" ODBC driver then datetime2 columns will appear as text. I seen someone make 3 days of messy changes to their application to then AFTER realize the issue was not date formatting, but the fact that access was seeing and viewing those columns  as text. So try re-linking tables using native 11. Native 11 is a good choice, but you have to install the native 11 on each workstation. Because of this "hassle" of installing on each workstation, then I for now continue to use the standard SQL driver to avoid having to install native 11 SQL driver on each workstation. this also means by choice I avoid the datetime2 data column in sql server.

如果你有很多很多的表，并且你不想采用原生11 sql驱动程序那么你需要重新迁移数据（或手动将每个datetime2列更改为datetime）。如果您使用SSMA迁移此数据，那么您可以/应该更改
从datetime2到datetime的数据类型映射。这样，在我迁移许多表后，datetime列对于默认的odbc SQL驱动程序是正确的。

If you have lots and lots of tables, and you do NOT want to adopt the native 11 sql driver then you need to re-migrate your data (or manually change each datetime2 column to datetime). If you are using SSMA to migrate this data, then you can/should change the data type mapping from datetime2 to datetime. That way after I migration of many tables the datetime columns will be correct for the default odbc SQL drivers.

只要查看屏幕截图，就可以看到该日期字段分隔符的" - " 。这看起来非常错误，并建议将日期列视为TEXT，而不是日期时间列。

Just looking at your screen shot you can see the "–" for that date field delimiters. That looks VERY wrong and suggests the date column is being seen as TEXT and not a datetime column.

我不想听起来不善，但不要过去，不要通过监狱，不要掷骰子为垄断游戏板的下一步行动。

I don’t want to sound un-kind, but do not pass go, do not pass jail, don’t roll the dice for the next move on the monopoly game board.

您希望100％修复此列数据类型问题，然后再进行疯狂的鹅狩猎，只需简单地修改Access中的大量表单和代码以及各种内容不需要也不想。

You want to 100% fix this column data type issue before you go on a wild goose hunt modifying tons of forms and code and all kinds of things in Access when you simply don’t need to nor want to.

迁移到SQL服务器可以并且将会引入问题以及您需要和想要的所有工作！但是，日期格式的广泛分解并不是您在迁移后必须更改或处理的内容。事情应该像以前一样工作，而不是在日期格式化方面更改
到访问应用程序。

Migration to SQL server can and will introduce issues and all the work you need and want! However a wide spread break down of date formats is not something you have to change nor deal with after a migration. Things should be working as before without changes to the access application in regards to date formatting.

祝你好运！

关心，

Albert D. Kallal（访问MVP）

加拿大艾伯塔省埃德蒙顿

Regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

这篇关于Ms Access前端的日期格式& Sql server后端的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！