Is there a way I can allow a group of users to enter their own data?

[JenniferMurphy]

I am still working on a project to distribute LP albums that I started 3 years ago. It is discussed here:

Suggestions for allocation algorithm

I volunteered for a little neighborhood project that has turned out to not be that "little". Several neighbors, including myself, mentioned that they had boxes of old vinyl record albums that they no longer want since they do not have a turntable. Several others said they would love to have the...

www.mrexcel.com

I now have over 1,000 albums. As discussed in the thread above, I have entered them in an Access database. Now I need to provide a way for each of the 20+ users to select the albums they want.

I could send each of them a copy of the database with a form that would allow them to make their selections. I would then need to merge the selections and apply some code to make the final selections. I could export the Access data to an Excel sheet and then do the same thing but with an Excel workbook and a form.

This approach has some serious limitations. If I get more albums, I would have to modify the database and then send out new copies to everyone. To make that work, I would have to first ask everyone to send me their copies so I could add the new albums without erasing any selections they may have already done. That is a pain in the neck for everyone and error prone.

I would like a way to put either the Access database or the Excel workbook "online" (OneDrive? DropBox? ???) with a "column" for each user and some sort of "protection" so that each user could see the album data and their selection column, but not the selection columns for any other users. They could enter their selections in their selection column, but the rest of the data would be read only. That way, I could make the data I have now available and the users could start making their selections. If I add any albums, they would see them the next time they access the data. I could then set a deadline for making selections, after which I would lock the dataset.

With this approach, there would be just one copy of the data. That should eliminate most of the errors. I envision the data set to look like the illustration below. Columns G-N contain the album data. That would be read only for the users. The actual data has several additional columns with other data about each album. Columns C-F are the selection columns headed by that user's initials. User TR, for example, would only see Columns C and G-N -- not any of the other selection columns (D-F).

Is there a way that I can make this available online preferably with Excel, or with Access? I assume that I would have to make a different form for each user.

Does OneDrive provide a way to give each users a unique password that will let the application know which form to load?

Thanks for any suggestions or pointers.

 

[JenniferMurphy]

PS: I forgot to mention that the selection columns, C-F, contain the users' selections. These are priority numbers 1-5.

 

[alansidman]

Here is one option. Share Your Microsoft Access Database Online with Access Database Cloud - Computer Learning Zone

I don't believe that you can share on Dropbox.Com or similar shared file services.

 

[JenniferMurphy]

Here is one option. Share Your Microsoft Access Database Online with Access Database Cloud - Computer Learning Zone

I don't believe that you can share on Dropbox.Com or similar shared file services.

It looks like that would do the job, but at a price that is a lot more than I can justify spending for a project that I am doing for free. If I understand the pricing right, for me to allow 20 neighbors acess to the database, it would be 20 x $45 + $48 = $945/month. Yikes! I'd rather spend that on the grandkids.

Is there no way for me to do what I want to do with OneDrive?

 

[awoohaw]

@JenniferMurphy
It may be convoluted. But, you could create some kind of excel form from a shared excel file on your Personal OneDrive that you can then link to from your MS-Access database. I have linked to excel files before, but not ones that were populated using a form or on OneDrive. But, I do know that microsoft forms are not in personal users tool kits so you may have to make an excel workbook look like a form.

 

[alansidman]

Yikes! I'd rather spend that on the grandkids.

I concur on this statement. I have tried in the past to do what you want and unless everyone is on the same network, then Access does not play friendly. Dropbox.com and Box.net do not play well with sharing Access files. Good Luck on this one.

 

[JenniferMurphy]

@JenniferMurphy
It may be convoluted. But, you could create some kind of excel form from a shared excel file on your Personal OneDrive that you can then link to from your MS-Access database. I have linked to excel files before, but not ones that were populated using a form or on OneDrive. But, I do know that microsoft forms are not in personal users tool kits so you may have to make an excel workbook look like a form.

Suppose I put the workbook containing the table above in a OneDrive folder? I give each taker a password and access to the workbook. Loading the workbook would run a form which would ask for the password. If accepted, it would allow read-only access to the album data (columns H-N) and read-write access to their selection column (C, D, E, or F). They could each make their selections in their column.

If I add any albums, they would be visible the next time they log in.

When the selection period is over, I'd lock the sheet and process the selections.

Would something like that work? How difficult would it be to more or less guarantee that each taker could only access the album data and only edit their selections? These are friendly neighbors, so I don't anticipate any shenanigans, but there could be mistakes.

Thanks for any comments, suggestions, or references.

 

[JenniferMurphy]

Two questions:

1. Can Excel access data in a cloud folder (OneDrive, DropBox)?
2. Can I create an Excel form that will run on both Windows and Mac systems?

Thanks