jwcolby
jwcolby at colbyconsulting.com
Sat Mar 3 10:05:32 CST 2012
As I have discussed previously I am building a small framework for refreshing ODBC links to SQL
Server tables and views. The objective was to provide an object oriented programmatic method of
relinking a table in Access, dynamically switching the database name, server name, or in fact any
part of the connect string. The framework is pretty much finished and I am about to start using it
at the client to allow me to switch between production and test databases.
If anyone is embarking on doing this kind of thing and thinks they would like to look at or use my
framework, email me offline and I will send it to you. If you have questions or comments, please
use this list for that discussion
The framework is an MDA library and consists of three module and four classes. The four classes
consist of:
Example syntax **************
cTDFs is a method in a module which creates an instance of clsTDFs and returns a pointer to it.
clsTDFs.mInit loads every ODBC linked table into the cache
clsTDFs.mTDFJITFactory "MyTblName" loads a single table into the cache. This allows JIT loading of
the cache.
clsTDFs.cTDF("MyTblName") gets an instance of clsTDF for a table name specified. If the table is
not in the cache yet it is loaded first.
cTDFs.cTDF("MyTblName").cCnnProperty("Database") = "SomeDbName" sets the database property of
MyTblName to "SomeDbName". It does NOT modify the connection srtring yet.
cTDFs.cTDF("MyTblName").cCnnProperty("Server") = "MyTestServer" sets the Server property of
MyTblName to "MyTestServer". It does NOT modify the connect string yet.
ctdfs.mTDFRefresh "MyTblName" modifies the connection string for MyTblName to the new (modified)
information. After this call the table is relinked using the modified information.
Classes **************
1) clsCnnProperty **************
Stores the name / value pairs for the properties (as I call them) of the connection string. A
connection string consists of things like:
Database=MyDB;
Server=SomeServer;
Driver=SQLServer;
and so forth. Each of these properties is parsed out of the bigger connection string and stored in
instances of clsCNNProperty
2) clsTDF **************
A single linked tabledef. All of the code and properties to work with a single tabledef. Each
linked table has a connect string that I parse and store. I store the original connection string
and then parse the connect string into instances of clsCNNProperty as described in #1 above.
Once I have done that I can modify any of the properties, for example I can change
Database=SomeDBX;
to
Database=SomeOtherDbY;
and then update the tabledef's connect string. When that is done, the table now points to a
different database.
3) clsTDFs **************
The supervisor for finding odbc linked tables, a class factory to build clsTDF instances and a
collection to store the instances, as well as methods and properties to manage all that stuff.
ClsTDFs can load individual tabledefs into cldSTF instances as required, or it can just scan every
linked TDF and load every ODBC linked TDF into the cache at once. As the TDF is loaded into the
cache it parses as described in #2 above. clsTDFs has methods to allow relinking just individual
tabledefs or every tabledef in the cache.
4) clsTimer **************
a timer class to allow me to observe the time required to do the loading and so forth
As you can see, the framework allows me to manipulate the connection string using a well defined
object oriented programming interface. I simply reference C2DbODBCRelink and then I can call object
methods and properties of the interface to modify the connection string as I require.
If you are interested in watching it work or actually using it in your projects, email me off-line
to avoid "me too" emails.
--
John W. Colby
Colby Consulting
Reality is what refuses to go away
when you do not believe in it