dynamic switchboard ideas/opinions/help... 1

[ucinv]

What I want to do is make a switchboard that will display certain items on a per user basis, without hardcoding the users names.
Info:
I am running SQL Server 7 with Access 2000, using ADO for programming. This need started because we had 2 databases in Access 97. Eventually they got migrated to Access 2000 and then combined into 1 database and then migrated the data into SQL Server 7. The 2 switchboards are very different item-wise. My goal is to combine the 2 switchboards into 1. Then depending on the user login, enable/disable items. Currently, I change the switchboard form recordsource to tblSwitchboard1 or tblSwitchboard2 depending on the user's role. Is there a better way?

I've thought about assigning switchboard items numbers like 1,2,4,8,16,32,64,128,256 then creating roles with numbers such as '6' (2+4=6 so a user with role 6 has access to switchboard items 2 and 4 only. I'm not so sure how I would have it do the calculation needed for this though.)

 

[LittleSmudge]

You could do it that way ucinv. But here is another way that might be worth investigating begore you commit too far.

Create a Many-to-Many relationship between the Users table and the Switchboard items table via a lining table ( lets call it tblSULink)

For any given User you can then create an ADODB Recordset that just contains switchboard items you need.

Something like
rst.Open "SELECT * FROM tblSwitchboard INNER JOIN tblSULink ON tblSwb.SwbId = tblSULink.SwbRef WHERE tblSULInk.UserRef = " & LogOnId

WHILE NOT rst.EOF

etc .. ..

rst.MoveNext
Wend


You can then add as many Switchboard items as you need.



G LS

 

[ucinv]

Thanks for your help LittleSmudge, it sounds like a better idea, and easier to implement (less code). I'm new to SQL Server 7 and am unsure about the Users table you refer to. Is there a Users table in SQL? I have not manually created one myself because I want to manage users at one place only, at the SQL Server security section (hence no hard-coded usernames).

 

[LittleSmudge]

No ucinv , by UserTable I meant the table that you have created that holds the information about your users ( The place where you have User and ROLE already )

tblUsers has fields such as
UserId PrimKey
Role Text

tblSwb has fields such as
SwbId PrimKey
SwitchboardReference - in whaever way makes sence

tblSULink has 3 fields
LinkId
UserRef ForeignKey to tblUser
SwbRef ForiegnKey to tblSwb

Then populate tblSULink with the values of the Keys from the other two tables.


G LS

 

[ucinv]

I have found that SQL stores the user info in the Master database in the sysxlogins table. I should be able to make this work. Thanks for your help.