Changing Server

  • Thread starter Thread starter riyaz.mansoor
  • Start date Start date
R

riyaz.mansoor

Hi

I'm just wrapping back an ADP project, but am faced with dilemma.
Here's the situation.

* Have a test server: ServerA
* ADP compiled to ADE to use ServerA
* Test server ServerA changed to ServerB ===> CANNOT connect.

What are my options besides recompiling my ADP and redistributing it
to the users.

Does ADP have a mechanism by which I can change the Connection
properties?

The domain solution I have come up (untested) is to use DNS alias for
the ServerA, to which ADE will connect. As the machine changes, point
the alias to the new machine. Is this workable?

This of course brings me to the next big question? What to do with a
WorkGroup?

Thanx
Riyaz
 
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)
 
just remove the workstation string from your connection string

it is an optional parameter; and yes-- if you overwrite it-- it will stay
overridden
Click to expand...

No. It does NOT get overwritten. Just in case this was a thing with
Access, I have checked the Host_Name on SQL Server and it is the old
name. Reading the posts, it seems that this is NOT possible.

Is this so?

Riyaz
 
just remove the workstation string from your connection string

it is an optional parameter; and yes-- if you overwrite it-- it will stay
overridden
 
I can't help you with your main problem, but for the problem of the
Workstation ID not changing, this is a known limitation/bug in ADP projects.
Vadim Rapp came up with a workaround for this problem that seems to work
fairly well. If he doesn't respond to this post in a few days, post back to
this thread and I'll dig up his public e-mail address and send it to you
privately.


Rob
 
you're sticking a workstation name in the connection string

and then you're bitching that it's not showing the correct workstation name?

pull out the workstation name from your connection string!
 
Robert;

what the hell are you talking about, he just needs to pull the workstation
clause from his connection string

I mean seriously guys do you guys not understand how SQL Server works?
 
I can't help you with your main problem, but for the problem of the
Workstation ID not changing, this is a known limitation/bug in ADP projects.
Vadim Rapp came up with a workaround for this problem that seems to work
fairly well. If he doesn't respond to this post in a few days, post back to
this thread and I'll dig up his public e-mail address and send it to you
privately.
Click to expand...

Thanks. I've figured it out. Workstation ID cannot be set - Access
resets it to the current machine_name. From postings on this group.

As for my other problem - CurrentProject.OpenConnection and
disconnected ADPs. A search on these terms in this news group yields
the answers.

thanks all.
 
Thanks. I've figured it out. Workstation ID cannot be set - Access
resets it to the current machine_name. From postings on this group.
Click to expand...

As I said, there *is* a solution available for that, if you really want it.
But by default, it always shows the same machine name. Alternatively, I
believe disconnecting and reconnecting in code will also work.


Rob
 
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
 
Back
Top