I'm upsizing my application from MS-Access FE and BE to MS-Access FE and SQL Server BE. I used the SSMA Access "upsizing" tool to convert all the tables from Access to SQL, then I linked the SQL tables in the Access FE using ODBC.
我正在将我的应用程序从MS-Access FE和BE升级到MS-Access FE和SQL Server BE。我使用SSMA Access“升迁”工具将所有表从Access转换为SQL,然后使用ODBC链接Access FE中的SQL表。
In my access database, some tables had DateTime columns that were converted in datetime(0) column in SQL Server. After I linked theses tables in Access, Access sees theses columns as text columns even if they are datetime2(0) columns in the SQL server BE.
在我的访问数据库中,某些表具有在SQL Server的datetime(0)列中转换的DateTime列。在Access中链接这些表后,Access将这些列视为文本列,即使它们是SQL Server BE中的datetime2(0)列。
This cause some problem because queries that were working with a date format are not working with the text format. Is there anyway to link the tables so that the datetime(0) field are treated as datetime values by Access?
这会导致一些问题,因为使用日期格式的查询无法使用文本格式。是否有链接表,以便日期时间(0)字段被Access视为日期时间值?
5 个解决方案
#1
7
According to TechNet, there is only limited support for the new data type, datetime2. I would go with datetime, if you can.
根据TechNet的说法,对新数据类型datetime2的支持有限。如果可以的话,我会选择日期时间。
#2
3
Similar issue: RESOLVED
类似的问题:已解决
I have an SQL server with a field as DATETIME2 data type and am connecting to it via ODBC as a linked table in MS Access on Win7.
我有一个SQL服务器,其字段为DATETIME2数据类型,并通过ODBC连接到它作为Win7上的MS Access中的链接表。
When connecting from two different workstations using the same db, one had a proper Data Type of "Date/Time" and the other had a Data Type of "Short Text"
当使用相同的数据库从两个不同的工作站连接时,一个具有正确的数据类型“日期/时间”,另一个具有“短文本”的数据类型
Solution: It turns out that the linked tables were established using two different DSN files, one had listed "DRIVER=SQL Server" and the other "DRIVER=SQL Server Native Client 11.0". In order to have "Date/Time" Data Type through the ODBC link, I needed to use Client 11.0.
解决方案:事实证明链接表是使用两个不同的DSN文件建立的,一个列出了“DRIVER = SQL Server”,另一个列出了“DRIVER = SQL Server Native Client 11.0”。为了通过ODBC链接获得“日期/时间”数据类型,我需要使用Client 11.0。
To see what drivers you have installed:
要查看已安装的驱动程序:
From the start menu search for "ODBC"
Select "Data Sources (ODBC)".
Click on the "Drivers" tab
You will see the drivers you have installed. I have three.
您将看到已安装的驱动程序。我有三个。
SQL Server 6.01.7601.17514
SQL Server Native Client 10.0 2007.100.5500.00
SQL Server Native Client 11.0 2011.110.6020.00
I believe SQL Server version 6 is installed by Win7 and updated via Windows Update. If this is all you have, you will only get TEXT out of DATETIME2 SQL DataTypes through linked tables.
我相信SQL Server版本6由Win7安装并通过Windows Update更新。如果这就是你所拥有的,那么你只能通过链接表从DATETIME2 SQL数据类型中获取TEXT。
The Native Clients were installed by SQL Server Management Studio 2008 and 2012 respectively.
Native Client分别由SQL Server Management Studio 2008和2012安装。
You can download and install SQL Server Native Client 11.0 from https://www.microsoft.com/en-us/download/details.aspx?id=36434
您可以从https://www.microsoft.com/en-us/download/details.aspx?id=36434下载并安装SQL Server Native Client 11.0。
#3
1
The SSMA for Access will convert any date/time field with values that are invalid in SQL Server to text. You should run the SSMA to try the conversion and it will tell you the problems, and then you can clean up the data before your actual conversion.
SSMA for Access会将任何日期/时间字段与SQL Server中无效的值转换为文本。您应该运行SSMA来尝试转换,它会告诉您问题,然后您可以在实际转换之前清理数据。
You'll have to skip the SSMA wizard, as it does the upsizing without previewing the results.
您必须跳过SSMA向导,因为它会在不预览结果的情况下进行升迁。
#4
1
I ran into the same issue which is what led me to this page, but I figured out the solution: Problem: linked tables from access to sql server show datetime2 columns as text, which has many implications like date comparison, sort, etc,.
我遇到了同样的问题,这是导致我进入这个页面的问题,但我找到了解决方案:问题:从访问sql server的链接表将datetime2列显示为文本,这有很多含义,如日期比较,排序等。
Solution: convert datetime2 in sql server to date time, and access will show the column as date field right away
解决方案:将sql server中的datetime2转换为日期时间,访问权限会立即将列显示为日期字段
Note that since tables already have data, you can't just change data types, I plan to add new columns datetime type, copy data over, remove orig column, rename
请注意,由于表已有数据,您不能只更改数据类型,我计划添加新列datetime类型,复制数据,删除orig列,重命名
How to check accuracy: I created a table with 3 columns in sql server, datetime, datetime2, date then I linked to it from access, only datetime showed as date field in access, other 2 showed as text
如何检查准确性:我在sql server,datetime,datetime2,date中创建了一个包含3列的表,然后我从access链接到它,只有datetime在访问时显示为日期字段,其他2显示为文本
#5
-1
It is generally required to verify your dates BEFORE the upsizing wizard. Users tend to enter "stupid" dates that are accepted by Access date fields but cause error in SQL Server. Try querying your tables with for dates outside a reasonable range like "BETWEEN 1/1/1930 and 1/1/2020" and correct those dates before restarting the upsizing wizard.
I have also found a product which I haven't used but which looks good: http://www.upsizing.co.uk/features_translate.aspx
通常需要在升迁向导之前验证您的日期。用户倾向于输入Access日期字段接受的“愚蠢”日期,但会导致SQL Server出错。尝试使用“BETWEEN 1/1/1930和1/1/2020”等合理范围之外的日期查询表格,并在重新启动升迁向导之前更正这些日期。我还发现了一个我没有使用但看起来不错的产品:http://www.upsizing.co.uk/features_translate.aspx
#1
7
According to TechNet, there is only limited support for the new data type, datetime2. I would go with datetime, if you can.
根据TechNet的说法,对新数据类型datetime2的支持有限。如果可以的话,我会选择日期时间。
#2
3
Similar issue: RESOLVED
类似的问题:已解决
I have an SQL server with a field as DATETIME2 data type and am connecting to it via ODBC as a linked table in MS Access on Win7.
我有一个SQL服务器,其字段为DATETIME2数据类型,并通过ODBC连接到它作为Win7上的MS Access中的链接表。
When connecting from two different workstations using the same db, one had a proper Data Type of "Date/Time" and the other had a Data Type of "Short Text"
当使用相同的数据库从两个不同的工作站连接时,一个具有正确的数据类型“日期/时间”,另一个具有“短文本”的数据类型
Solution: It turns out that the linked tables were established using two different DSN files, one had listed "DRIVER=SQL Server" and the other "DRIVER=SQL Server Native Client 11.0". In order to have "Date/Time" Data Type through the ODBC link, I needed to use Client 11.0.
解决方案:事实证明链接表是使用两个不同的DSN文件建立的,一个列出了“DRIVER = SQL Server”,另一个列出了“DRIVER = SQL Server Native Client 11.0”。为了通过ODBC链接获得“日期/时间”数据类型,我需要使用Client 11.0。
To see what drivers you have installed:
要查看已安装的驱动程序:
From the start menu search for "ODBC"
Select "Data Sources (ODBC)".
Click on the "Drivers" tab
You will see the drivers you have installed. I have three.
您将看到已安装的驱动程序。我有三个。
SQL Server 6.01.7601.17514
SQL Server Native Client 10.0 2007.100.5500.00
SQL Server Native Client 11.0 2011.110.6020.00
I believe SQL Server version 6 is installed by Win7 and updated via Windows Update. If this is all you have, you will only get TEXT out of DATETIME2 SQL DataTypes through linked tables.
我相信SQL Server版本6由Win7安装并通过Windows Update更新。如果这就是你所拥有的,那么你只能通过链接表从DATETIME2 SQL数据类型中获取TEXT。
The Native Clients were installed by SQL Server Management Studio 2008 and 2012 respectively.
Native Client分别由SQL Server Management Studio 2008和2012安装。
You can download and install SQL Server Native Client 11.0 from https://www.microsoft.com/en-us/download/details.aspx?id=36434
您可以从https://www.microsoft.com/en-us/download/details.aspx?id=36434下载并安装SQL Server Native Client 11.0。
#3
1
The SSMA for Access will convert any date/time field with values that are invalid in SQL Server to text. You should run the SSMA to try the conversion and it will tell you the problems, and then you can clean up the data before your actual conversion.
SSMA for Access会将任何日期/时间字段与SQL Server中无效的值转换为文本。您应该运行SSMA来尝试转换,它会告诉您问题,然后您可以在实际转换之前清理数据。
You'll have to skip the SSMA wizard, as it does the upsizing without previewing the results.
您必须跳过SSMA向导,因为它会在不预览结果的情况下进行升迁。
#4
1
I ran into the same issue which is what led me to this page, but I figured out the solution: Problem: linked tables from access to sql server show datetime2 columns as text, which has many implications like date comparison, sort, etc,.
我遇到了同样的问题,这是导致我进入这个页面的问题,但我找到了解决方案:问题:从访问sql server的链接表将datetime2列显示为文本,这有很多含义,如日期比较,排序等。
Solution: convert datetime2 in sql server to date time, and access will show the column as date field right away
解决方案:将sql server中的datetime2转换为日期时间,访问权限会立即将列显示为日期字段
Note that since tables already have data, you can't just change data types, I plan to add new columns datetime type, copy data over, remove orig column, rename
请注意,由于表已有数据,您不能只更改数据类型,我计划添加新列datetime类型,复制数据,删除orig列,重命名
How to check accuracy: I created a table with 3 columns in sql server, datetime, datetime2, date then I linked to it from access, only datetime showed as date field in access, other 2 showed as text
如何检查准确性:我在sql server,datetime,datetime2,date中创建了一个包含3列的表,然后我从access链接到它,只有datetime在访问时显示为日期字段,其他2显示为文本
#5
-1
It is generally required to verify your dates BEFORE the upsizing wizard. Users tend to enter "stupid" dates that are accepted by Access date fields but cause error in SQL Server. Try querying your tables with for dates outside a reasonable range like "BETWEEN 1/1/1930 and 1/1/2020" and correct those dates before restarting the upsizing wizard.
I have also found a product which I haven't used but which looks good: http://www.upsizing.co.uk/features_translate.aspx
通常需要在升迁向导之前验证您的日期。用户倾向于输入Access日期字段接受的“愚蠢”日期,但会导致SQL Server出错。尝试使用“BETWEEN 1/1/1930和1/1/2020”等合理范围之外的日期查询表格,并在重新启动升迁向导之前更正这些日期。我还发现了一个我没有使用但看起来不错的产品:http://www.upsizing.co.uk/features_translate.aspx