I'm having a problem where a simple SQL view (held on SQL Server 2005 Express) linked via ODBC to an access front end (2007) was using SUSER_SNAME() to identify who was running the query in Access using the UID of the ODBC connection string, but for some reason it was returning another user who had run the query several hours previously. When I took out "DATABASE=" from the connection string it worked once, but not since then.
The SQL server is on a windows workgroup and 3rd party Active Directory is run by Novell, so I can't use windows authentication, I have to use SQL server authentication. When the user opens the front end access database, a vba script re-links the OBDC tables using their windows logon as the UID (which matches the user created in SQL server). I then tried to get the SQL view to filter the data on the query using the UID from the connection string so the user only had access to the records they were allowed to view.
Does the SQL server ODBC connection not close? Why isn't it returning the correct username using the uid from the DNS-less connection string? Can anyone see where I'm going wrong? I would have thought it was fairly common to try and filter SQL server views using the username from a DNS-less connection? Why does the ODBC link update sometimes, but not other times?
--------------------------------------------------------------------------------------------------------------------------
On Error GoTo AttachDSNLessTable_Err
Dim db As DAO.Database
Dim tabdef As TableDef
Dim stConnect As String
Dim GenericUser As String
Dim Result As String
Dim rst As Recordset
Dim StSQL As String
Dim TableExists As Boolean
Set db = CurrentDb
stServer = "SERVER\SQLEXPRESS"
stDatabase = "Database"
GenericUser = "GenericUser"
stPassword = "*****"
Set rst = db.OpenRecordset("Tbl_ODBCPurge", dbOpenDynaset)
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
stLocalTableName = rst![PurgeTblName]
stRemoteTableName = "dbo." & stLocalTableName
On Error Resume Next
Set tabdef = CurrentDb.TableDefs(stLocalTableName)
If Err.Number = 0 Then
TableExists = True
Else
TableName = False
End If
On Error GoTo AttachDSNLessTable_Err
If TableExists = True Then
db.TableDefs.Delete (stLocalTableName)
Else
End If
If DLookup("PurgeType", "Tbl_ODBCPurge", "PurgeTblName = '" & stLocalTableName & "'") = "Generic" Then
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";UID=" & GenericUser & ";PWD=" & stPassword
ElseIf IsNull(DLookup("UserID", "Qry_Users", "UserID = '" & stUsername & "'")) Then
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";UID=" & GenericUser & ";PWD=" & stPassword
Else
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set tabdef = New TableDef
tabdef.Name = stLocalTableName
tabdef.Connect = stConnect
tabdef.Attributes = dbAttachSavePWD
tabdef.SourceTableName = stRemoteTableName
db.TableDefs.Append tabdef
db.TableDefs(stLocalTableName).RefreshLink
rst.MoveNext
Loop
Else
End If
AttachDSNLessTable = True
Set tabdef = Nothing
Set db = Nothing
Exit Function
AttachDSNLessTable_Err:
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function
The SQL server is on a windows workgroup and 3rd party Active Directory is run by Novell, so I can't use windows authentication, I have to use SQL server authentication. When the user opens the front end access database, a vba script re-links the OBDC tables using their windows logon as the UID (which matches the user created in SQL server). I then tried to get the SQL view to filter the data on the query using the UID from the connection string so the user only had access to the records they were allowed to view.
Does the SQL server ODBC connection not close? Why isn't it returning the correct username using the uid from the DNS-less connection string? Can anyone see where I'm going wrong? I would have thought it was fairly common to try and filter SQL server views using the username from a DNS-less connection? Why does the ODBC link update sometimes, but not other times?
--------------------------------------------------------------------------------------------------------------------------
On Error GoTo AttachDSNLessTable_Err
Dim db As DAO.Database
Dim tabdef As TableDef
Dim stConnect As String
Dim GenericUser As String
Dim Result As String
Dim rst As Recordset
Dim StSQL As String
Dim TableExists As Boolean
Set db = CurrentDb
stServer = "SERVER\SQLEXPRESS"
stDatabase = "Database"
GenericUser = "GenericUser"
stPassword = "*****"
Set rst = db.OpenRecordset("Tbl_ODBCPurge", dbOpenDynaset)
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
stLocalTableName = rst![PurgeTblName]
stRemoteTableName = "dbo." & stLocalTableName
On Error Resume Next
Set tabdef = CurrentDb.TableDefs(stLocalTableName)
If Err.Number = 0 Then
TableExists = True
Else
TableName = False
End If
On Error GoTo AttachDSNLessTable_Err
If TableExists = True Then
db.TableDefs.Delete (stLocalTableName)
Else
End If
If DLookup("PurgeType", "Tbl_ODBCPurge", "PurgeTblName = '" & stLocalTableName & "'") = "Generic" Then
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";UID=" & GenericUser & ";PWD=" & stPassword
ElseIf IsNull(DLookup("UserID", "Qry_Users", "UserID = '" & stUsername & "'")) Then
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";UID=" & GenericUser & ";PWD=" & stPassword
Else
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set tabdef = New TableDef
tabdef.Name = stLocalTableName
tabdef.Connect = stConnect
tabdef.Attributes = dbAttachSavePWD
tabdef.SourceTableName = stRemoteTableName
db.TableDefs.Append tabdef
db.TableDefs(stLocalTableName).RefreshLink
rst.MoveNext
Loop
Else
End If
AttachDSNLessTable = True
Set tabdef = Nothing
Set db = Nothing
Exit Function
AttachDSNLessTable_Err:
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function
