Change Server Name in ADP Connection Property

[Jim Molter]

I have an adp that points to a SQL Server on another
network. I also have a copy of the Database on a local
SQL Server.

The adp errors out on open because it can't find the named
server. Since it won't open, I can't change the named
server. Nor can I change the name of the SQL Server to
coincide with the server named in the adp.

The only way I have gotten around this in the past is to
create a new project pointed at the local server, and move
all the objects into it.

I have to ferry this application back and forth. Isn't
there a better way?

 

[prabha]

Hi Jim,

Why check server status before connecting. Example,

Dim osvr As SQLDMO.SQLServer

'Create the SQLDMO Server Object.
Set osvr = CreateObject("SQLDMO.SQLServer")
osvr.Name = "Name of your Server"

On Error GoTo StartError
IF srvr.status = 1 THEN
' server is running
' sample code to connect to server
' exit function
END IF
Exit Function
StartError:
msgbox "Server not available (or code to point to different
server)"

For details see the following knowledge base article:
MOD2000: How to Deploy an Access Data Project That Includes the Microsoft
Data Engine
http://support.microsoft.com/default.aspx?scid=kb;en-us;240293

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."



--------------------
| Content-Class: urn:content-classes:message
| From: "Jim Molter" <[email protected]>
| Sender: "Jim Molter" <[email protected]>
| Subject: Change Server Name in ADP Connection Property
| Date: Sat, 31 Jan 2004 07:48:47 -0800
| Lines: 15
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcPoEbaLLWHydFNsT3+TAXFPQrdPpA==
| Newsgroups: microsoft.public.access.adp.sqlserver
| Path: cpmsftngxa07.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.adp.sqlserver:16220
| NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver
|
| I have an adp that points to a SQL Server on another
| network. I also have a copy of the Database on a local
| SQL Server.
|
| The adp errors out on open because it can't find the named
| server. Since it won't open, I can't change the named
| server. Nor can I change the name of the SQL Server to
| coincide with the server named in the adp.
|
| The only way I have gotten around this in the past is to
| create a new project pointed at the local server, and move
| all the objects into it.
|
| I have to ferry this application back and forth. Isn't
| there a better way?
|

 

[Dan]

You have some options. One option is to clear the
connection each time you close the app, and reconnect it
when it opens. You could read the server name from a
text file or the registry or prompt the user for the
server name. This would require some VB code, but there
are plenty of examples of how to do it.

Another option is to trap the connection error and prompt
the user for the servername if the initial connection
fails.

- Dan