Menu
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.
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.
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?
273
The DATETIME2 data type specifies a date and time with fractional seconds. DATETIME2 supports dates from 0001-01-01 through 9999-12-31. The default value is 1900-01-01 00:00:00. The time is based on a 24-hour clock.
time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications. datetime2 has larger date range, a larger default fractional precision, and optional user-specified precision.
Datetime2 has fractional seconds precision of 7 which means that there are 7 digits representing the nanosecond value whereas DateTime has a precision of 3.
The DateTime2 is an SQL Server data type, that stores both date & time together. The time is based on the 24 hours clock. The DateTime2 stores the fractional seconds Up to 7 decimal places (1⁄10000000 of a second). The Precision is optional and you can specify it while defining the DateTime2 column.
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.
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.
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.
The Native Clients were installed by SQL Server Management Studio 2008 and 2012 respectively.
You can download and install SQL Server Native Client 11.0 from https://www.microsoft.com/en-us/download/details.aspx?id=36434
170
According to TechNet, there is only limited support for the new data type, datetime2. I would go with datetime, if you can.
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
