[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Move an Access db back end to MSSQL in a SharePoint lab environment

Follow on from a prior question on an issue with an Access db back end having ID field conflicts.  Suggestion was to move the Access tables to SQL first, that this would preserve the ID fields in the numbering scheme that is lost when using the Access to SharePoint export tool.

Goals are 1) to moves ~12 tables with ~1,600 records to SQL on the SharePoint lab machine, 2) Present the tables in SharePoint lists for view/edit etc. as they would be if exported via Access and 3) Continue to use the current Access front end connecting to the tables in SQL in place of the current Access tables back end.

Since this is really a three part question; can we start by asking the first obvious, which is "is this possible/best solution?" All is lab right now but as you all know even building a SharePoint lab is crazy time consuming, so if a great risk, shoot the idea down would be an acceptable answer, with extra "points" for an alternative idea.
0
VirtualKansas
Asked:
VirtualKansas
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Why not just move the backend to SQL Server and be done with it? You'll find quite a few limitations with Sharepoint, and most regret the decision to move an Access database to Sharepoint.
0
 
VirtualKansasAuthor Commented:
OK; I'm sensing a re-occuring theme in all responses to scuttle SharePoint for this project.  I'm now officially accepting the redirection advice (ouch, but better to know now.)

If you will allow me to step back, then and glom onto this thread a little more; the intent has always been to make real time web-enabled progress reports for the project that Access data is managing.  So setting SharePoint aside (looking like that is inevitable), I will have to open a new question pertaining to creating a solution that presents Access reports via web, with the caveat that this db vintage (the front end forms, queries & reports) doesn't lend itself to the task and the db is live so the issue is intractabl.e (my first clue was when a SharePoint Acccess services import flat out told me that the db was too old to web enable reports.)

So; long story short, will open yet another thread to discuss question of how to web enable vintage Access reports, with my thanks to you and prior reponders for what looks like saving me alot of unneccsary pain attempting to apply SharePoint to the task.

But; if you would consider responding to the original question of how to move back end tables to SQL while keeping the ID's of the troublesome table; I will take a stab to gain the experience.
0
 
KoenVostersCommented:
Have you considered dropping access from the equasion and moving the database to SQL And use the Business Connectivity Services from SharePoint to get out of the box forms on your data?

http://blog.karstein-consulting.com/2011/02/23/walkthrough-create-a-simple-bcs-connection-with-sharepoint-designer-2010/
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jeffrey CoachmanCommented:
<Follow on from a prior question on an issue with an Access db back end having ID field conflicts.>

For me at least, ...it is not clear what this means or how sharepoint/SQL would resolve this....
0
 
VirtualKansasAuthor Commented:
RE: Have you considered dropping access from the equation...

This is a great walk through and will help a great deal in probing the solution.  The challenge remains that the access db has many tables and queries, reports, etc., which I'm guessing can be managed by the access front end connecting to the sql/sharepoint migrated data and that's ok for a future question.

to be clear; will the question "...this would preserve the ID fields in the numbering scheme that is lost when using the Access to SharePoint export tool" be resolved by following this method?  If, so I can close this question with my thanks and then give it a go...
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I will have to open a new question pertaining to creating a solution that presents Access reports via web, with the caveat that this db vintage (the front end forms, queries & reports) doesn't lend itself to the task and the db is live so the issue is intractabl.e
You can't directly present Access reports on the web. Access reports must be presented within the Access framework - and you can't run Access from a webserver. If you need live, realtime reports you should consider building a web-based app to handle that.
0
 
VirtualKansasAuthor Commented:
I'll wrap this one up as answers have directed me well enough to test and progress.

One additional note; KoenVosters lead me to the http://blog.karstein-consulting.com, which is a total treasure trove of well formed step, by step information.  I'll follow the link in the answer specifically to verify in test if the pesky ID field makes it through to SP and will also accept as an answer "...most regret the decision to move an Access database to Sharepoint." as good advice is this whole thing continues to be such a chore.

Thanks & regards...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now