MS Access – Problems with Sharing Database via Online Storage Services

ms accesssharepointstorage

I have a desktop database I created in Access. I'd like to share the database with a single collaborator, who can have full privileges to the database (table access). However, my collaborator is much less tech-savvy so to make it easier for him, I have set up forms to make table updates, so table access is not a must for him. With that in mind, I am willing to split up the database if this would be a better option.

I had considered sharing the database with him over a Sharepoint website, but have little experience with this and would not know where to begin. I also do not think it would be worth the (relatively low) monthly price to share with a single person. I do not expect our team to grow either, so there is no need to prepare for this. I think the best option is to share the database on Dropbox so that we both have the same version to work with. I've read some forums posts on doing those and all seem to discourage sharing Access databases like this. However, I've had a hard trouble understanding, as I am a novice. There is no need for us to both be able to work on the database at the same time, but I have no idea how I can restrict access to a single user at a time. Can you please explain what the problems are with sharing an Access database in simple terms and what workarounds there is, if any?

Please don't suggest other options as I am trying to keep this project as simple as possible and my experience with SQL is minimal. Plus, I need to keep this simple for my collaborator as well and this is a sort of personal project I don't have a ton of time and resources for.

Thank you for the help!

Best Answer

To put it simply - it would work if only one of you were editing the database at a time. If both of you were to edit the database, it could corrupt the file, discard each other changes or cause sync issues.

By the way, have you considered Google Docs?

Related Solutions

Sql-server – SQL Server Permission Denied Error When I’m DBOwner

From what I see you created a server login with server roles (DBCreator and SecurityAdmin are server roles). But this doesn't mean it already has privileges on an existing database.

What you need to do is to create a user in that database and map it to the login:

use your_db
create user x for login y
exec sp_addrolemember 'db_owner', 'x'

This will assign your new user the database role db_owner to the database. Now you should be able to create tables with the login. You can try also other roles, if you don't need to be the db owner.

Stored Procedure Performance Issue in Sharepoint Report

Your Sharepoint db instance will have a ReportingService_weirdsequenceofcharacters database. It stores report execution and performance metrics. There is a view called ExecutionLog2 in that database which has 3 Times. Use them to find whether the bottleneck is sql, report processing, or rendering. You should also be able to compare these values to the values from your production report server (if you have one). A non-sharepoint report server database will typically be named ReportServer.

TimeDataRetrieval

The number of milliseconds spent interacting with data sources and data extensions for all data sets in the main report and all of its subreports. This value includes:

Time spent opening connections to the data source
Time spent reading data rows from the data extension

Note: If a report has multiple data sources/data sets that can be executed in parallel, TimeDataRetrieval contains the duration of the longest DataSet, not the sum of all DataSets durations. If DataSets are executed sequentially, TimeDataRetrieval contains the sum of all DataSet durations.

TimeProcessing

The number of milliseconds spent in the processing engine for the request. This value includes:

Report processing bootstrap time
Tablix processing time (e.g. grouping, sorting, filtering, aggregations, subreport processing), but excludes on-demand expression evaluations (e.g. TextBox.Value, Style.*)
ProcessingScalabilityTime (see AdditionalInfo column)

TimeRendering

The number of milliseconds spent after the Rendering Object Model is exposed to the rendering extension. This value includes:

Time spent in renderer
Time spent in pagination modules
Time spent in on-demand expression evaluations (e.g. TextBox.Value, Style.*). This is different from prior releases, where TimeProcessing included all expression evaluation.
PaginationScalabilityTime (see AdditionalInfo column)
RenderingScalabilityTime (see AdditionalInfo column)

http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx

Related Question