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

  • Thread starter Thread starter Hugo Kornelis
  • Start date Start date
H

Hugo Kornelis

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)
 
I haven't tested this idea, but what if, rather than deleting the existing
linked tables, you renamed them? Rename them by appending some suffix that
isn't used for any other purpose in your database, say "_TBD" (standing for
"To Be Deleted"). If an error occurs, in the error handler, change the name
back by removing the prefix. If no error occurs, at the end of the
procedure, delete the tables with the "_TBD" suffix.
 
In the section where you're adding the details of the existing linked
tables, you could add the details of the existing connection, and then
simply recreate it if an error arises.

What I do to ensure that errors like this don't ever occur is limit what the
users can select as input to the function. For instance, I'll give them a
choice of between their Development, Acceptance and Production versions,
rather than letting them type in the server and database name. Typically,
I'll do this by having an INI file that includes the valid parameters, but
you could also store the information in a table in the front-end.
Realistically, in a situation like this, it doesn't make sense to let the
users type in the value (nor even to choose from a list of valid servers and
valid databases on the selected server: the application is only going to
work with specific databases!)
 
In the section where you're adding the details of the existing linked
tables, you could add the details of the existing connection, and then
simply recreate it if an error arises.
Click to expand...

Hi Doug,

That did the trick. Easy, once you have the idea. :) Thanks!!
What I do to ensure that errors like this don't ever occur is limit what the
users can select as input to the function. For instance, I'll give them a
choice of between their Development, Acceptance and Production versions,
rather than letting them type in the server and database name. Typically,
I'll do this by having an INI file that includes the valid parameters, but
you could also store the information in a table in the front-end.
Realistically, in a situation like this, it doesn't make sense to let the
users type in the value (nor even to choose from a list of valid servers and
valid databases on the selected server: the application is only going to
work with specific databases!)
Click to expand...

I'd love to do something similar, but I'm afraid I can't. The way it
works is that I send a backup of a SQL Server database and an Access mdf
file to my customer, together with instructions on how to create a new
SQL Server database from the backup, how to get rid of the annoying
warning when opening the Access file by signing it with his own
certificate, and instructions on how to change the connection strings
for the linked table (i.e., how to use that form I made that calls
FixConnections in the background).

I might be able to exercise some control over the database name they
choose when "restoring" the backup I send them (though I'd rather give
them the ability to choose their own, in case they already have a DB
with that same name). But there is absolutely no way for me to have any
control over the server (and possibly instance) name they have their SQL
Server installed on. (And in case you want to suggest using a dropdown,
that would indeed prevent typos but it would saddle me with the burden
of keeping seperate copies of the .mdb for each customer, and of having
to change the list of values for the dropdown every time a customer
makes a change to their domain topology).

As an afterthough, you might want to add the error handling you
suggested to your own copies of the code as well. Though a limited
choice doesn't allow for typos, there's still the possibility of the
code being run by a user with unsifficient authorisation in a database,
or at exactly the moment a DBA decides to set the database to single
user mode for some emergency maintenance. In those cases, you too would
lose a linked table!

(Just holler if you want me to post my code - though I doubt it'll meet
your bar for code quality. Hey, what can I say, I'm just a simpleton SQL
Server developer.. <g>)

Once more, thanks for the solution, and MANY thanks for the great code!
 
I haven't tested this idea, but what if, rather than deleting the existing
linked tables, you renamed them? Rename them by appending some suffix that
isn't used for any other purpose in your database, say "_TBD" (standing for
"To Be Deleted"). If an error occurs, in the error handler, change the name
back by removing the prefix. If no error occurs, at the end of the
procedure, delete the tables with the "_TBD" suffix.
Click to expand...

Hi Brendan,

Thanks for the idea. I didn't try it myself either, as I decided to try
Doug's suggestion first - and when I found it worked, I didn't feel like
investigating the alternatives. :)
 
Hugo Kornelis said:
I'd love to do something similar, but I'm afraid I can't. The way it
works is that I send a backup of a SQL Server database and an Access mdf
file to my customer, together with instructions on how to create a new
SQL Server database from the backup, how to get rid of the annoying
warning when opening the Access file by signing it with his own
certificate, and instructions on how to change the connection strings
for the linked table (i.e., how to use that form I made that calls
FixConnections in the background).
Click to expand...

You may have missed my point. Someone (even if it isn't you) will know which
database(s) they've created on which server(s). Since it only makes sense to
connect to those databases, store have them store that information
somewhere.
I might be able to exercise some control over the database name they
choose when "restoring" the backup I send them (though I'd rather give
them the ability to choose their own, in case they already have a DB
with that same name). But there is absolutely no way for me to have any
control over the server (and possibly instance) name they have their SQL
Server installed on. (And in case you want to suggest using a dropdown,
that would indeed prevent typos but it would saddle me with the burden
of keeping seperate copies of the .mdb for each customer, and of having
to change the list of values for the dropdown every time a customer
makes a change to their domain topology).
Click to expand...

I don't see how having a separate INI file is a burden, or, as suggested,
they can store the information in a table.
As an afterthough, you might want to add the error handling you
suggested to your own copies of the code as well. Though a limited
choice doesn't allow for typos, there's still the possibility of the
code being run by a user with unsifficient authorisation in a database,
or at exactly the moment a DBA decides to set the database to single
user mode for some emergency maintenance. In those cases, you too would
lose a linked table!

(Just holler if you want me to post my code - though I doubt it'll meet
your bar for code quality. Hey, what can I say, I'm just a simpleton SQL
Server developer.. <g>)
Click to expand...

Sure. Send it along. (djsteele at canada dot com) May as well help others.
<g>
 
Back
Top