Hide Database Window-Not Just on Startup 2

[meghan95]

Hello,
This seems like it should be simple, but maybe I'm not looking in the right place. I have an Access 2000 database located on a shared drive that everyone in the company can use. Users only need to run reports that I have already created.

I currently have a Main Switchboard that runs on startup and gives a listing of several possible reports to run. I have set the startup options so that the users can't see the database window (unless they know about F11, etc., but I'm not worried about that now).

However, there is another group of users that will need a different set of reports (don't want to give the whole company access to all available reports). Can I give them a shortcut to another form acting as a switchboard and deny them access to the main database window as well?

So far I've only seen this hide database window option for startup, but startup is already associated with my first Switchboard.

Any help would be appreciated.

 

[FancyPrairie]

Several ways to do it. Here's one.

Right click on the form you want and select the submenu item "Create short cut".

 

[meghan95]

I forgot to mention that I'd rather not use Access security if I can get away with it. This database is an export (that I did not design) from a Microsoft Outlook based CRM program that we have implemented. The built-in reporting that is part of the software program is not very robust, so they created this export for us. I am very much a beginner in maintaining this database.

When I posted this I had not seen some of the additional posts to the other thread about keeping users from nosing around. I will try some of those suggestions since it seems to be a similar situation.

Thanks

 

[bergers]

Hi there,
Following is code I have seen to get the User Name and Computer Name of the current PC. You may be able to use this along with a system/hidden table (table name beginning with 'usys') that contains the user names and their permissions. Then make an 'autoexec' macro that opens the appropriate form depending on the current user name.
Typically atCNames(1) = the User Name, atCNames(2) = the computer name... Hope this helps.

Kindest regards

Private Declare Function api_GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function api_GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function atCNames(UOrC As Integer) As String
'**************************************************
'Purpose: Returns the User LogOn Name or ComputerName
'Accepts: UorC; 1=User, anything else = computer
'Returns: The Windows Networking name of the user or computer
'**************************************************
On Error Resume Next

Dim NBuffer As String
Dim Buffsize As Long
Dim Wok As Long

Buffsize = 256
NBuffer = Space$(Buffsize)


If UOrC = 1 Then
Wok = api_GetUserName(NBuffer, Buffsize)
atCNames = Trim$(NBuffer)
Else
Wok = api_GetComputerName(NBuffer, Buffsize)
atCNames = Trim$(NBuffer)
End If

End Function

 

[Stevehewitt]

Hey,

I might be missing the point here, but can you not just import the main structure of your current DB into a blank new DB, add in your reports for these other users, and then have both access DB's in the same shared area, but put NTFS permissions on the new DB with the reports? (I am assuming you are using NT!)
Steve Hewitt
Systems Manager

 

[meghan95]

Fancy Prairie - I've already created the shortcut. My problem is being able to hide the database window from being viewed once the user clicks on their shortcut (since this will not be the default switchboard upon opening the database)

bergers - seems a little complicated for me (I learned Access by using the "Teach Yourself in 21 Days" book so am not very good with code yet but might give it a try.

Steve - Sounds like a logical solution that I'm working on now. Like I said, the main database is created from an export engine that is setup to run every 30 minutes. I guess the import would then have to run to keep everything up to date.

Thanks for the suggestions.

 

[FancyPrairie]

Here's an easy way to handle it. You don't have to worry about modifing your code as new users are added.

Setup a table that contains 2 fields: The users name and the name of the form/report the users wants launched at startup. (Forget about the switchboard). In the AutoExec macro, launch the following function.

Note that if a record for the current user does not exist, a form is opened that asks you to register the user (i.e. enter name and report to open). Then loops back to start the process over again. This step is not necessary, but helps you keep things up-to-date as new users are added.

Function StartUp()

Dim dbs as DAO.Database
Dim rst as DAO.Recordset

Set dbs = CurrentDB
10:
set rst = dbs.OpenRecordset("Select * from YourStartUpTable Where strUserName = '" & CurrentUser & "'"

if (rst.eof) and (rst.bof) then
Docmd.OpenForm "OpenAForm to register the user"
rst.Requery
goto 10
End if

DoCmd.OpenReport rst!TheReportToOpen

rst.close

End Function

 

[FancyPrairie]

Forgot to mention. Since you don't want to use system security, then store the user's network id in your table. Then get the user's network ID as explained in bergers post. Code would look like this:

Function StartUp()

Dim dbs as DAO.Database
Dim rst as DAO.Recordset

Set dbs = CurrentDB
10:
set rst = dbs.OpenRecordset("Select * from YourStartUpTable Where strUserName = '" & atCNames(1) & "'"

if (rst.eof) and (rst.bof) then
Docmd.OpenForm "OpenAForm to register the user"
rst.Requery
goto 10
End if

DoCmd.OpenReport rst!TheReportToOpen

rst.close

End Function

 

[meghan95]

FancyPrairie - thanks for all of your efforts. One quirk to this database is that everytime our export runs from the other software program, it deletes all existing tables and replaces them, even ones that have been created in Access and not by the export. So I don't think any additional tables would work. I gave you some stars, though. I'm sure someone else will run across this problem and will benefit from your code.

I decided to try Steve's suggestion of creating another database, and I've actually linked all tables from the first database. This is working perfectly right now.

Thanks to all.