F
FA
Hi, I have 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
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