Seemingly getting wrong connection string from tabledef

Using Ms Access ...

I am trying to change the user to which I am connected to a SQL Server Database from Access. The startup form from my Access Application dynamically connects to the tables via an Altered version of a routine posted on Microsoft's KB. In Essence it deletes the tabledef that matches the name localName you call it with, Calls Currentdb.CreateTableDef to create a new one with the appropriate new connection string and appends it the the CurrentDb.TableDefs collection. As far as I can see, its the append function that actually contacts the database and throws an error if your connection string is wrong.

In my case, I did an original pass through all the tables in my database calling this routine with an old (standardised - ie I normally use this) string and all tables connect fine.

I am now trying a second pass through this start up form with a new user/password combination so as to test different permissions in the database.

I have then built a new connection string with a revised name and password and am currently single stepping through the function I just described.

I have a variable td which contains the newly created tabledef and I am just prior to appending it to the Currentdb.TableDefs collection

In the debugger "Immediate" window ?td.connect gives the connection string with the newly formed connection string correctly and ?td.name gives tblConfig as the table name.

Just to ensure the TableDef has been correctly deleted from the current collection I did ?CurrentDb.Tabledefs("tblConfig").Connect from the immediate window and was rewarded with a dialog box that said the collection didn't contain a member with that name

I then single step over the line

CurrentDb.TableDefs.Append td

And now call ?CurrentDb.Tabledefs("tblConfig").Connect again from the debuggers Immediate window and the response is the old connection string not the new one.

There is an OnError active in this routine, but It is not activated during the single stepping.

The only explanation I can think of for this, is that somehow the old connection string is stored on disk as a result of saving the database at some point and append doesn't copy across the开发者_高级运维 connectionstring when it is finally appended.

Does anyone have a better explanation?

---

It turns out that the connection string is indeed cached

http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx

---

I had an incorrect password for the new connection string (one character was wrong) and it appears that access does not store the new username and password when that problem happens.

Somehow, despite deleting the old tabledef with the old username and password, Access was able to get it back - maybe it was cached, I don't know - and use it. Obviously that worked and so it carried on with that string rather than the new one.

It seems that because the old string worked, it didn't bother to throw an error, so I was now connected to the table with a server login that didn't have the same permissions as I expected.