[AccessD] C2DbODBCLink - framework for relinking ODBC links

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



More information about the AccessD mailing list