I have come across an issue with MS Access databases where if I logon using the 3 tier logon (Globe) then when I use dates in a where clause the sql generated puts the dates in single quotes as per an sql query. However if I use the desktop logon then the sql generated puts the dates in the following format
PupilOnRoll.EntryDate BETWEEN {d ‘2010-09-05’} AND {d ‘2010-09-07’}
Which works fine against the access database.
Now if I save a report that has run fine on desktop logon and load it into a globe or 3 tier logon then the sql generated is as per the sql generated on the desktop logon.
I am erally confused by this and have checked the prm settings on my local machine but cannot find a solution to the issue.
Hi the error message using 3 tier login is as follows:
A database error occured. The database error text is: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression… (WIS 10901)
i.e. with 3 tier login against an MS Access database with dates in the where clause the generated sql puts dates in ‘01/09/2000’ format but in 2 tier login or desktop login the date format in the generated sql is {d ‘2010-09-05’} which works.
The single quote format does not work against an MS Access database but the {d ‘2010-09-05’} format does.
Obviously, it seems to be something to do with middleware connectivity / data access set up on the server.
Have you tried running a report, directly, on the server itself?
Check the connectivity on there, version of MDAC compared to your client and the way that the ODBC has been set up e.t.c
Go through the list I gave you, also, it covers where most things can go wrong.
I have done everything I can do from my end ( I do not have direct access to the servers) but to no avail. My issue is related but not the same, it’s not the actual date format that is the issue in my case but the format of the generated sql when using an access database. I do agree that it seems a server-side setting but what could it be? I have attached my access.prm file and sqlsrv.prm file from the servers to see if anyone can break this down further.
Compare these to the ones on your local machine, that is what is used when you run this in full client, non ZABO.
As I said you should check the MDAC version, on the server and the one on your machine.
You should also check whether the DSN has been set up exactly the same, on the server, as it is on your client machine.
You should also check the server side language settings, as descibed in the link.
I appeciate you don’t have server access, but that is where the problem lies.
I will do a side by side comparison shortly however does what you are saying imply that if I copy the prm file from my local pc settings to the server then all will be well?
If you change the server side prm, you will have to restart the connection server. From memory, in older versions, you had to re-boot the machine.
However, you may need to check the other things, also, as contained in the check list I linked to.
That list is the most comprehensive I know, if you answers not there, I don’t know what to suggest.
Just wanted to let you know that it worked simply by replacing the prm files on the server with the files from my local machine, and by getting our IT team to go through the checklist.
(BOB member since 2010-04-16)
(BOB member since 2005-01-06)