I would like to add that i'm using Access 2003 SP2 to SQL Server 2000

   Dim strConnect As String
   strConnect = CurrentProject.BaseConnectionString
   Debug.Print strConnect
   strConnect = "PROVIDER=SQLOLEDB.1;Workstation
ID=1;PASSWORD=pass;PERSIST SECURITY INFO=TRUE;USER ID=GrMarine;INITIAL
CATALOG=GrandMarineOperations;DATA SOURCE=(local);Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096"
'    strConnect = Replace(strConnect, "Source=TestServer",
"Source=ProductionServer")
'    CurrentProject.CloseConnection  ' tried with and without
   CurrentProject.OpenConnection (strConnect)
   Debug.Print CurrentProject.BaseConnectionString

the result is: the output is somehow truncated and does not reflec the
changes (Workstation ID) that i've made.

PROVIDER=SQLOLEDB.1;PASSWORD=Gr2Marine6;PERSIST SECURITY
INFO=TRUE;USER ID=GrMarine;INITIAL CATALOG=GrandMarineOperations;DATA
SOURCE=(local);Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=RIYAZMANSOOR
PROVIDER=SQLOLEDB.1;PASSWORD=Gr2Marine6;PERSIST SECURITY
INFO=TRUE;USER ID=GrMarine;INITIAL CATALOG=GrandMarineOperations;DATA
SOURCE=(local)

On May 24, 10:47 am, "riyaz.mans...@gmail.com"
<riyaz.mans...@gmail.com> wrote:

toi khong hieu gi het

Hi Riyaz,

If I understand your posting correctly you would like to change Servers
within your ADP from your Test to Production environment. Not sure whether
you require users to logon or want to hardcode these values, but I manage
this using a logon form that prompts the user for the:

Server
Username
Password

The server choices are shown in a combobox which is populated in the
Form_Open event by the following code:

============================
Private Sub Form_Open(Cancel As Integer)
Dim intI As Integer
Dim dmoApp As SQLDMO.Application
Dim dmoName As SQLDMO.NameList
On Error GoTo Form_Open_Error

 Set dmoApp = New SQLDMO.Application
 With dmoApp
   ' Get list of available SQL Server instances
   Set dmoName = .ListAvailableSQLServers
   With dmoName
     For intI = 1 To .Count
       cboServer.AddItem (.Item(intI))
     Next
   End With
 End With
 Set dmoName = Nothing
 Set dmoApp = Nothing
 
Form_Open_Exit:
 Exit Sub

Form_Open_Error:
 MsgBox str(Err.Number) & " - " & Err.Description, vbCritical + vbOKOnly,
"Unexpected Error - Form_Open"
 Cancel = True
 Resume Form_Open_Exit
End Sub
===========================

Then it is just a simple matter of setting the connect string appropriately.

Hope this helps
Guy

Riyaz,

Sorry forgot to mention you need to set a reference to the Microsoft SQLDMO
library for this to work, but you will probably need this anyway if you want
to users to backup/restore/attach/detach/run SQL Maintenance procedures
against your database installed on the server.

Cheers
Guy