We are having a really weird issue and I hope someone can help us figure this out.
We have a 32-bit server Citrix server that is running a 16-bit application. The application is written in VB 3 and uses ODBC and MS Access 2.0 (linked tables) to connect to a SQL Server database on another server.
The production copy of the application is working fine on this machine. However, we are now trying to set up a test copy as well to test a new release of the application with minor bugfixes in it, and we are having trouble with the new test ODBC connection showing up in Access 2.0.
If Jim (administrator on the machine) creates a system DSN through the regular 32-bit ODBC Administrator, he can see the DSN through Access 2 when he goes into File / Attach Table.
Anyone else logging on cannot see the DSN Jim created through Access 2.0 through File/Attach Table. (It shows up just fine in ODBC Administrator).
If Cary (also an admin) creates a system DSN the exact same way as Jim, he CANNOT see his DSN, until he copies c:\windows\odbc.ini into c:\documents and settings\<Cary's username>\windows and overwrites the copy of ODBC.ini he already has. Then it works fine for him. But no one else can see the DSN except maybe Jim (untested, Jim was away when we tried this step).
When I log in (power user, not admin), I can't see the DSNs either Jim or Cary has created. I don't have a Windows folder in my profile directory. I had a roaming profile, so we tried changing my profile to local like Jim and Cary's and it didn't help. They even tried making me an administrator on the machine. No change. If I manually create a windows directory and copy odbc.ini into it, it makes no difference. If I create a DSN, I can't see it in Access 2.0.
We have tried having Cary or Jim do the linking to the table, thinking that perhaps even if I can't see it under my ID to link it, the linkage might still work if it exists - no luck. When I try to open the linked table in Access 2.0 it says "ODBC-connection to <dsn name> failed." followed by "Can't open table in Datasheet view."
If I try to create a new DSN from within Access 2.0 (File / Attach Table / <SQL Database> / OK / New... and choose "SQL Server (32 bit)" I get an error saying "The setup routines for the SQL Server (32 bit) ODBC driver could not be loaded. You may be low on memory and need to quit a few applications."
This same error occurs for me, Jim, and Cary.
We have a second Citrix server that is set up with the same application and we checked to make sure it isn't just this machine being weird, and it isn't - we get the same behaviour on the other server.
I've checked ODBC.ini and the registry keys associated with it, and the entries for the production DSN that shows up in Access are identical (except for name and server) to the test DSN that does not show up.
We've made some huge progress. It turns out that my roaming profile had a copy of ODBC.INI stored in it (on my H: drive - home directory - h:\windows) and when I deleted it, the next time I tried to launch the application the INI was recreated, complete with the new DSNs.
I still don't know WHY it is doing this, but it might be specific to our extremely-locked-down weird setup that our computers run under.