Yesterday I was given a brief to create a small tool that would update the ODBC Linked Tables in various Access databases. The catch: the database to which the linked table refers is moving platform (DB2 to Oracle) and has a new schema name. The former is easily manageable using the built-in Linked Table Manager in Access, but the latter is more difficult – the SourceTableName (called the ForeignName in Access’ internal schema) cannot be changed on an existing Linked Table, even programatically. Thus, my only option was the delete and recreate each Linked Table definition with the proper values.
Easier said than done. While attempting to append a new Tabledef to an access database, I encountered a rather vexingly obtuse of error;
System.Runtime.InteropServices.COMException: Could not find installable ISAM.
at Microsoft.Office.Interop.Access.Dao.TableDefs.Append(Object Object)
This error occured while attempting to Append() my new table definition on to the database. Google indicated that either my ISAM drivers were corrupted, or my connection string was wrong. Well, I’m trying to use ODBC not ISAM, so I assumed that the reference to ISAM and any related driver issues were a red herring.
Below is the C# code snippet that generated the error. (It has been simplified, removing many of the form-specific elements.) It is in essence reading a list of TableDefs from a list box (where their names had been previously populated, allowing the user to choose which ones to update) and then attempting to create a new TableDef with the same name but a different connection string and SourceTableName.
var dbe = new DBEngine();
Database db = dbe.OpenDatabase("C:\database.mdb");
foreach (String tableName in lstLinked.CheckedItems)
var tbdOld = db.TableDefs[tableName]; //load up the old TableDef for us to get values from
var tbdNew = db.CreateTableDef(tableName); //create a new TableDef with the same name
tbdNew.Connect = "DSN=JDETEST_ORACLE;DBQ=JDETEST ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;";
tbdNew.SourceTableName = tbdOld.SourceTableName.Substring(tbdOld.SourceTableName.IndexOf(".")); //trim the old schema name off the SourceTableName value
tbdNew.SourceTableName = "CRPDTA" + tbdNew.SourceTableName; //prepend the new schema name. resulting value should look like "CRPDTA.Tablename"
db.TableDefs.Delete(tableName); // remove the old TableDef ...
db.TableDefs.Append(tbdNew); // ... and append the new one
The connection string for tbdNew I drew from Access’ internal MSysObjects table – I created a new linked table to the new location, and took a look at the resulting object record;
This of course, is what led me astray. The Connect value in the table above and the TableDef.Connect property of a TableDef object are related, but not identical. The connection string in the code snippet above isn’t complete – I needed to have ODBC; added to the start (as below). I discovered this by examining the TableDef of the new Linked Table and noticing that the Connect property did not match the Connect table value above. Once I made this addition, everything started working fine;
tbdNew.Connect = "ODBC;DSN=JDETEST_ORACLE;DBQ=JDETEST ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;";
After running the code and examining the resulting Linked Table objects in MsSysObjects, I could see that ODBC; had been trimmed from the front, bearing out my theory. I suspect that without the ODBC; tag to tell Access that it is meant to be an ODBC linked table, it by default assumes that you are connecting to an ISAM data source, and starts looking for a driver – which it of course cannot find, leading to the ISAM error above.