Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access tables export as a routine to a sql instance sharepoint 2010 will then use as a source

Posted on 2013-01-19
5
Medium Priority
?
421 Views
Last Modified: 2013-01-21
Ridiculous to be sure; but a production environment using a split access database cannot be disrupted; so building scaffolding around the production access tables for reports, etc.

If proof of concept materializes, then SharePoint could well take over as it was intended; where sql on SP will be the back end, access front end can continue to do the job as it has in the past and SP can start doing the things it does to serve information based on common (access front end and sp) sql data.

Now question; is it possible to routinely export access data to a sql instance that sharepoint can use for external content?  Keeps the access tables db alive and alone in production, whilst sharepoint dev/test can continue using somewhat live data.  I see no way for sp to connect directly to access db tables, which would be best outcome.  Have successfully exported access tables to sp using access and all looks fine.  But given data is a one time load from access to sp; how to keep the sp data up to date, at least once/day?

As I said "ridiculous" to be sure, but best idea I've had to get out of the rut and allow sp dev lab to progress, while access live db stays untouched in production.
0
Comment
Question by:VirtualKansas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 28

Assisted Solution

by:burrcm
burrcm earned 1000 total points
ID: 38797873
If the db is small enough to run in Access, then the uber simple solution would be to schedule a delete all from the sql db and reload the whole database, at a time when there is nothing much going on. If there is no idle time, then it would have to be appends based on new records; the simple way would be daily based on date.

Chris B
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1000 total points
ID: 38797893
Well, you also mentioned that Access was front end - does that mean you have plans to move the database to SQL ?

SQL Server can connect to Access and import directly

You can also control it from the Access end and there are various documents like : http://office.microsoft.com/en-au/access-help/import-or-link-to-sql-server-data-HA010200494.aspx or http://support.microsoft.com/kb/892490 as a couple of examples

But I would be inclined to set up a linked server (in SSMS) to it can read the access database.

You could also use an openrowset operation to access the access tables...

e.g.  In SQL Server

-- openrowset to access a specific table (customers) in an Access DB (C:\EE\Test.accdb)

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','C:\EE\Test.accdb';'Admin';'',Customers)
GO

-- or set up a linked server to Access as a more permanent connection :

EXEC sp_addlinkedserver 
      @server = 'lnksvr_Access', 
      @provider = 'Microsoft.ACE.OLEDB.12.0', 
      @srvproduct = 'OLE DB Provider for ACE',
      @datasrc = 'c:\EE\Test.accdb'
GO

-- now add in a login - Office does like to have an "admin" user

EXEC sp_addlinkedsrvlogin 
      @rmtsrvname = 'lnksvr_Access',
      @useself = 'FALSE',
      @locallogin = NULL, 
      @rmtuser = 'Admin', 
      @rmtpassword = NULL
GO

-- now just to test if linked server worked properly, let's list all the tables

EXEC sp_tables_ex 'lnksvr_Access'
GO

-- and then we can simply use a 4 part identifier for the select clause

select * from lnksvr_Access...customers

-- and then drop the server when we have finished playing with it

sp_dropserver lnksvr_Access, droplogins

Open in new window


Now, there needs to be a trusted relationship from the actual server to where the Access datbase lives.

Then when it comes to updating a local database table in SQL Server, then simply do an "insert ... select ... where not exists ()" type update on the SQL Server table.

If there are unique keys, then use them to first update if already there, then insert if not already there, and delete if they are there but shouldnt be.

And it is not that ridiculous :)
0
 

Author Comment

by:VirtualKansas
ID: 38798407
Good morning EE's and thank you for replies...  I'm  little thick headed this early on a Sunday, but thank you for the plans.  Will follow through, shortly.  Thanks & regards...
0
 

Author Closing Comment

by:VirtualKansas
ID: 38802983
I have to be honest and close this one out with my thanks.  There has been a temporary redirection of efforts and I won't be able to get back to this thread in a timely manner; so will close and split points as haven't been able to use either excellent ideas.  Apologies if even split is lop sided; clearly both answer are on point.  Thank you, talk to you soon...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38803676
No problems, but if you do get a chance and post another question, would be good to see the link here too :).

Cheers, and hope to see you again soon.

Mark Wills
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

721 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