When it came to creating DSN-Less Linked Tables I had always used a procedure that
deleted the TableDef and appended a new one until a problem occurred. The code I was
using to save Linked Tables as DSN-Less Tables was not working with some of the Views
in SQL Azure. This was a serious problem because the application I was developing would
be distributed to clients who would then distribute it to their clients. We did not want
to use a DSN file. But now the code that normally worked without a hitch was failing.
Because I was developing for SQL Azure, I had to use SQL Azure Security which includes
the Username and Password in the Connection string. Even though I explicitly set the
dbAttachSavePWD (Enum Value: 131072) when I appended the new TableDefs the Connection
Property of my views still did not include my Username and Password. So I quickly wrote
some code to loop through the TableDef properties to see if I could discover the problem.
I discovered that the TableDef Attributes of the Views for which my code was not
working was 536870912 but for the Tables and Views that were working it was 537001984.
After checking the TableDefAttributeEnum Enumeration values I was puzzled. The
Attributes value for the Views which were not working was 537001984 which is the
value for dbAttachedODBC (Linked ODBC database table). And the value of the Attribute
for the Tables and Views that were working was 536870912 which is not in the list.
After a few moments I figured it out. I saw that if you add the dbAttachedODBC value
of 536870912 to the dbAttachSavePWD value of 131072 it equals the 537001984 Attributes
value of the DSN-Less Tables and Views that were set properly. This made sense since
the documentation Description for dbAttachSavePWD is "Saves user ID and password for
linked remote table". Apparently the Views needed both Attributes. But how could I set it?
Even though my code explicitly set the Attributes value to dbAttachSavePWD when creating
the new TableDefs it was not working. Eventually I found some code that used the
TableDef.RefreshLink Method, added the TableDefs Attributes dbAttachSavePWD (131072) value,
and tested it. This solution worked. Below is the code.
I felt better about using the tdf.RefreshLink Method rather than deleting the TableDefs
and appending them again. I read that you could delete your TableDefs and not be able
to append a new one if there is an error in the Connection string at
this page on Doug
Steele's website at the bottom of the page.
I found an interesting discussion
about whether to delete and then append a new TableDef or use the RefreshLink Method on
Access Monster. However the latest Developer's Reference documentation settles the matter
for me when it states the TableDef.RefreshLink Method "Updates the connection information
for a linked table (Microsoft Access workspaces only)."
You may also want to see the sample code
from The Access Web by Dev Ashish using the RefreshLink Method.
Below is an example of the code used to get the Connection string. As I stated in the procedure
notes I never use "Connection" in Constants, Variables, or Procedure names. Nor do I use cnn, con,
cnnString, etc. Instead I disguise the name of my Procedure to make it hard for a hacker to use
to get my Connection string. Constants and Procedure names, along with some variables, are easily
seen by opening up even an accde or mde file with a free Hex editor unless you have encrypted the
database file. If I can see the name of your Constant I can very easily get its value.
You can see or download the code used in this article from our
Free Code Samples page.
Need help with a database or Excel?
We can help!
We use Microsoft® SQL Azure™ to provide safe and affordable 24/7 internet access to your company's vital information.
Contact us for a Free Consultation, feedback, or more information:
Page layouts based on Matthew James Taylor's Perfect multi-column CSS liquid layouts
© 2008-2010 Patrick Wood