Solved

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

Posted on 2013-01-05
7
303 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

756 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