Incorrect Host Name
-
We have a MS Access application that has been compiled into a .MDE application - The application comunicates with MS SQL Server tables via linked tables and an ODBC Data Source.
I have auditing switched on on the MS SQL Server and just carried out a check on the Audit results and noted that the users of the application all had the same Host Name - The Host Name was that of the PC that compiled the .MDE file.
The PC that compiled the .MDE file is currently switched off and yet I can see processing going on on the SQL Server via SQL Profiler which is reporting that the host name as that of the switched off PC.
Does anyone have any ideas?
-
In MS Access, after you select the DSN you want to use as your link, the SQL Server Login form pops up.
Click the Options>> button & then clear the Workstation ID field. Clearing this field will allow SQL Server to display the actual Workstation (host name) connected.
You may also want change the Application field (replace 'Microsoft Open Database Connectivity') with a more meaning Application name.
***********
Are you creating the DSN on each machine using the MDE file?
In case you weren't aware, if you create a File DSN (vs a machine or system DSN) & use that to create your linked tables in Access, it will embed the complete connection parameters for each linked table directly in the table.
The advantage with this method, is that you do not have to distribute or create a DSN on each machine that will be using your application. Don't know if that would be helpful in your situation - just that ought I'd pass it along. I use this method at work, makes life easier since I just copy the MDE file out to a network share & the users can work with it right away. I don't have to go through the hassle of creating the DSN first.
Kevin
-
Thanks a lot for the information it looks to be exactly what the problem is - I'll give it a try.
Is there anyway of changing the existing links without having to drop them and recreate them?
-
You can use the "Linked Table Manager" (menu option Tools | Database Utilities | Linked Table Manager) to change the DSN to point to another DSN (such as a file DSN) but this does NOT give you the option to change the Workstation ID.
So if you are trying to modify the Workstation ID field then answer is no - at least I haven't found any way using the GUI.
You could do this via some VBA code - modify the table connection property, however if you don't have any VBA code put togetherr right now that does that, then it is probably quicker to just drop & relink the tables.
Kevin
-
I thought I had just found a way by going into design on the linked table and then pulling up the properties and changing the Description of the ODBC connection but this didn't work.
I'll have a scan on the web for some scripting.
Thanks again for your replies.
-
Yes the table properties are read only when viewed that way. If you want to modify the connection string via code here is a sample you could use as a starting point.
*******************************
this uses a reference to DAO so within an Access VBA module set a reference to
Microsoft DAO 3.6 or 3.51 - depending on what version of Access your running.
both connection strings create DSN-less connections & they use Windows Authenication
so you will have to modify them to suit your requirements.
Easiest way is to run this sub with the
'.Connect = sConnectStr
'.RefreshLink 'update
lines remarked out. This will then just print the current connection strings to the
debug / command window - you can copy & modify as needed.
Public Sub TblConnect()
Dim tb As DAO.TableDef, db As DAO.Database
Dim sConnectStr As String
'This connection string does not inlcude the WSID parameter
'so SQL Server will pick up the host name
sConnectStr = "ODBC;DRIVER=SQL Server;SERVER=yourSQLSERVER;" _
& "APP=MYApp;DATABASE=yourdatabase;Trusted_Connection=Yes"
'This connection string sets the WSID paramter to hidden which is
'will be displayed in SQL Server as the host name
sConnectStr = "ODBC;DRIVER=SQL Server;SERVER=yourSQLSERVER;" _
& "APP=MYApp;WSID=hidden;DATABASE=yourdatabase;Trusted_Connection=Yes"
Set db = CurrentDb
For Each tb In db.TableDefs 'now look at each table in this mdb
With tb
Debug.Print .Name & ", " & .SourceTableName
If Len(.SourceTableName) Then 'if something there it is a linked table
'check to ensure the connect property is the same as the one we built
Debug.Print .Connect 'display the current connection string
If StrComp(.Connect, sConnectStr, vbTextCompare) <> 0 Then
'.Connect = sConnectStr 'it's not the same so set to the correct one
'.RefreshLink 'update
End If
End If
End With
Next
Set tb = Nothing
Set db = Nothing
End Sub
***********************
Kevin
-
Thanks a lot for the info - I'll give it a try.
I've already written some code and changed the WSID to nothing which resulted in no host name being passed to the SQL Server at all.
I'll see if excluding the WSID altogether resolves the issue.
Regards,
Nic.
-
IN my testing, yes you have to remove the WSID parameter all together.
Leaving it blank in the connection string ;WSID=; displays nothing in SQL Server.
Also, as you make changes to it the linked tables connection property in Access, you have to close & reopen Access to have the changes reflected in SQL Server.
Once you open a linked table in Access, it keeps that connection open until Access is closed.
Kevin
-
Great! - Thanks for the news.
Regards,
Nic.
-
If you already has some VBA code in your project with the following function you can get the currently logged Windows Username an Computer Name, so that you can put it into the ODBC connection string:
Attribute VB_Name = "modSysNetworkInfo"
'Option Compare Database
Option Explicit
Private Const MODULE_DATE = "1999/01/11"
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpbuffer As String, nSize As Long) As Long
Private Declare Function GetComputerName Lib "kernel32.dll" Alias "GetComputerNameA" (ByVal lpbuffer As String, nSize As Long) As Long
Function NetworkUserId() As String
' Returns UserId@StnId
Dim tBuffer As String
Dim lSize As Long
Dim tUtente As String
Dim bNonInRete As Boolean
'
bNonInRete = False
tBuffer = Left$("Local" + Space$(255), 255)
lSize = Len(tBuffer)
Call GetUserName(tBuffer, lSize)
If lSize > 0 And Trim$(tBuffer) <> "" Then
tUtente = Trim$(Left$(tBuffer, lSize - 1))
Else
bNonInRete = True
End If
'
tBuffer = Left$("Local" + Space$(255), 255)
lSize = Len(tBuffer)
Call GetComputerName(tBuffer, lSize)
If lSize > 0 And Trim$(tBuffer) <> "" Then
tUtente = tUtente + "@" + Trim$(Left$(tBuffer, lSize))
Else
bNonInRete = True
End If
If bNonInRete Then
tUtente = "x@y network not accessed"
End If
'
NetworkUserId = tUtente
End Function
HTH
-
I want to ask Kevin about his idea of File DSN ... does it support a lot of user connected to it (above 30 users) without crashing?
also is it faster than System DSN?
Alamir Mohamed
Alamir_mohamed@yahoo.com -
In theory, a File DSN is faster only because during the initial connection, the computer does not have to do a Registry look up to locate the connection parameters – everything is already embedded in the table connection.
With a System or Machine DSN, it needs to look in the Registry to get those parameters.
In practice, I doubt you would be able to notice any difference.
My main reason for using a File DSN is just the convenience – no need to create a DSN on each user’s computer. When you use a File DSN to link tables, it pulls all the necessary connection info direction into the table connection parameters at the time the table is first linked.
With regards to stability – there would be no difference.
In my experience, MS Access can start exhibiting instability with many concurrent connections – whether the tables are native Access tables or ODBC linked tables to SQL Server. There is no magic number of concurrent connections. I believe the hard limit is 255 but I doubt you’d be able to get an application running very long at that number with out it locking up or corrupting your Access file.
It was really designed for smaller, limited concurrent connection scenarios.
Five to ten concurrent connections should be okay. Above that it all depends – I’ve had some Access apps using 25-50 connections but can’t say I would recommend it.
You may want to investigate creating a Microsoft Access Project. This uses OLEDB to connect to SQL Server vs. ODBC. I heard some users get better results.
In the Access Help under the Contents tab, look for the section entitled “Working With Microsoft Access Projects”
Generally, if I need to look at this route, I usually just go with a VB or Web based application, but it might be worth checking out – especially if you have a lot of time already invested in you Access application.
Kevin
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply