Access Database Process and Many External Users Updating

I have a question regarding an access database process. I have a spreadsheet on about 50,00 records and a large number of those records gets updated manually by a team of about 6-7 people. Here is the process I am proposing.

1) Run report and save on sharepoint site (not is spreadsheet form since it is too large)
2) Import to Access DB
3) Run any logic that needs to be applied (update queries and such)
4) Export spreadsheet and save on sharepoint
5) Users update spreadsheet and resave
6) Reimport bi-weekly and append changes

Can you think of a better way to do this? Updating in Access takes too long ( that is what the team tells me).

Thank you all so much for any input!
kwarden13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
If you are going to use SharePoint, why not simply save the data in a SharePoint list and forget the import/export process?  Access can link to it directly.  Your users would update the SharePoint list rather than the spreadsheet.
0
MlandaTCommented:
That sounds like a very complicated approach to solving a simple problem. Yes... It's often faster to do data entry into Excel than most other platforms. But at the same time, that cannot be used as justification for introducing complexity into the process.

I would actually create a SQL Server database, and give people Access front ends which connect to this database. They can search and filter and do updates on the SQL Server database.

You can apparently also use excel sheet with macros as the front end to the SQL Server database. Use a variation of the concepts discussed here http://www.toadworld.com/platforms/sql-server/w/wiki/10392.editing-an-sql-server-table-in-excel

But keep it simple. The process you describe would be too cumbersome to maintain. Work on improving the access experience. Understand it better, optimise it
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kwarden13Author Commented:
I don't have access to an SQL Server and am using Access 2010. I am quite good at programming. I should mention the team updating is in India and I am in the US.

What I am gathering is at minimum create an access db and have them do the updating in the db as well. Basically eliminating any spreadsheets. I do think the spreadsheets are quite cumbersome and difficult to maintain as well. I just walked into this job and have a database/programming background while the team has bee doing this process for 8 years and have no knowledge of dbs.

I will dig around a bit.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

kwarden13Author Commented:
Also the data is about 50k to 100k records so I dont think a sharepoint list would work
0
MlandaTCommented:
The SQL Server option is worth considering, for performance and reliability. There are free versions which you can setup quite quickly and easily
0
PatHartmanCommented:
It is the shared data that is problematic.  Although you can create a SQL server database and have people all over the world link to it using your Access FE, you will probably find it too slow to be viable.  I dislike SharePoint and would not recommend it as a "database".  However, as a means for a widely dispersed population to use for data entry, it is a low cost solution if you already have SharePoint available.  You can tell within an hour if it is viable.
0
John TsioumprisSoftware & Systems EngineerCommented:
There is another solution if you don't have great speed demands given the fact you don't have a lot of records....
You can setup a cloud VM and connect your Access to the Cloud...
It will require some work but all your data would available anytime ...
Personally i have found a free VPS service and i have a nice MySQL installation for all the data i need to have access to them...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.