Heenan, Lambert
Lambert.Heenan at aig.com
Tue Jan 7 13:24:24 CST 2014
Cross-posts to Access-L and AccessD. Hi everyone, and a Happy New Year to you all. My year begins with a puzzle regarding connecting via ODBC to an Oracle schema. This is all happening in Access 2010. Trying to keep this brief: I have a system DSN which allows me to connect to the production schema (which we'll call PROD) and another system DSN that connects to the development schema (DEV), and I have DAO code that quite happily allows me to switch back and forth from PROD to DEV and back again with no issues. Both of these DSN's use the same account ID, and different passwords. The need has now arisen to use a new account name (user ID) to connect to the production schema. So I created a new System DSN using the new account name. From within the ODBC Admin App (in Control Panel) I am to successfully test the connection. I can also connect just fine to the schema using TOAD using the new credentials. Then in Access I use the Get External Data/ODBC Database ribbon option to link to the tables in the schema. I check the box to save the password and voila, the tables are linked, readable and updateable. I then grab the connection string for the ODBC linked tables and store it away for later use. Here is where the problem is. The DAO code I'm using to change the links to the ODBC tables creates a tabledef object... Set tdfLinked = CurrentDb.CreateTableDef(strTable, dbAttachSavePWD) And then it sets up the required fields in the tabledef object... tdfLinked.Connect = strConnect tdfLinked.SourceTableName = strSourceTable and then appends the tabledef object to the database TableDefs collection. CurrentDb.TableDefs.Append tdfLinked When I start off with the application linked to the schema using the new DSN and account name, and run my code to link to the development data (which has a different DSN and account name) there is no problem. The links all change as desired. But when I then do the reverse, run the code to change the links from development schema to the production schema, as soon as the 'CurrentDb.TableDefs.Append tdfLinked' line executes the Oracle ODBC Driver Connect dialog pops up, showing the correct Service Name, the correct User Name but asking for the password to be entered again. When I manually enter the correct password (which is already present in the connection string) I then get an error stating that the connection to the schema failed, and it shows the user name and passwords passed in - ***which are correct***! When I run the exact same code, but use the old account names and DSN for the production schema everything works. I can toggle back and forth between the development and production schemas with no problems. Does anyone have any suggestions as to how to resolve this? Lambert "A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools." ― Douglas Adams<http://www.goodreads.com/author/show/4.Douglas_Adams>, Mostly Harmless<http://www.goodreads.com/work/quotes/2842984>