Using Data from Access in SharePoint via SQL

This is a reoccurring issue I have; I don’t know C# beyond one program I wrote 3 years ago when my career path took me away from development and toward other areas. But now, working with SharePoint, this has become a handicap – I want to accomplish a task that is more complicated than straight SharePoint Designer can handle. Unfortunately I don’t have time to defer the task while I learn the C# that would solve this task in a more efficient manner. OK, so it is imperative that I get around to learning C#. Until then what can I do? I do know SQL. This leads me to my current workaround.

Task: Create a site for an engineering project that will store drawings, pieces of equipment (known by their tags), and all specs and requisitions related to those tags. The drawings are created with a program called CADworks. CADworks stores information about the drawings (such as the tag numbers) in an Access database. Pull in the information from the Access database into a SharePoint Equipment list that can be used to tie together drawings (stored as PDF’s in a SharePoint library), Specs (information can be in lists or libraries), Requisitions (lists), and Purchase Orders (library.)

I hoped first to use a ShareVis workflow to take the drawing number as it’s uploaded into the library and pull the data from the Access database and manipulate the list then. However, that functionality does not currently exist with ShareVis – the plug-ins that looked like they might be able to do what I wanted are for forms only. (They did however pass on that request to their development department. I have hope that it will be possible in the future.)

Issue: Can’t insert into SharePoint List via SQL Insert. So can I pull data from Access AND SharePoint Libraries to create a cross-linking data view?

1. Create a linked server to access an Access database. See Configuring a Linked Microsoft Access Server on SQL 2005 Server . Since the CADworks database is stored on the shared drive, I’ll need to create a batch file to copy it to the SQL server nightly.
2. Create store procedure to query access database:

   set ANSI_NULLS ON
   

   set QUOTED_IDENTIFIER ON
   

   GO
   

   — =============================================
   

   — Author:        SharePoint Diva
   

   — Create date: 3/10/2009
   

   — Description:    Pull data from Linked Access Database
   

   — to display in a data view on SharePoint
   

   — =============================================
   

   ALTER PROCEDURE [dbo].[csp_GetComponentsMechanical]
   

       — Search by PID if provided, otherwise use wild card
   

       @pid varchar(40) = ‘%’
   

   AS
   

   BEGIN
   

       — SET NOCOUNT ON added to prevent extra result sets from
   

       — interfering with SELECT statements.
   

       SET NOCOUNT ON;
   

    

       — Inside declare for debugging
   

       — DECLARE @pid varchar(40)
   

       — SET @pid = ‘%’
   

    

   — Insert statements for procedure here
   

       SELECT DISTINCT REPLACE(DWG_NAME_,’.DWG’,”) AS [P&ID], REPLACE(TAG_,’-‘,”) AS [Equip. Tag]
   

       FROM AccessDatabase…[AccessTable]
   

       WHERE DWG_NAME_ like @pid
   

   END
   

    

3. Create Database Connection in SharePoint Designer:

   
   

    

   
   

    

4. Create a data view in SharePoint Designer:

   

    

5. Modify the DataForm field to create a hyperlink to the drawings library based on the value of the field:

   

   

   Address example: http://DevActiveProject/DrawingsDesign/Forms/AllItems.aspx?&FilterField1=P%5Fx0026%5FID&FilterValue1={@P_x0026_ID}

    

   So now I have an equipment page containing data from Access that links to my document library, and because of the query string parameter, I can create a link in my document library that will redirect to a filtered view of my equipment page.

    

   

    

   I’ll cover in a following post the workflow I created for my library to create the hyperlink back to this list.