Dan Waters
df.waters at comcast.net
Sun Feb 26 09:05:20 CST 2012
Well - since the Library file is designed to be the same file for either Access or SQL tables, I can't use stored procedures. But, a while back I did a speed test to compare Access table links to ODBC table links. I set up loops doing just data transfers. It turned out that Access table links were 9% faster than ODBC table links for the identical data transfer. And, I got the same results at two companies. ODBC table links are not fast - OLEDB is (I think about 20X faster). For that 'missing piece' you might get a copies of these books (if you don't have them already!): http://www.amazon.com/Microsoft-Access-Developers-Guide-Server/dp/0672319446 /ref=sr_1_1?ie=UTF8&qid=1330268023&sr=8-1 http://www.amazon.com/Professional-Server-Development-Access-2000/dp/1861004 834/ref=sr_1_4?s=books&ie=UTF8&qid=1330268077&sr=1-4 http://www.amazon.com/Access-2002-Enterprise-Developers-Handbook/dp/07821401 06/ref=sr_1_2?s=books&ie=UTF8&qid=1330268164&sr=1-2 I read parts of these once when I thought I was going to get into ADO development. I was able to set up just a few procedures so you can call them with parameters to read, insert, or update data. Saves a lot of repetitive typing later on. Unfortunately I don't have those anymore. Good Luck! Dan -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Saturday, February 25, 2012 10:32 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Testing SQL Server linked FEs 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 > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com