change my network database to local for each user... help!
-
hi guys!! i have an access 2003 database in my network drive which all my users use.... this database has a sql server backend.. most of my queries and reports are still in access , little by little i am migrating them into sql server as well.
this is my question, after doing some research, i read that the best thing is to make a copy of the network access database and then put it into each users local drive. is that correct? thwi will work for me since i only want each department to see the reports that pertain to them.. my boss also told me that now according to departments wants a switchboard so then each department can only open certain reports and forms... can anyone tell me the best way to go abou this?????
Can i create a main switchboard and then for that switchboad to open other ones. for example , have a management swithboard and then for this one to have the rights to open sales, marketing , etc.... my question is becuase then if i create a new report , do i have to go to everyones computers and then copy it?
help!!
-
Relax
Have a look at http://www.granite.ab.ca/access/autofe.htm for possible ways of automatically updating your access-versions.
At work we have a table with program versions. At startup the Access application verifies its current version with the version in the backend (sql server). If it is newer, it starts a batch file and closes itself. The batch file has a check if the .ldf file is present. When it is no longer present it copies the new version from across the network and restarts the application.
-
Thank you Jo... perfect thanks!!! so i could have the same copies of the database front end, but then how do the different switchboards would work? each department has to have a different switchboard ....
-
I have no experience with switchboard but they look like forms with button. When clicking on a button it opens a new form (see the visual basic for applications code). Is there a way of determining the department at startup of your application?
If you, you could use a small subroutine to open the correct switchboard related to the department.
Our startup is something like this.
The Autoexec macro starts a routine MyInit in module StartingUp
The routine MyInit verifies the current version with the one of sql server.
If it is the current version, it displays a login-form. After logging in, the program knows who the user is and opens the appropriate forms and hides the login-form. (docmd.OpenForm ....)
-
I have basically done what you are talking about on the switchboard, as follows. The switchboard is set up normally with multiple switchboards (eg. Main, DeptA, DeptB, DeptC). When the user starts the system before they see the switchboard they login to identify themselves, and there is a user table with things like access level, department, and starting menu. The Switchboard Item table has been modified to have an access level, and list of departments for each item in it, so users only see the entries they are authorized to see. It took a bit of change to the code behind the switchboard form, but not really that much.
Dick Schroth
-
What Dick said and with this code in a module you can obtain the userinfo without prompting them each time they open the program (users get tired of that very quikly;-)
With Username you should obtain the users department from UsersTable and open the according Form/Switchboard
' Declare for call to mpr.dll.
Declare Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, lpnLength As Long) As Long
Const NoError = 0 'The Function call was successful
Public Function GetUserName()
' Returns the Operating System User Name of the currently logged in user.
' Buffer size for the return string.
Const lpnLength As Integer = 255
' Get return buffer space.
Dim Status As Integer
' For getting user information.
Dim lpName, lpUserName As String
' Assign the buffer size constant to lpUserName.
lpUserName = Space$(lpnLength + 1)
' Get the log-on name of the person using product.
Status = WNetGetUser(lpName, lpUserName, lpnLength)
' See whether error occurred.
If Status = NoError Then
' This line removes the null character. Strings in C are null-
' terminated. Strings in Visual Basic are not null-terminated.
' The null character must be removed from the C strings to be used
' cleanly in Visual Basic.
lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
GetUserName = lpUserName
Else
' An error occurred.
' MsgBox "Unable to get the name."
GetUserName = "Unknown"
End
End If
' Display the name of the person logged on to the machine for debugging purposes.
MsgBox "The person logged on this machine is: " & lpUserName
End Function
-
Thank you guys soooo much.. i am going to use all of your advice.. i will let you know how it goes...
-
hi guys, how does the user table that i need to create for the permissions look like? what columns do i need besides username and Item Number and how do i link it to the switchboard?
-
With username u can find out the department. In table where departments are stored you add a field Swithboard where you put in the formname per department. On the first opened form you read out the switchboard field for that user and put that in the docmd.openform
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply