This question is not about dsn-less connection strings in MS Access per se (which I can build successfully). The following string worked just fine last week when connecting to our iSeries from MS Access 2010 on Windows 7. Over the weekend, our IT group installed a variety of anti-virus systems and now the string works intermittently. However, the behavior is extremely peculiar and takes a bit of explaining. This is the string:
ODBC;Driver=iSeries Access ODBC Driver;System=nnn.nnn.nnn.nnn;UID=myuid;PWD=mypwd;DBQ=mylibraryname;Initial Catalog=mylibraryname;MGDSN=0
It is assembled in a loop that runs through a table of connection parameters, building linked tables on the fly (first dropping them if they already exist then adding them to the tabledefs collection). There are about 12 linked tables which reside on two different iSeries LPARs (two different IP addresses specified in the "System" connection parameter). The tables reside in several different libraries so the parameters "DBQ", "System" and "Initial Catalog" vary in each iteration.
Today the loop only works for ONE of the LPARs (one IP address) and for the other I get error 3011 ("The MS Access database engine could not find the object MYLIBRARYNAME.TABLENAME..."). This error occurs when the following line of code attempts to execute:
CurrentDb().TableDefs.Append tblLinked 'tblLinked is of type TableDef
I can manually create the linked table objects using the MS Access 'External Data' menu for ODBC using named DSN's created with the Windows ODBC utility. I can refresh all linked tables using the Linked Table manager. I just can't use VBA to add linked tables to the tables collection for tables on one ip address, and then add linked tables to the other ip address in the same Access session. If I close and reopen Access, I can create a linked table pointing to either system using the same VBA. I just cannot subsequently create a linked table to the other ip address using VBA.
I looked at IBM's connection parameter options and added the parameters for DBQ, Initial Catalog, CONNTYPE & LIBVIEW, none of which were needed last week. I ran compact/repair and then after more research created a new database and imported all objects, then converted the db to a .mdb format (Access 2007) and ran compact/repair. No help.
What's nuts is that if I close the Access application and reopen, my code can append a tabledef to whichever ip address is mentioned first, but any attempts to append a tabledef that links to the other ip address fail with error 3011. Each time I close and reopen I can link to whichever ip address I want, but not the other. This is 100% repeatable. To be clear, I have to close the Access application, not just the current database. Upon reopening I can connect to the whichever IP addr I want, but not the other.
The code worked last week, but not today (first day back in the office after the long weekend).
I've decided that appending an ODBC tabledef to the tabledefs collection causes Access to verify the existence of the table, but not by using the connection parameters. Instead, the .append method triggers a verification process that uses some setting that persists but that I cannot find it to reset its value. How could connection information be cached and not reset by the connection string??
Can anyone point me to where I might look next?
