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 >