Re: Changing Workstation ID in an adp
MGFoster
Hash: SHA1
Each workstation should have it's own .adp file. Why would one
workstation use the .adp file on another workstation?
I've found Workstation ID really doesn't matter. I usually use NT Logon
Authentication for any security matters. Using network admin tools,
network admins should be able to identify which users are hitting a
server.
Here is something I found to get the Workstation Name (not sure if it is
the same as wksta ID):
Public Declare Function GetComputerName Lib "Kernel32" Alias _
"GetComputerNameA" (ByVal strBuffer As String, _
lngBufferSize As Long) As Long
Public Function GetComputer() As String
' From Access-VB-SQL Advisor, Vol 8. No 2., p. 17.
Dim strComputerName As String
Dim lngSize As Long
Dim lngLength As Long
strComputerName = String(16, " ")
lngSize = Len(strComputerName)
lngLength = GetComputerName(strComputerName, lngSize)
GetComputer = Left(strComputerName, lngSize)
End Function
More info can be found in MS KnowledgeBase article Q148835.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQUIUY4echKqOuFEgEQJQmgCg+Vv+E5wSyeWUFI3TuVp2RFfR1kIAoMxP
j8K5uHffYaIJNKH4QuZEC6Vc
=43wa
-----END PGP SIGNATURE-----
Emad Sarraj wrote:
> The "Workstation ID" in the connection string on an Adp seems to get set when
> the Adp file is opened, but once it's there opening that adp from another
> workstation does not change that value, which causes connections to sql
> server to look like it's all coming from the same workstation.
>
> I've learned that the "baseconnectionstring" that contains the "workstation
> id" is read-only, so is there a way to populate the correct workstation id
> connecting to sql server?
Vadim Rapp
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
Paul Smith
solution been found, or the Access bug fixed? It is very discouraging that
such a glaring defect would be confirmed by a support rep and then the issue
completely dropped. Would someone from Microsoft please address the status
of this defect?
Emad Sarraj
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
You wrote on Thu, 16 Sep 2004 09:09:03 -0700:
PS> In the two years since the support request was opened by Microsoft, has
PS> a solution been found, or the Access bug fixed?
No. It's the same in Access 2003.
Vadim