Solved

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

Posted on 2013-01-05
7
297 Views
Last Modified: 2013-01-07
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
Comment
Question by:VirtualKansas
7 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 38746983
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
 

Author Comment

by:VirtualKansas
ID: 38747004
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
 
LVL 14

Accepted Solution

by:
KoenVosters earned 400 total points
ID: 38750172
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38751269
<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
 

Author Comment

by:VirtualKansas
ID: 38751460
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
 
LVL 84
ID: 38751882
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
 

Author Closing Comment

by:VirtualKansas
ID: 38751980
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now