Aug 26 2021 08:31 AM
Aug 26 2021 08:31 AM
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"/>
<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"/>
<server name="otherserver" hostname="DBHOST2" port="60000"/>
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!
Aug 30 2021 05:17 AM
Aug 30 2021 05:40 AM - edited Aug 30 2021 07:20 AM
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.