jwcolby
jwcolby at colbyconsulting.com
Mon Feb 27 09:26:59 CST 2012
Dan, OLEDB is supposed to be faster, however I have issues using that driver with links to the SQL Server at an IP address. When I do a database that connects over the internet I use Hamachi. The table is then linked to 5.xxx.yyy.xxx,1433. Using the SQL Server driver things work as expected. Using the OLEDB native driver the links fail. If that bug is ever fixed (or figured out on my end) then I will go back to using them. It would no doubt make an enormous difference in my speed but in the meantime I am forced to use "SQL Server" as the driver property. John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it On 2/26/2012 10:05 AM, Dan Waters wrote: > 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 >