MS-Access sees SQL server's datetime2 fields as TEXT

Question

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?

Tinned

Do you mean by datetime2(0)? Datetime should work with MS Access. I need to do some more messing for datetime2. – Froemming

@Remou: Yes, I meant datetime2(0), I'll correct the question. I must admit I don't know the difference between datetime and datetime2. What kind of messing do you need to do to work with Access? – Cinthiacintron

I just meant I needed to find out more. According to technet.microsoft.com/en-us/library/cc179181.aspx, there is only limited support for the new data type, datetime2. I would go with datatime, if you can. – Froemming

@Remou: Ok, I think I can still replay the export and this time I'll configure SSMA to create datetime fields instead. Il you formulate this as an answer I'll accept it. – Cinthiacintron

Answer 1

According to TechNet, there is only limited support for the new data type, datetime2. I would go with datetime, if you can.

Froemming

Answer 2

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

Dingdong

Thanks for bringing new information to an old question, and welcome to Stack Overflow! – Dark

One can modify SSMA to upsize to the older datetime. I recommend this if one is not going to use and adopt the newer native 11 drivers. The newer driver is preferred, but requires that each workstation have the newer drivers installed. For this reason, I currently use the older SQL driver that installed on all windows machines by default. – Jockstrap

Answer 3

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.

You'll have to skip the SSMA wizard, as it does the upsizing without previewing the results.

Arnoldarnoldo

Answer 4

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

Solution: convert datetime2 in sql server to date time, and access will show the column as date field right away

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

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

Wachter

Answer 5

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

Mollie

The date limits in your BETWEEEN statement are not correct for SQL Server. The references say 1/1/1753 is the starting point. Also, SQL Server 2008 adds a new kind of date field that can go back to the year 1. – Arnoldarnoldo

@David: of course they are not (it would be sad for SQL Server). I was recommending doing a check on whatever date range is reasonable (for one's data), to eliminate stupid dates. – Mollie