[AccessD] Testing SQL Server linked FEs

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



More information about the AccessD mailing list