Solved

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

Posted on 2013-01-05
7
301 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SharePoint 2013 URL Duplication Issue 5 25
Access 2016 Transfer Spreadsheet Headache... 4 34
default combobox value 12 18
Access VBA to Count Worksheets in a Workbook 3 21
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

839 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