Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    12

    Unanswered: SQL Server as backend, error w/ transaction logs

    I have several front-end Access 97 applications which write data to a SQL Server 2000 database. In viewing the transaction log created in SQL Server, I noticed that the users and their associated host names are incorrect for any transactions written from Access. For example, user1 is on the machine host1, but the transaction log reports the user initiating the query from host2. I assume this is a known bug from within Access or the ODBC connection. Does anyone know of any information about this strange occurrence?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by lisa.shores
    I have several front-end Access 97 applications which write data to a SQL Server 2000 database. In viewing the transaction log created in SQL Server, I noticed that the users and their associated host names are incorrect for any transactions written from Access. For example, user1 is on the machine host1, but the transaction log reports the user initiating the query from host2. I assume this is a known bug from within Access or the ODBC connection. Does anyone know of any information about this strange occurrence?
    hi Lisa

    You using linked tables by any chance?

    EDIT - and\ or DSNs?
    Last edited by pootle flump; 10-17-05 at 12:26.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2003
    Posts
    12
    Yes, we are.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by lisa.shores
    Yes, we are.
    Ok - open a linked table in design view and click on properties. You will see the connection string used in the Description field. You'll note that the WSID parameter is included. Access fills this in when you link a table. Also, if you create a file DSN it writes it into there too (open a dsn in notepad to see).

    Obviously, this means the WSID travels with the app - the ID of the WS that created the link is passed to SQL Server. You can't delete it and if you remove it from the DSN it writes it into the table again. The only way I have been able to get rid is with the following code. If there is no WSID parameter hard written to the table description field then Access will provide the true value. Run this once and each time you link a table

    Code:
    Sub TDefConn()
    
    	Dim TDef As dao.TableDef
    	Dim db As dao.Database
    	
    	Set db = CurrentDb
    
    	For Each TDef In db.TableDefs
    
    		If Nz(TDef.Connect, "") <> "" Then
    
    			TDef.Connect = Replace(TDef.Connect, ";WSID=TheWSID", "")
    		
    			TDef.RefreshLink
    		
    		End If
    
    	Next TDef
    	
    	Set TDef = Nothing
    	db.Close
    	Set db = Nothing
    	
    End Sub
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - you can check the affect of this by running sp_who or sp_who2 or just querying sysprocesses before and after the change

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •