Exporting data from Access to SharePoint and Importing data into InfoPath from a SharePoint List

Category: Administration

Level: Beginner 

This beginner level tutorial will show you how you can export data from Access to SharePoint and then import data from a SharePoint list into InfoPath. Let me tell you that you can also create an InfoPath form that could access data in an external database including Access. Using a built-in wizard in InfoPath, creating such a form is very easy. You can display data from Access in the InfoPath form. You can also incorporate search feature with ease using the wizard. This tutorial is divided into parts. The first part shows how to export data to SharePoint from an Access database. 

Export data from Access to a SharePoint list.

Detailed Steps:

1.       Export Access data to a SharePoint list. Go to 'External Data' in Access and select 'SharePoint List' from the 'Export' section.

Well, honestly speaking that's the only step required. Using the wizard you can either select a pre-built SharePoint list or you can create a new one before exporting data.

Importing data from a SharePoint list into an InfoPath Form

1.       Create a new data connection in InfoPath and select following options:

a.       Create a new connection > Receive data

     

b.      Select "SharePoint library or list" option.

c.       Enter the (SharePoint) list's path where you exported the Access data.

d.      Select your list from the list of lists and libraries.

e.       Select the field (or fields) that you want to show in InfoPath.

f.        Check 'Store a copy of the data in the form template'.

g.        Add a name for the data connection and check the "Automatically retrieve data when form is opened" option.

h.      Click "Finish".

Use following steps to populate a text box with data imported from MS Access:

1.       Add a text box in your form.

2.       Right click the text box and select "Text Box Properties" to open the properties page. Enter a default value for this field (Click the button (fx) shown on the right side of the "value" text box).

      3. Click 'Insert Field or Group.' button.

      4. Select secondary data source from the 'Data source' drop down.

      5. Expand all nodes until you see field names. Select the field that you want to retrieve value from.

      6. Click OK twice and that's it!

You can use more fields to display data from different columns of the Access table.