Dan Waters
df.waters at comcast.net
Sat Feb 25 21:00:21 CST 2012
Hi John, Excellent! Each of my systems is in its own set of identically named folders (except the top folder which is Prod, Test, Review). Within the folder where the FE app file is located, I have a file named Config.mdb. During startup, I read the tdf.connect property string from a field in a table named tblParameters in Config.mdb. Once I've got that string, I compare it to the tdf.connect property for the last table in my FE app file. If they are the same, I skip the table link refreshing - if not then I initiate the table link refreshing. I initiate a progress bar to see make sure the refreshing is working when it's supposed to. The Config.mdb file is identically named and has identical table structure at all locations, so it can be used by all FE apps. Because the Config.mdb file stays in its location, it always has the correct tdf.connect string for that particular system and customer (or system on my PC). Also - one of my customers has the data tables in SQL Server, the rest use an Access file. They all use the same Library.mdb file. The Library file has a duplicate set of table links - one set is ODBC and the other is Access table links. In this file, if my system detects a 'SQL' in the tdf.connect string, and the ODBC links have 'SS_' as a prefix, then the access table links are first renamed with a 'AC_' prefix and then the 'SS_' prefix is removed from the ODBC links. It does the opposite in the opposite situation. By doing this I can use the same library file for both an Access BE and for a SQL Server BE. It's Gettin' Easier! Dan -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Saturday, February 25, 2012 7:15 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Testing SQL Server linked FEs 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 > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com