ODBC--call failed #241

[jamier01]

Hi,

We're currently using Access 2013 to manage some data and it need to reference some other information from SQL Server. This normally isn't an issue.
I've written a view that we can use as a linked table to pull selected data into Access but some users (not all) are getting the error: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (#241).

The following query to create the SQL view works for everyone but pulls through all 900k items to the linked table in Access:

SELECT linkRef, DateUpdated
FROM myTable
WHERE DateUpdated IS NOT NULL

What we want is the latest date for each reference (~196k items) so the following should work and does for me and a couple of others but not everyone:

SELECT linkRef, MAX(DateUpdated) AS DateUpdated
FROM myTable
WHERE DateUpdated IS NOT NULL
GROUP BY linkRef

The aggregate seems to cause the error. I've also tried SELECT linkRef, CAST(MAX(DateUpdated) AS DATETIME2(0)) AS DateUpdated as well as SMALLDATETIME. The data type of DateUpdated is DATETIME2(0) so we shouldn't have conversion issues and all dates in this field appear to be valid.

We have an inefficient work-around (this query is only part of a larger group of queries to create a more complicated view but is the cause of our woes) but I'd like to understand why this is happening to prevent it in the future.

Thanks

 

[Micron]

MS Access doesn't like the 'BIGINT' data type.

Not prior to 2016, but it does support BIGINT now?

 

[Legacy 456155]

I just had an issue today with BIGINT. ADO COM converted it to a string. I just went ahead and changed the datatype in the server table column to CHAR(x). Is there another way around this Micron? (Well, besides having to code for it in every instance I use it in my project.)

 

[Micron]

Sorry, that's out of the realm of my expertise. If you're still having this issue with only some users, then I can't see how the same code or expressions that work for somebody else could be at play here. To my mind, it has to be something else - perhaps a difference in Access build versions, or something else that is different from one user to the next. You've already covered Refferences, so it's apparently not that. To me, life is simpler when I grab data from ODBC or other such sources and dump it into native Access tables. Sometimes the data is just coerced, sometimes it needs to be manually converted, but when that is taken care of, everyone's using 'compatible' data.

 

[Legacy 456155]

It's not a deal breaker in this project. I have a single field in the entire BE that uses BIGINT. Well, it used to. I just cast it from a string to a TempVar(LongLong). I'm just curious if there is some sort of setting in ADO COM or the connection itself. No big deal. I didn't have a plan to fix it, but saw your mention of BIGINT here and thoughtlessly hijacked this thread.

 

[Micron]

saw your mention of BIGINT here and thoughtlessly hijacked this thread.

Usually I notice that but not this time!