Multi Database Access using ADP

  • Thread starter Thread starter Chris Newman
  • Start date Start date
C

Chris Newman

Hi

I have created an ADP project that is a "generic" application that sits on
each users computer. I have code in the ADP to change the connection string
so that the user can select different databases the ADP connects to. (the
different DB's are strucurally identical). The problem is if one of those
databases isnt available or the computer holding a particular database isnt
available, on running the ADP it says "<DB NAME> was not found on <server
name> etc..." While this is quite correct, I dont want the message to come
up!

The user may not actualyl want to connect to the DB that is offline, so will
then select from a dropdown (from startup form) a different database to
connect to.

The message occuring as soon as the ADP is run AND it timesout searching for
the database. I have tried saving "no connection string" into the ADP but
that didnt work - it still remebers!

Is there a workaround to prevent the message showing up?

Thanks

Chris Newman
 
You could make a small form as startup form and whenever your ADP app
starts, this form shows up and give a list of possible SQL Servers for user
to select. When user click "OK", you use
CurrentProject.OpenConnection()/CloseConnection() method to connect the ADP
app to the selected SQL Server. Of course you could do a bit more than
giving a list. You could code this form connects to a default Server when it
is loaded. If the default server is not available, you catch the timeout
error and either prompt user to select other server or automatically try to
connect next possible server... The key to this issue is to use
CurrentProject.OpenConnection() method.
 
I do use this - however, access attempts to connect as soon as the ADP is
opened in Access - even before any startup/autoexec stuff happens.
 
I open an About Form when the application starts which calls all of my
startup routines (menus, options etc). After three seconds its visible
property is set to false and it opens a custom login form. The user
then selects the connection they want. In the unload event of the
About Form I set the CurrentProject.OpenConnection "Provider=" (along
with cycling through and closing any open objects) which causes the
adp to become disconnected, so next time its started there is no
connection prompt.

This works well, except when the application terminates abnormally, in
which case the user is prompted the next time it is restarted (I
advise the user to press cancel and ignore the prompt).

It would be nice if there were an easier way of starting an adp in a
disconnected state.
 
Thanks for the tip Adam. I might give that a go and see if I can persude
Access to behave for 5 minutes!

Chris
 
Back
Top