How to error-proof Doug Steel's "FixConnections"?

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi all,

I have an Access application that serves as a fron end for a SQL Server
database. Since our customers use different names for their SQL Server
servers (and possibly choose a different name for the database as well),
I had to come up with a way to allow the customers to change the
connection strings for all linked table with minimal effort.

After asking around, I was pointed to the FixConnection routine as
written by MVP Doug Steele
(http://www.accessmvp.com/djsteele/DSNLessLinks.html). I made a simple
form where the users can enter a server- and databasename and that then
calls FixConnections to change all connection strings to point that
server and database. Worked like a charm, and life was good.

But just now, I got an email from a customer: the front-end refused to
open some of the forms because the linked tables were gone. After some
digging, I found the result - apparently, there had been some attempts
to change connection strings towards a misspelled server or database
name. The on error routine in Doug's code will catch this and abort the
process - but only after deleting the first linked table! So each time a
user errs while specifying server or database, one of the linked tables
disappears. (The copy I got from this particular customer had less than
half of its linked tables left, which is probably saying something about
how they work <g>)

Since I can't prevent with 100% reliability that users err while typing
a server or table name, I need some other way to prevent the access
application losing its linked tables. One option would be to somehow
"undo" the removal of the original table if the creation of the new
version with the updated conenction fails. Another option would be to
test the validity of the server and database specified before calling
the FixConnections script. But unfortunately, either of these options
seems to be beyond my VBA coding capabilities.

So, has anyone here encoutered a similar problem? How did you solve it?
Any ideas and suggestions are most welcome!

(BTW - I'm using Access 2003, in case it matters)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.

Relevant Pages

  • Re: Remobjects v KBM
    ... >> client query components) follow from that. ... Then, connections can be created to say SQL Server, Oracle, Interbase and ... can then be created from the abstract dataset definition in 'customers' to ... implicitly - this makes your code not be database connection specific). ...
    (borland.public.delphi.thirdpartytools.general)
  • RE: WSS install locked into MSDE DB - Can not install for SQL Serv
    ... For the MASTER database ... Althought we change the Configuration Application pool several times ... interface BUT they appear to be MSDE databases and not SQL Server databases. ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: How to Replicate an SQL Server 2000 Database
    ... actual server name) enterprise manager should associate the database with the ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Remobjects v KBM
    ... > can then be created from the abstract dataset definition in 'customers' to ... One on the client side and one on the server side. ... This is possible to setup on each connection pool, to ensure cross database handling easily via the cross database ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files
    ... I have had quite a number of clients that used a SAN for a database server and suffered significant performance problems because their underlying SAN infrastructure was a huge stripe across many drives. ... By application files I mean the binn folder which contains the sql server executable among other things. ...
    (microsoft.public.sqlserver.setup)