Access ODBC does not recognize automatic client reroute

[bubba35]

Hi,

I have an ODBC connection to a DB2/LUW database in MS/Access (2016) and am trying to implement ACR (Automatic Client Reroute).

I have added the following (bold) to my db2dsdriver.cfg file:

<database name="MYDBASE" host="DBHOST1" port="60000">

<parameter name="hostname" value="DBHOST1"/>

<parameter name="port" value="60000"/>

<parameter name="AltHostName" value="DBHOST2"/>

<parameter name="AltPort" value="60000"/>

<acr>

<parameter name="enableAcr" value="true"/>

<parameter name="maxAcrRetries" value="10"/>

<parameter name="acrRetryInterval" value"1"/>

<parameter name="enableseamlessACR" value="true"/>

<parameter name="enableAlternateServerListFirstConnect" value="true"/>

<alternateserverlist>

<server name="otherserver" hostname="DBHOST2" port="60000"/>

</alternateserverlist>

</acr>

</database>

I have tested this on my machine and it successfully falls over to the alternate server when the primary server is unavailable.

However, when I attempt to connect to the database in MS/Access, I get a connectivity error (SQL30081N)

Has anyone worked with MS/Access and ACR? Thanks!

[#2]

Does the connection work if you DO NOT try to include the automatic reroute?

[#3]

Yes. Normally Access connects to the database with no issues. With the ACR parameters defined, I can still access the primary database with no issues. However to attempt to test ACR, I changed the port number in db2dsdriver.cfg and db2cli.ini, for the primary database, to a bad port number, to make the primary database inaccessible, so ACR would take over and connect to the alternate database. At that, it does successfully connect to the alternate database when using db2cli. However, it doesn't in Access. Thanks!

[#4]

@bubba35 

What that tells us, then, is that this is not a viable method for ODBC Access connections. It also tells you that using db2cli is a different environment where it does work. 

I would, therefore, look at an alternate approach which doesn't rely on trying to use this approach as is.

What I would do instead is create a function in VBA that tries to connect to your primary database using the default parameters. If that succeeds, you should then be able to proceed on to work because Access will cache that successful connection for the remainder of the session. However, if it fails in this function, your code would then try the alternate connection. Again, if that succeeds, you are good to go. In other words, instead of trying to make the fall-back inside one config and ini, create two versions and let Access try them in turn before settling on the one to be used for a given session.

Look at this discussion (which uses MySQL) for a pattern that could be modified to this purpose.

I realize that it's less than ideal, but sometimes getting the job done takes precedence.

[#5]

Wow, very good stuff there. Thanks George, I will look into this option.