Access 2010 & Citrix: Conversion failed when converting date and/or time from character string

Hi,

Weird phenomenon here:

When I logon remotely using Citrix an Access application works just fine. When a certain user logs on he gets this error message when trying to open a form (locally it works fine for him too) ... I've checked my and his regional settings: they seem to be the same.

Any other ideas what the problem could be!?

Thanks a lot!
XeronimoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
What is the character string?
Why do you use a character string at all for a date?

/gustav
0
XeronimoAuthor Commented:
I don't know ... there isn't supposed to be one? How could I identify what character string is causing the problem?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Any other ideas what the problem could be!?>>

 Corrupt profile is a possibility, but that's just a guess without any real detail.

 I'd have the app display the values at the point of the error in some way and see what it is exactly that is happening.

Jim.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

XeronimoAuthor Commented:
A corrupt profile on the Citrix server?
0
XeronimoAuthor Commented:
cactus_data:

This is the only date related thing I could find in that form: the SQL contains this : "WHERE (((dbo_tInscripActiv.dateSaisie)>#7/1/2012#)" ... does that help you?
0
Gustav BrockCIOCommented:
I guess it should be:

 "WHERE ((dbo_tInscripActiv.dateSaisie)>#7/1/2012#)"

and that seems perfectly valid.
So I'm out of ideas.

/gustav
0
XeronimoAuthor Commented:
Hm :/

I've tried to execute the actual query using Access via Citrix (and not just the Access app which is 'run-only'), same error message ...
0
Gustav BrockCIOCommented:
So what data type is dateSaisie?

/gustav
0
XeronimoAuthor Commented:
Datatype on the MSSQL server is 'datetime'.
0
XeronimoAuthor Commented:
And the weird thing is that locally it works. It's only when connecting using Citrix that this error message comes ...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<A corrupt profile on the Citrix server? >>

 Yes or the profile being used.   In any windows environment, each user has a saved profile with all their settings.   In some cases, these are roaming profiles which are used wherever they login.   In others, there is a seperate profile on each machine.

 The profile contains things like desktop settings, position of icons, regional settings, User registry values,  etc.   It's not un-heard of for a profile to become corrupt and give weird results as a result.

 If you've got a case where one user is having a problem with a copy of a DB another user is not having a problem, and they are running on the same server (meaning the same Office install), then a corrupt profile is a good possibility.

  It is possible of course that they are working with different data, which is what triggers the error and is why you need to look first at what each is doing.

  But if they are doing the indentical same thing with the same DB and one gets and error and one does not on the same server, then that points to a corrupt profile.

Jim.
0
XeronimoAuthor Commented:
Hm ... I've deleted the profile of that user on the Citrix server (in the 'documents and settings' folder), I've reconnected using Citrix (with that user's name and password), I've started the Access app > same problem :(

The regional settings seem legit though. But there has to be a date problem somewhere ...
0
Gustav BrockCIOCommented:
But there are no strings here. That should be if the field was datetime2 which often is returned as string by the ODBC driver.

/gustav
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The profile is a registry key....I would leave it until your sure it's the problem.   And if you do mess with it, you can do that through the GUI or regedit.   I use regedit and I always make a backup.

 Also I just re-read your question; it works OK if he logs in locally on the server?

Jim.
0
XeronimoAuthor Commented:
JDettman: it works when the user accesses the DB using his local front-end (a certain MDB). But it doesn't work when that same MDB is being used via the Citrix server (for this user; it works for me but I'm an admin > some permission problem on the Citrix server?).

It seems to me like the problems only started though when I upgraded Access from 2007 to 2010 on the Citrix server ...

The regular users also can't log on locally to the Citrix server.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
So if you take a copy of the local FE, and put it on the Citrix server for him, it doesn't work?

Then if you take that same copy and put it on the Citrix server for you, it works?

That suggests it is his profile on the Citrix server.

Jim.
0
XeronimoAuthor Commented:
Well, the published Citrix application starts an network MDB file using the Access installed on the Citrix server: "C:\Program Files\Microsoft Office\Office14\msaccess.exe" "U:\mydb.mdb" /runtime

When the user is logged on to his workstation he can start that same mydb.mdb and it works just fine.

When I log onto the server that hosts Citrix locally as an admin I can start that same mydb.mdb and it works fine.

Argh, this is a mess ... I should start again in a more coherent way as to when it works, when it doesn't, which permissions the user has, etc ... !?
0
XeronimoAuthor Commented:
And before upgrading to 2010 it worked ...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<When I log onto the server that hosts Citrix locally as an admin I can start that same mydb.mdb and it works fine.>>

 You need to compare the same thing; what happens when you try to run it on the Citrix server as a published app just the same as when he does when he gets an error?

 or if he logs onto the Citrix server locally, does it work like it does for you?

Jim.
0
XeronimoAuthor Commented:
Ok, so when I run the published app via my Citrix login > it works.
When I run the published app via his Citrix login < the app starts but when trying to open a form containing a date the error message comes.

And just to be clear: what the published app does is start an MDB file from the network using the MS Access that's installed on the Citrix server.

As for logging onto the Citrix server locally I can't do it with that other user, Windows keeps telling me I'm not allowed to even though I've added the user to Remote Desktop and Terminal Services users ...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Ok, so when I run the published app via my Citrix login > it works.
When I run the published app via his Citrix login < the app starts but when trying to open a form containing a date the error message comes.>>

 So it sounds like his profile is messed up in some way.  Your using the same server, same install of Office, same DB...there's not much left other then that.   could possibly be permissions in some odd way, but an easy way to check that is make him part of the admins group on the machine and test.  If he still gets the error, it's not that.

 What OS is this running on by the way?

Jim.
0
XeronimoAuthor Commented:
The OS of the Citrix server is W2003 ...

Hm, it seems it's a permission problem after all!? I've added the user to the local admin group of the Citrix server and now it works!
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Hm, it seems it's a permission problem after all!? I've added the user to the local admin group of the Citrix server and now it works! >>

 My guess then would be a registry key that they don't have access to.  You can view the registry keys being accessed with Process Monitor:

http://technet.microsoft.com/en-us/sysinternals/bb896645

 Just be sure to filter only on that process, and start the trace just before you execute the process.  There will be a lot of them<g>.

 It also can monitor files, so you can check that as well.

Jim.
0
XeronimoAuthor Commented:
JDettman: thanks but for now I'll just leave him in the admin group, he can't do (or doesn't know how to do) anything else with this. I don't have the time right now to figure out that registry problem. Especially since it might also have to do with the fact that normal Citrix users don't have access to local resources on the Citrix server? Maybe the problem stems from there ...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Especially since it might also have to do with the fact that normal Citrix users don't have access to local resources on the Citrix server? Maybe the problem stems from there ... >>

 and that is the issue.  Citrix servers are normally "locked down", meaning end users can't get to a lot of things (like the run box off the start menu).  This includes commands, folders, and registry keys.

 As you started off, "Weird phenomenon here".   I can't think of what would cause a problem with processing a date like that.  But you've certainly proved it's related to permissions.

 I've had one problem like this in the past (combo box would be blank for some users) and it turned out to be that they didn't have access to a folder that contained a .DLL.

 Your going to need to use process monitor to figure out where the permission denied occurs and what it's going after.

  But at this point, you know for sure it's not an app error.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Citrix

From novice to tech pro — start learning today.