Story

I'm working on a project for a business which is working with an MS Access database. We converted the MS Acces db to an sql db without adding relations. And succesfully imported them into Acces. In some tabs on the form the data works, others not.

Question

We are using Forms in Access and it works fine when used with the MS Access tables. But when we switched to MS SQL some parts of the form stopped working. From what we can tell it seems to be related to dates but it's really hard to debug these kind of errors.

How could we fix or figure out what causes these errors?

3238: ODBC--data out of range.

error

Update

In the mean time we have edited our ODBC driver to Native SQL 11 We get this new error. We found out it is this query that is causing errors. We think it is a date format issue, but don't know how to solve it.

SELECT [tbl_Accommodatie]![Gemeente] & " " & [tbl_Accommodatie]![Naam] & " " & [tbl_Accommodatie]![Adres] AS Accommodatiegegevens, 
tbl_Onderwijstype.OnderwijstypeKort, tbl_Sporttak.Activiteit, 
[tbl_Sporttak]![Activiteit] & " - " & [tbl_Programma]![TitelActiviteit] AS txtActiviteit, 
IIf([tbl_Programma]![Reservatie]=1,"OK",IIf([tbl_Programma]![Reservatie]=2,"Geweig.",IIf([tbl_Programma]![Reservatie]=3,"Onbek.",IIf([tbl_Programma]![Reservatie]=5,"OK, maar","Geannul.")))) AS Status, 
tbl_Accommodatie.Gemeente, tbl_Sporttak.InschrFrmBAS, tbl_Programma.ProgrammaID, tbl_Programma.TerreinKeuze, 
tbl_Programma.UrenReservatie, tbl_Programma.Datum, 
tbl_Programma.InzetPersoneel FROM ((tbl_Programma 
INNER JOIN tbl_Sporttak ON tbl_Programma.SporttakId = tbl_Sporttak.SporttakID) 
INNER JOIN tbl_Accommodatie ON tbl_Programma.AccommodatieId = tbl_Accommodatie.AccommodatieID) 
INNER JOIN tbl_Onderwijstype ON tbl_Programma.OnderwijstypeId = tbl_Onderwijstype.OnderwijstypeID 
WHERE (((tbl_Programma.Datum)='2020/01/01') 
AND ((tbl_Programma.SchooljaarId)=IIf([forms]![frmMenuAlgemeen]![chkMetVorigSchooljaar]=False,[forms]![frmMenuAlgemeen]![txtGekozenSchooljaar],[forms]![frmMenuAlgemeen]![txtGekozenSchooljaar]-1) Or (tbl_Programma.SchooljaarId)=IIf([forms]![frmMenuAlgemeen]![chkMetVorigSchooljaar]=False,[forms]![frmMenuAlgemeen]![txtGekozenSchooljaar],[forms]![frmMenuAlgemeen]![txtGekozenSchooljaar])));

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

CodePudding user response：

Ok, if you done a good migration, then TOPS on the list?

Well, if you use the built in (legacy) SQL server driver, then datetime2 columns will appear as text. in fact STOP ALL AND EVERY CHANGE you are making to try and fix this.

there should be ZERO, I repeat ZERO changes in regards to datetime issues. You going down a path, trying to fix things, when ZERO changes are required here.

the 10 second test? Open a linked table in design view, and check if the date/time columns are seen as date time, or AS TEXT!!!!

If they are seen as text, then that is your HUGE problem, and trying to fix code and queries IS NOT REQURED!!! - I repeat, NOT required. So, you on a wild goose chase when none is required.

There are two solutions to the datetime issue: If you used the Access migration assistant, then BEFORE you migrate, change the mapping to use datetime on sql server, and NOT datetime2

Second solution: Install adopt the native 11 (or later I think 17 or 19 is now the latest) SQL driver. But, when you do this, YOU MUST RE-LINK. So, it looks like you updated the sql driver to a later native driver, but you ALSO must remember to re-link your tables and be 100% (no, 200%) sure that you re-linked the tables using the newer drivers. After doing so, then again, that 10 second test of flpping a linked table into design view in Access - and again be 100%, 200% sure that the datetime columns are indeed being seen as datetime.

So, you have two choices - and only you can decide which is less work.

Don't use datetime2 data types in sql server tables. This will ensure that any and all work stations that run the applcation will work.

Do accept and use datetime2 columns, but then you MUST re-link using the newer "native 17 or later odbc drivers, but you NOW also MUST install that driver on each workstation. This can be a pain in some cases, but if you update the odbc driver, and it is a very good idea, then you MUST as noted ensure you install that same newer driver on all work stations that run Access. And of course it must be the SAME version odbc driver.

So the built in "legacy" sql driver is older, not the best but it does mean you don't have to change or setup or install the odbc driver on each work station (it is by default installed on all versions of windows - and has been that way for at least 20 years now.

So, do NOT go on a wild goose chase of changing VBA code, forms, and your sql queries.

If you migrated to sql server, and ALSO check the table in design mode, and it sees the column as date time?

THEN ZERO ZERO ZERO changes are required for those existing quires and forms. There are and can be some other changes, but datetime is NOT ONE of those changes. So, as I stated, STOP what you doing right now, fix the above issue. If you don't, you can spend the next two weeks chasing down datetime errors and issues when NO CHANGES are required in this area.

As noted, I very much prefer the newer sql drivers, but the big downside is that you have to install the newer odbc drivers on each work station. As a result, in some larger corporate deployments, we continue to use the "legacy" or so called "non native" sql drivers. (the Sql Server driver). But, with this choice, then we have to be VERY careful to not use, nor introduce datetime2 columns into the database, since they are seen as text columns by the built in legacy driver.