jwcolby
jwcolby at colbyconsulting.com
Sat Feb 25 19:15:28 CST 2012
Dan, I have written a framework in VBA for dealing with the ODBC links, basically a pair of classes and a module. I am able to load and parse all properties of the TDF connection string such as Database, Server, UID, PWD and so forth. I am able to modify any existing property (such as the database) and write the modified string back to the TDF.Connect property. I am able to create new properties (ADD a UID for example) and delete existing properties, writing the modified connection string. All that is working very well. It looks like it takes about .6 milliseconds to load and parse a single TDF connection. YMMV of course based on workstation speed. What I envision doing with all this is to allow me to swap out the database name in the connection string. I have already done a backup and a rerstore to the same db name + _Test. DISCO becomes DISCO_Test. Once I have that backup of the production database I can swap the database name from DISCO to DISCO_Test to "instantly" point to the test database, and then swap it back again to point back to production. I haven't yet decided to use a naming convention like this, or a table of records with the database production / test names in a pair of fields. A table would allow me flexibility of course, just pull the database property and look it up in the production column, then grab the test name and write that back into the connection string. Vice-versa to move from production to test. I already have a CopyAndRun system in place for copying the database from a production location to a local directory. I could just add this stuff into that to automatically relink to test if the copy is going to a test local directory or something like that. Basically a single FE could dynamically switch from production to test as it loads, based on... John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it On 2/24/2012 10:12 AM, Dan Waters wrote: > Hi John, > > It's apps and databases all on one server. For example, there is an > appPROD.mdb and a dataPROD.mdb (or SQL database), and there is an > appTEST.mdb and a dataTEST.mdb (or SQL database). The parallel systems do > not work together or communicate with each other. > > In the PROD system, users log into an AutoUpdater file, which will upload > the latest FE files to the client PC if needed. Users don't run FE apps on > the server. > > HTH! > Dan > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Friday, February 24, 2012 6:48 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Testing SQL Server linked FEs > > Dan, > > Are these parallel systems physical servers, installed instances of SQL > Server or databases? It sounds like you are talking about databases. > > John W. Colby > Colby Consulting > > Reality is what refuses to go away > when you do not believe in it > > On 2/17/2012 1:25 PM, Dan Waters wrote: >> I have parallel systems: Production, Test, and Review. Test is just >> for me to do usual test stuff. Review is for users when they want to >> look at a change I've made so they are certain it works like they wanted > it to. >> >> In SQL Server, prior to using either Test or Review, I make a backup >> and then restore that backup as DatabaseTEST and DatabaseREVIEW. Each >> different access app file has ODBC links which point to its respective >> database in SQL Server. >> >> Each system has its own Config.mdb file, which holds the path for the >> table links. If I'm done testing, I can copy the FE.mdb file from the >> Test folder to the Prod folder. Part of the startup code is to check >> to see if the table links in the FE.mdb match what's in the Config.mdb >> file. If not, then a procedure to redefine the .Connect property of the > table links is run. >> >> I have this same setup for each customer on their server, which I can >> log onto via VPN/Remote Desktop. >> >> HTH! >> Dan >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby >> Sent: Friday, February 17, 2012 11:29 AM >> To: Access Developers discussion and problem solving >> Subject: [AccessD] Testing SQL Server linked FEs >> >> To this point I have always worked with MDB data stores. When I did >> testing where I needed to modify data (adds/ deletes / edits) I would >> copy the BE files to a local directory and then map that local >> directory to the X: drive where all the links pointed to. Voila, "test > mode". >> >> Obviously that doesn't work with links to SQL Server. I have seen >> code for modifying the link info of the tabledef, storing the server / >> database info in a local table in the FE. That seems like one >> solution. Is anyone doing something like this? >> -- >> John W. Colby >> Colby Consulting >> >> Reality is what refuses to go away >> when you do not believe in it >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >