Re: Changing Workstation ID in an adp

From: Vadim Rapp (vr_at_myrealbox.nospam.com)
Date: 09/11/04

  • Next message: Vadim Rapp: "Re: Possible to copy/paste from image on form ??" 
    Date: Sat, 11 Sep 2004 13:00:44 -0500

    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

  • Next message: Vadim Rapp: "Re: Possible to copy/paste from image on form ??"