1

I am developing a system in Access talking to a Sql Server backend. I can connect with two separate accounts A and B so that I can control permissions. In particular I have a view which is accessed via a pass through query which is denied to A but allowed by B.

Normally selection of A or B as the login is related to which Access Security Group the user belongs in, but I have set it up so that people in the Admins group (ie me) read the login from an internal access table. I have also created a form (and associated code) that allows an Admin to change this value.

This all works great and does its job perfectly - provided I start up Access from scratch.

It detects I am admin, reads the last value I set in the internal table, connects to the server with the correct login string (I loop deleting and re-creating all the tabledefs using this new connection string) and then displays my first form. I navigate to a button that runs the pass through query. When I click that button it recreates the pass through query, by deleting one with the same name and recreating it with the correct connection string (A or B login) before then running it to output results. If I am A, then it fails with a permission error (which I display and inform the user about), if I am B it works and I get the results.

I have added a system to attempt to change this on the fly for testing purposes. Having changed who Admin should login as (by writing to an internal table), it recalls the startup code, which loops through deleting and re-creating tabledefs and then puts me back at the intial form.

HOWEVER - If I now navigate to the button that runs my permission controlled query, it still deletes and re-creates the query def from scratch, but when I run it, it seems to run in the context of the SQL Server Login it set when I first started access, and not the new SQL Server Login I have just re-created everything with. So the query will run when it shouldn't (of visa versa).

If I exit Access and try again - it starts working properly again.

The only conclusion I can draw from this is that somewhere inside of Access it is caching the ODBC connection string - and instead of using the new one is using the old.

So my question is - is my conclusion correct, and if so how can I tell Access to clear its cache.

I am developing in Access 2010 - for a system that will ultimately be running in an Access 2000 environment - so the file format is an .mdb in the Access 2000 format.

akc42
  • 4,893
  • 5
  • 41
  • 60
  • 1
    I have just come across this blog post which seems to confirm my conclusion about caching the connection string http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx – akc42 Sep 11 '11 at 08:35

3 Answers3

3

I came to this topic because I had the same question: "How to clear the cache in Access 2010?"

In my case, the problem was that my application somehow "remembered" the entire path to my linked photos, even though I referenced only the file name. One of the links above lead me to search under "File > Current Database > Caching Web Service and SharePoint tables." The option to "Use the cache format that is compatible with MS Access 2010" was already checked, but I enabled the check box for "Clear Cache on Close" and closed the database.

Voila! All previously cached values, including the values for my linked photos, were cleared out. It doesn't appear that this setting affected my ODBC DNS-less connections, but I haven't confirmed this.

**TO CLEAR CACHING, go to File-->**OPTIONS-->Current Database, and scroll down to Caching Web Service and SharePoint tables.****

Community
  • 1
  • 1
DUHdley d'Urite
  • 187
  • 3
  • 15
1

As far as I know, there is no way to clear this cache. If you execute a query and supply a different UID/Password for that query, then the permissions you obtained from that act will remain in effect until such time you close down Access.

Thus if you execute another query and supply a "differnt" UID/password, and then later own execute another query with “lower” permissions, the other cached UID/password will be used. So you can (and will) have multiple UID/passwords cached at this point in time - you have no control over which one is used.

The only way around this would be to adopt a separate ADO query – this to my knowledge does not cache the credentials like when using DAO queries.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
1

Can this page about ODBC linked access password reset be what you're looking for ?

iDevlop
  • 24,841
  • 11
  • 90
  • 149