Re: Changing Workstation ID in an adp

From: Emad Sarraj (esarraj_at_nospam.nospam)
Date: 09/16/04 

Date: Thu, 16 Sep 2004 09:39:04 -0700

Thank you Vandim, your post has been helpful.

My problem now is getting Microsoft support to respond to this question.
This is the second time that I post a question using my MSDN Universal
registered account and nobody from microsoft responds.

"Vadim Rapp" wrote:

> Hello Emad:
> You wrote in conference microsoft.public.access.adp.sqlserver on Fri, 10
> Sep 2004 09:05:03 -0700:
>
> ES> The "Workstation ID" in the connection string on an Adp seems to get
> ES> set when the Adp file is opened, but once it's there opening that adp
> ES> from another workstation does not change that value, which causes
> ES> connections to sql server to look like it's all coming from the same
> ES> workstation.
>
> ES> I've learned that the "baseconnectionstring" that contains the
> ES> "workstation id" is read-only, so is there a way to populate the
> ES> correct workstation id connecting to sql server?
>
> ES> I've looked on the web and newsgroups and could find this same question
> ES> a number of times but with no answer.
>
> Here's the transcript of the Microsoft support incident, which I opened back
> in 2002.
>
>
> ==============
>
> Problem Description: I created an Access ADP project on my machine named
> VADIM, compiled it into ADE, and deployed on user's machine.
>
> The user runs the ADE on his machine named USER1, connecting to SQL Server
> 2000 with NT authentication. In the Enterprise Manager/Management/Current
> Activity, I see user's mahine name (column "Host") shows up not as USER1 but
> as VADIM.
>
> In real life, there are many users running the ADE, and they all show up as
> VADIM machine. Apparently, this is incorrect. For example, Enterprise
> Manager among other things allows to send a message to a user (who, for
> example, is blocking a table) using machine name, and that is sending it not
> to machine USER1 but to VADIM (to me)
>
> In my ADP, I open Connection, go to its Advanced tab, and indeed there I see
> VADIM is hardcoded (the last item in the list). Apparently, this is
> hardcoded into the ADE and results in showing up as machine name when
> connected to SQL Server.
>
> If I reset that value with the button "reset" and then hit OK, it gets
> restored.
>
> Is there a way to have users running ADE to show up in the Enterprise
> Manager (or any other monitoring tool) with their respective machine name,
> instead of the developer's machine name?
>
> ==========
>
> Thank you for choosing Online Support for your Microsoft Technical Support
> offering. My name is Jun Chen and I will be assisting you with this Service
> Request.
>
> In your case you've indicated you created an Access ADP project on your
> machine named VADIM, compiled it into ADE, and deployed on user¡¯s machine.
> The user runs the ADE on the computer named USER1. In SQL Server Enterprise
> Manager, you found out the connection shows VADIM as the host name. In the
> ADP, you open Connection, go to All tab, and you see VADIM as the
> workstation ID.
>
> We shall be working to resolve this specific issue through the course of the
> case. If I have misunderstood your concern please let me know.
>
> It looks like we may need to reset the connection string so as to work
> around this problem. For example, the following code can reset the
> connection so that it will use the default Workstation ID and Application Name:
>
> Function Startup()
>
> CurrentProject.CloseConnection
> CurrentProject.OpenConnection "PROVIDER=SQLOLEDB.1;INTEGRATED
> SECURITY=SSPI;INITIAL CATALOG=NorthwindCS;DATA
> SOURCE=SQLSERVERNAME;Application Name=what;Workstation ID=testw"
>
> End Function
>
> You may run the code in a startup form so that it will reset the connection
> as soon as you load up the database.
>
> I hope this helps. If you have any further concerns, please let me know.
> It¡¯s my pleasure to be of assistance.
>
>
> =================
>
>
> Problem Description:
>
> It looks like it does not work. Here's the steps I just tried:
>
> 1. created a new project adp1.adp
> 2. created form1
> 3. put the following code:
>
>
> Private Sub Form_Open(Cancel As Integer)
> CurrentProject.CloseConnection
> CurrentProject.OpenConnection "PROVIDER=SQLOLEDB.1;INTEGRATED
> SECURITY=SSPI;INITIAL CATALOG=RGRSQL;DATA SOURCE=PSSQL;Application
> Name=what;Workstation ID=testw"
> MsgBox CurrentProject.Connection.ConnectionString
> End Sub
>
> 4. compiled into ade
>
> 5. opened ADE.
>
> Msgbox showed the same connection string as before, i.e.
> provider=microsoft.access.oledb.10.0; etc.
>
> no workstation id; no application name. In Enterprise Manager, application
> name=Microsoft Office XP; Workstation id=<my development machine name> - and
> when I start it from another machine as well.
>
>
> =============
>
>
>
> The code that I provided does not change the Application Name to ¡®what¡¯
> and change Workstation ID to ¡®testw¡¯. It just resets these values. If you
> check the connection properties through File->Connection menu after you run
> the code, you should see that the Application Name is empty and the
> Workstation ID is the name of the computer.
>
> I have tested the behavior on my side. In Enterprise Manager, Application
> Name = Microsoft Office XP; Workstation ID=<the computer name>. If I open
> the Access Project and run the code on another computer, the Workstation ID
> changes to that computer name.
>
> Please let me know if this works for you.
>
>
> ==========
>
> Problem Description: Yes! it works. Thanks, we can close the incident.
>
> The only remaining question is: in fact, I would prefer to set a specific
> name for the application name, and have the real machine name for the
> workstation id. Your solution resets both. Do you know a way to have the
> real machine name, but the application name not "office 10" but the one I
> want?
>
> thanks,
>
> Vadim
>
> ===========
>
>
> Thank you for the update, and sorry for the delayed response.
>
> The solution that I provided resets both the application name and the
> workstation ID. However, I am not able to set a specific name for the
> ¡°Application name¡± currently using the connection string. I am still
> trying to figure out how to set the ¡°Application Name¡± extended property.
> I will let you know the information as soon as I have any update.
>
> Thank you for your understanding.
>
>
>
> ========================
>
> Vadim
> ----------------------------------------
> Vadim Rapp Consulting
> SQL, Access, VB Solutions
> 847-685-9073
> www.vadimrapp.com
>
>