MS Access Table Access Error ! ! !

[samdazz]

One of my user gets the following error from MS Access running from his local machine and linking to the database & appropriate tables. The front end MS Access application uses back end SQL server to get info from. ODBC connections are in place, folder/file NTFS security for this user is also in place. he is admin on his PC. His machine uses DHCP to connect to the network. One thing I'd like to mention is: We have Citrix server on which this application is published and this user can connect and perform the same tasks using the Citrix client. He is having errors in connecting to the tables Only when he launches locally.

Recently he got new upgraded PC and since than this problem arose. He was OK on his older PC. Any ideas what & where else to look?

In my opinion, it must be some tweak that needs to be done on user's machine.

PS: The problem appeared only on this user's new PC, from his old PC he was able to get to the tables and all other good stuff. All other users in the same group do not have any problems at all.

Thanks
Sam

 

[Bilbo_Baggins_Esq]

Does your Admin/Rights database keep track of Machine ID? If so, has it been updated to reflect the new PC?

Have you tried removing the original linked tables and initiating new links?

If none of this works, I's suspect it is a problem with the ODBC connector on that PC.

 

[The_Doc_Man]

Bilbo is right that a new machine needs to be part of the consideration.

In a domain-based environment, machines have accounts independent of the users who run them. (Otherwise, domain policies could never be accessed at boot time - think HKEY_LOCAL_MACHINE settings in the registry.) However, if the person can log in to the domain at all, this is probably not the whole story.

One question is whether any Access Control Lists named the old PC. (This is rare and verges on idiotic for a machine account.) Or whether the machine account was listed as part of a group and the ACL calls out the group. (This is less rare and not quite as idiotic.) In the latter case, the new machine needs to be added to the group.

Another issue is whether the person's username changed for the domain with the advent of the new machine. OR... if the user's machine doesn't use local login security - i.e. always logged in as Admin user, then the bit about assuring the new machine's account has been added suddenly becomes more important.

The problem with MS Windows is that its security is sometimes a bit too complicated. You cannot forget that the security of a network-connected file (such as your shared MDB file) has a local permissions profile AND a network permissions profile.

Adding Citrix to the mix is an interesting problem. Search this forum for "Citrix" and see if you are having one of those problems. In particular, using Citrix, you are NOT using the SHARE profile - because to Citrix, the MDB is local. But when your user is running Access on his local machine, the mapped drive with the MDB is counted as NETWORKED and therefore trips on the SHARE permissions - which MINIMIZE against the local permissions.

(I.e. if the local says "Do what you want" but the SHARE says "Don't hurt me" - you can't hurt the MDB because the SHARE permissions stop you.)

An issue that you need to assure is that when the user logs in via Citrix and when he logs in locally that Access "sees" the same exact username. Citrix accounts don't always have to use the same username as the domain name. They are their own beasts.

Please be aware that if you publish Access via Citrix and did not obtain the appropriate type of license for it, that you could well be violating your Access end-user licensing agreement. Yeah, I know, who cares if Microsoft misses a nickel now and then? But your company might have policies on this topic that should not be ignored.

 

[samdazz]

Thanks for the helpful suggestions. I trimmed down the problem which truly looks like an ODBC connection string issue. I can get this user's tables linked to to our Dev. environment's databases using another connection string which pretty much the same form just different server but nothing works in Prod. When I test the ODBC connection, the test runs successfully. I checked the registry as well:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

There are couple of entries and my Prod entry looks like this:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Connection2Prod]
"Driver"="C:\\WINDOWS\\System32\\SQLSRV32.dll"
"Server"="ProdServ"
"Database"="master"
"LastUser"="User1"
"Trusted_Connection"="Yes"

This connection doesn't work although the Connection's Test runs successfuly at the end when I create a new ODBC connection.

The other ODBC connection (working connection, Dev. Environment) registry shows like this:

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Connection2Dev]
"Driver"="C:\\WINDOWS\\System32\\SQLSRV32.dll"
"Database"="master"
"LastUser"="User1"
"Trusted_Connection"="Yes"

As you can see there is no Server name in this registry entry of Server Name in the Dev ODBC connection.

I tried adding/deleting certain things in the registry and create/delete the ODBC connections but nothing works. I can see when I create the Prod ODBC connection it shows me the database 'master' already choosen & grayed out but in Dev ODBC connection it shows with a check mark and it gives me a choice of different databases which should be the way in Production as well. I already checked there are no temp files that are left behind on the PC as well.

Any suggestions

What could be done at this point. Its really annoying but hopefully we'll get the resolution soon.

Thanks

 

[samdazz]

Any more help !!!

Please post any more suggestions.
Thanks

 

[samdazz]

No more suggestions or what? Please post. Thanks