I am converting an old ADP to ACCDB. I have already reconnected the SQL server.
My development PC:
1. Windows 10
2. Local SQL Server Express 2019
3. Office 2010
4. ODBC 13 for SQL Server
My Customer:
1. Windows 11 or 10
2. Windows 2019 Server with SQL Server Express 2019
3. Office 2016
4. ODBC 13 for SQL Server
There is some errors that I cannot understand what is the cause and how to make a quick fix.
1. [Microsoft] [ODC Driver 13 for SQL Server] Restricted data type attribute violation (#0)
In my development PC, it runs without any error when I open a form. But in my customer PC, it encounters the error. It takes me some days to find out that it is due to the views in SQL Server.
It is due to the view definition, if a form that record source is linked to a view that containsin the definition, it will encounter the error. But I do not encounter the error in my development PC.Code:CONVERT(varchar(12), datacolumn1)
Why and is that a quick way to fix it?
2. Report with Date Time
After days of testing, I find that the ACCDB cannot execute SQL with data time column successfully.
This cannot be execute:
Code:SQL = "select * from table1 where expiredate > '" & DateFrom & "'"
I do not understand why and so I rewrite the string with sub SQL string using OA Date number.
The above SQL returns correct records in both my PC and my customer PC.Code:SubSQL = "(select *, CDBL(expireddate) as dateno from table1)" SQL = "select * from " & SubSQL & " where (expiredate is not null) and dateno=" & doubleDateFrom
However, the following SQL cannot return correct records in my customer PC. It returns empty (not null) but just no record. But it returns correct records in my PC.
Any suggestion?Code:SQL = "select * from " & SubSQL & " where (expiredate is not null) and ((dateno >=" & doubleDateFrom & " ) and (dateno <= " & doubleDateTo & " ))"
Thanks.
Alfred