0

I am working on an Access database that has linked tables to a DB2 database via DSN ODBC connection.

All of the tables use the same System DSN since they're both in the same DB2 database. To get one group of tables, I use the system DSN "A" with one userid and password, and for the other group of tables, I use the same system DSN "A" as in the first group of tables, but with another userid and password.

This wrecked havoc in Access since if I open a table from the first group, it would cache the credentials and then if i tried to open a table from the second group without restarting Access, it would throw an Invalid credentials/username error.

Using this answer I was able to work my way around it: I created two different System DSN ODBC connections, one for each group of tables. This allows me to open the tables from either group without authorization errors in the same session (e.g. wihtout having to restart Access).

I was wondering, if there's a way in Windows 10 and Office 365 (via vba, AutoExec macro, changing the registry, policies, etc.) to change the default behaviour of CurrentProject.AccessConnection.Properties("Cache Authentication") from True to False

This property is readonly once the database is opened.

So another thought I had is: if you open a remote database with the cache authentication set to False and add it to your workspace in another database, can you run queries via DAO in VBA in that "remote" database?

For example: Database "A" has all of the linked tables to DB2. From another database "B" I open Database "A" and add it to Database "B"'s workspace with Cache Authentication set to false (<- is this possible?) vía OpenConnection? or OpenDatabase. Once Database "A" is opened from Database "B", would I be able to run on-the-fly queries (meaning no stored proc) in database "B" using VBA referencing the linked tables in Database "A"?

UPDATE According to Access 2003 VBA Programmer's Reference p.556 one can't specify advanced options using OpenConnection Snapshot from book

This question has been asked before several times:

braX
  • 11,506
  • 5
  • 20
  • 33
Jose R
  • 738
  • 2
  • 10
  • 26
  • So you have a working process. Seemed to have answered your own question. – June7 May 08 '19 at 17:58
  • @June7 sort of. I can fully work on the Access DB, but was wondering if there's a way to disable the cache of credentials in Access, sadly with work, you know, you have the minimum working solution and go on like in my case, hopefully someone will have an answer if disabling the Cache is possible. – Jose R May 08 '19 at 23:09

1 Answers1

0

Unfortunately, I am not aware this is possible.

You could however adopt (and should) adopt DSN less connections. This would thus eliminate the need to use a DSN. (the are a pain these days - especially with locked down machines). And this would allow you to change the logon/uid without having to re-link.

However, you STILL will quite much need two sets of tables if you need to get at both databases at the same time. You could also re-run the link code, but no doubt you been avoiding the re-link, and you want to just swap the DSN.

So, you CAN switch user/pass without a re-link. However, if you need to switch the database your pointing to? No way around this - you have to re-link the tables.

So, with JUST one set of linked tables, you can logon again (with different user/id), but to switch the database you pointing to, you have to re-link.

And no, you can't clear the password cache unless you exit, and re-load access.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Thanks, I tried to use DSN-Less tables, I was able to link to them and open them correctly. But the password caching still occurred, so I wasn't able to go down this route. – Jose R May 09 '19 at 01:55
  • Your question was worth a try! Been using Access + 16 years, and been using the password/uid cache trick for about as long. As noted, you can execute logons, and they are added to the cache, but there is no way short of a re-start of the application to clear it out. You could perhaps do a re-link code to swap the DSN- but then you just re-linking anyway. There is simply no way to get around this, since you can't clear out the cache, and this no doubt was/is the motivation for your question. Would love to give a different answer - but it is "no" you can't clear the cache. – Albert D. Kallal May 09 '19 at 02:14