Re: MS Access with Linked tables Security Issue

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Nikos, I have come up with some codes that are allowing me to
connect to SQL Server database with one Super ID that has all the super
permissions .
All of the six users have Windows (System) User IDs. lets say if the
IDs are
1) xyz123 2) abcd123 3) mnop124 4) plop4521 5) kmno1235 6)
tclm1478

How do i code in VBA so that xyz123 can have access to all the objects
in .mde file but abcd123 can have access to only table1 and table2 and
form1 and form2.
I am gona have to embed these System IDs into a module and call it in
my startup form. MS Access should compare each ID and grant the
appropriate permission.

Can you help me out with the VBA Code please ??

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables
Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String
If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN &
";UID=Admin;PWD=test123!;DATABASE=" & strDatabase & ";"
End If
SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."
Call DeleteODBCTableNames
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)
Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename],
dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing
Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub
Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " &
Err.Source _
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables
End Sub

Thanks alot

.

Relevant Pages

  • Re: Login failed for user sa. (Error code: 18456).
    ... What transport mechanism is your default for SQL? ... > SQL Server database name: ... I tried windows authentication to access sql server database. ... I got different error message as "Login failed for user ...
    (microsoft.public.sharepoint.portalserver)
  • Re: How to error-proof Doug Steels "FixConnections"?
    ... rather than letting them type in the server and database name. ... you could also store the information in a table in the front-end. ... SQL Server database from the backup, how to get rid of the annoying ...
    (microsoft.public.access.externaldata)
  • Clone SQL DB (using VB?)
    ... I'm having a SQL Server database called X. ... So I need to update the script ... When I snap my fingers, ...
    (microsoft.public.sqlserver.msde)
  • Re: Mail Merge
    ... You can find my "super easy word" merge here: ... Often if that query has any functions, ... database, then again problems arise. ...
    (comp.databases.ms-access)
  • Re: class super method
    ... confusing wxPython classes to have a consistent interface is where we also make the most use of super(), but our database wrappers also provide consistent functionality to all the dbapi cursors, no matter what the backend database may be. ...
    (comp.lang.python)