CODEWITHSUNDEEP
sql-servervbams-access
cgjeff
cgjeff

Reputation: 41

Best way to handle time formats from SQL Server in Access front end

I've migrated a database formerly in Access to SQL Server and am now rebuilding my Access front end to work with that SQL Server back end using a DSN-less link. I'm running into issues with new data entry in my time field. The error I get is ODBC--update on a linked table...failed. [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification (#0). I'm assuming this has to do with the way Access converts the data into short text from SQL Server, where it is a time(0) data type.

My question is what is the best way to handle "time" data to work in both Access and SQL Server? Ideally users would enter data in Access simply as something like "0130" where this means "1 hour and 30 minutes" (we never record seconds). And ideally the data in SQL Server would be formatted in some sort of time or datetime/datetime2 format.

I'm in a position to modify the formatting or code of the Access front end or the SQL Server back end (or both)--what's the cleanest way to go about this?

Upvotes: 1

Views: 1067

Answers (1)

Gustav
Gustav

Reputation: 55841

The best way is to user data type DateTime in SQL Server. Any ODBC driver will read and write that from Access as native DateTime of VBA.

If you must use DateTime2 in SQL Server, you must install and use one of the never ODBC drivers, not the "SQL Server" ODBC driver that comes with Windows as it cannot read the microsecond resolution of DateTime2.

You should never use the other date/time data types of SQL Server: Time and Short Date

Upvotes: 2

Related Questions