One .adp, two or more different SQL Server

[kolorasta]

Hi. hope you can understand my english.

I have 2 SQL Servers.
Is there some way to set to which sql server connect the .adp (or .ade) via code??
Now I have 2 sql servers (development and production).. but soon I will have more server at production in different cities. All SQL Servers have different names. Same databases names but the servername are all different.
So I have to configure the connection of the .ade in the the development server. Then when I want to send and upgrade to one of my clients, I have to go to his city and manually reconfigure the .ade to connect to his SQL Server.
This problem would be solved if all SQL Servers had the same name, but this is not my case.

It would be nice to have a config.ini file or something like that where the .ade could read the name of the server to connect to.

Is it possible to implement something like this.

Thanks in advance.

 

[Moniker]

One simple way to accomplish that is to store the name of each server in a table and then have the ADP loop through each server until it finds a working connection. You'd do this with error trapping, something like this (pseudo-coded here)

Sub ConnectToServer()

    On Error Goto Handle_Error

    For x = 1 To NumberOfServers
        LinkThisDB.Relink = PathToServerX & DBName
    Next

Handle_Error:
    If Err.Number = The_Number_Telling_You_It_Cant_Find_The_Server Then
        Resume Next
    Else
        MsgBox Err.Number & " - " Err.Description
    End If

End Sub

To get The_Number_Telling_You_It_Cant_Find_The_Server value, attempt to connect to a server you know you cannot connect to, and note the error number it returns.