[AccessD] Testing SQL Server linked FEs

jwcolby jwcolby at colbyconsulting.com
Sat Feb 25 22:31:56 CST 2012


So how do you feel about mdb vs sql server in your identical FE which can link to either?  I have to 
say that using stored procedures to return data sets for reports and the like is sooooo fast. 
"Large" tables in Access MDBs with many users shows a night and day difference in speed.  I can do a 
view, sorted on specific fields and load that view in a form.  It is editable and way fast in SQL 
Server.

What I haven't discovered yet is how to use dynamic filtering to subsets (all claimants with SSN 
beginning with 123 for example, or even the claimant with a given SSN) which are editable in Access. 
  I keep looking for that missing piece but to this point, no joy.  I can do this if I don't need to 
edit the records, for example return a set of records in a search form to select one of those 
records to open.  I have tried UDFs back in SQL Server and pass through queries in Access.  Just no 
joy so far.

:(

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 2/25/2012 10:00 PM, Dan Waters wrote:
> 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