Access Deployment

[nidaz17]

HI,


I have googled dozens of pages but haven't found the answer to fit my scenario.

I have made an access database which will have two users. its an inventory database ( similar to the template)
I want some forms and tables to be used by my sales dept and others by the warehouse inventory stockers.so i made two category views and two forms as switchboards for both groups/users. now i want that when the sales people log in they see only their category from the navigation pane and the other group sees only theirs. i implemented user level security using work group file and created their log ins but i dont know how to set the way the navigation pane appears for each user.

Now i would like to make this clear that this database is not being shared on a network. it will be such that the sales ppl enter their PO's for the day email it to the warehouse and the ppl there ,then use that info to maintain the inventory tables on the same database.then they email it back to the sales. (i know pretty lousy; but what do i do to synchronize the two) What do i do with the workgroup information file in this scenario?{Security is not my main concern}. my main concern is ease of use and data integrity that is it!
actually i'm working on this on asp.net C# and sql server but while the development for this goes on i'm using this access one.(pretty clear i'm no expert on acces and VB)

Please help me i've developed the whole thing and am dying to put it into action so that i can move on to developing the real thing in .net and sql.

[Datagopherdan]

This can get tricky. I have a database that I developed not too long ago where personnel work remotely, so they need their own copy of the database and can only view their relevant information. The way that I set this up is that when a record is created, it is assigned to a staff memeber. Next, theres a function where they select a staff member and a database will be automatically created for them. The database that is created will be filtered to only contain the records that were assigned to them. Theres another function as well to synchronize the data where the data from the staff member's database is uploaded to the main database and if theres any new records that are assigned to the staff member, those will be added to the staff member's database.

I don't know how familiar you are with VBA coding but basically much of this was performed in VBA by getting the path of the staff member's database to create remote queries off the path in order to transfer data back and fourth. If this sounds along the lines of what you're trying to do, let me know and I'll put together a stripped down version of the database with some of the code to get you started.

Dan
Access Development

[nidaz17]

hmm ..urs seems to be amore complicated situation.
in my case the ppl from both dept will be writing into different tables. so i just want to know that if they import from each other the tables get updated? or is it the case then if warehouse ppl import then the sales tables get updated but the warehouse tables get back to the state they were a day ago(that day's data being lost)

and please i need functions for getting the group of the signed in user and then allowing him to see the navigation pane as per his category. how do i do that?basically wat do i write in the autoexecmacro or VB code for the db to check who has logged in and then open up the navigation pane as per his category.

in my senario how do i specify the workgroup file for the two remote depts.

[Datagopherdan]

Mine really is not that complicated. The challenge was in figuring out the flow and the logic to it all. With mine, theres no chance that the users will be overwriting one another's data because they are all working on different records. If it's a case where remote users who will be uploading data can be working on the same records, then it gets alot more tricky.

As far as only being able to see their data. That's exactly what the database I described does. I was under the impression that your users will be using their own standalone copies of a database that is not linked to a shared back-end. Their data would need to be uploaded, is that not the case?

If that's not it and it's a multi-user environment then the solution is much simpler. You can use the "CurrentUser" function as part of a SQL statement to specify which records a given user can view. Theres a million and one variables to this. If you could upload a stripped down version of your db, that would help alot. I could see what you're working with and what you want your results to be.

Dan
Access Development

[nidaz17]

Both can view all reports but just not write in the others portion or see each other's forms. like i've made separate forms for writng in a table and for viewing it.
Its like it is designed as a multi user database so that i cud put it on network but as of now its being used like a record book that which gets transmited here n there physically(emailing)but the ppl at each end should also keep there respective copies. so that now when they get the emailed copy they import from it ;i dont know if this wud overwrite their tables in their copy too.

yeah i know it has to do something with current user but i dont know how to use it. can i zip n email my db structure or should i explain verbally. basically its based on the inventory template so there isnt much to hide
Also please advice abt my work group file query

[Datagopherdan]

If you can upload a copy of it here or just go to my site by clicking on the "Access Development" below. I have a user form, which accepts uploads or you can just e-mail it to me.

Dan
Access Development

Both can view all reports but just not write in the others portion or see each other's forms. like i've made separate forms for writng in a table and for viewing it.
Its like it is designed as a multi user database so that i cud put it on network but as of now its being used like a record book that which gets transmited here n there physically(emailing)but the ppl at each end should also keep there respective copies. so that now when they get the emailed copy they import from it ;i dont know if this wud overwrite their tables in their copy too.

yeah i know it has to do something with current user but i dont know how to use it. can i zip n email my db structure or should i explain verbally. basically its based on the inventory template so there isnt much to hide
Also please advice abt my work group file query

[nidaz17]

Ok i've emailed it to you. please help me in my above queries..
you can view the categories i've created and see the switchboards in both to know the functionality for each category. i want help for the navigation pane to open according to the group(category) the user belongs to. and advice on deploying the work group info file and data synchronization(importing the db)in the scenario i've described.
Thank you very much.

[Datagopherdan]

Ok, I'll try to explain this as best I can. Looks like your users will have separate databases where they will not be tied into a network, so you would need to assign them their specific records and have the ability to upload their data to a central database, correct? If that's the case. First, you would need to dynamically create a database for them based off a template that you would create. Here's the code.

'Create a new database for the selected staff member
    Dim fs As Scripting.FileSystemObject
    Dim strSourceDir As String
    Dim strDestinationDir As String
    Dim strName As String
 
    'Ensure that a selection was made
    If IsNull(Me.cboFind) Then
        MsgBox "You must select a staff member.", vbInformation
            Exit Sub
    End If
    Set fs = New Scripting.FileSystemObject
    strSourceDir = "C:\YourFolder\StaffTemplate.accdb"
    strDestinationDir = "C:\YourFolder\StaffCopies\" & Me.cboFind.Column(1) & ".accdb" 'This is the name of the staff member that is selected from a drop-down
 
    fs.CopyFile strSourceDir, strDestinationDir, True
    MsgBox "Database has been successfully created for " & Me.cboFind.Column(1)
    DoCmd.SelectObject acForm, "frm_New_DB": DoCmd.Close

Next, you would need a synchronization function that would upload all the person's data who you selected to their database. You would do that by creating a series of remote queries for the record source and then running action queries that would be based off the queries that you created. This is pulling the path from a file dialog that you would use to select the database to synch.

strSQL = "SELECT tbl_Indiv_Treat_Plan_Dev_Pers.*, rcl.cl_pers_ID, " & _
        "[Forms]![frm_Download_Upload]![pers_Name] & ' - ' & [itp_ID] AS qsel_Pers_Name, " & _
        "[Forms]![frm_Download_Upload]![pers_Name] & ' - ' & [itpdp_ID] AS qsel_Pers_ITPDP " & _
        "FROM ((tbl_Client_List AS rcl INNER JOIN tbl_Functional_Assessment AS " & _
        "rfa ON rcl.cl_Client_ID = rfa.fa_cl_ID) INNER JOIN tbl_Indiv_Treat_Plan AS " & _
        "ritp ON rfa.fa_ID = ritp.itp_fa_ID) INNER JOIN tbl_Indiv_Treat_Plan_Dev_Pers ON " & _
        "ritp.itp_ID = tbl_Indiv_Treat_Plan_Dev_Pers.itpdp_itp_ID IN " & strPath & _
        "WHERE (((rcl.cl_pers_ID)=[Forms]![frm_Download_Upload]![pers_ID]));"
qdfITDP.SQL = strSQL

Probably a little confusing on paper but hopefully this gives you the general idea on how to set it up.

[nidaz17]

Sorry but i totally do not get what you're talking about. my database is as simple as can be there are no separate databases. its simply a case of different users using the same database but that database is not shared on the network. it might be put on the network later on but right now they'll simply enter records into their tables through the forms i've created for them. then the entire application will be emailed to the other department , where they will add records through their forms. i'll check out the importing facility of access myself now forget about this.

i just need macro functions for opening and making visible different forms for the different types of users.

If you view my database you'd see the categories i've created and see the switchboards in both to know the form visible to each category. i want help( code, macro function) for the navigation pane to open according to the group(category) the user belongs to.

and advice on deploying the work group info file.

I'm sorry but can anyone understand what i'm asking for?

[maximus]

So basically you have the same application used by two different groups. 1 group enters data then as you say the entire application is emailed to another Department and they enter data relevant to there department. Now i assume you requirement is that you want the respective departments to be able to open the data entry forms that is applicable to there department

Is my understanding of your situation correct.

[nidaz17]

yes, exactly.
i'm very grateful to Dan for all the time he has given me through his solution but my situation is simply the one mazimas pointed out. As i pointed out earlier: security is not my foremost concern . its just the ease of use and data integrity that would be available if a different view is available to each group of users.
i've made two separate switchboard like form which point to the forms and reports for bopth types of users. i've made visible the tables, reports and forms concerning each group separate by using the categories provided in navigation pane. Now i simply want that one one group of users opens the database and logs in using their account they see the navigation pane open in one way and when the other group does so they see a different view of the navigation pane. i've given the rights and access through the user level security using workgroup file so even if the users get into others forms they wont be able to go beyond their permissions.
howvere i just dont want that to be visible which does not concern them. for this i desire the code.

and how do i share my work group information file in such a scenario. i've read that when u share on network put the workgrp file on the server and specify it in the shortcut what do i do in this case?