How to Setup Microsoft Access Database Connection

By using Microsoft Access, users can add a secondary data connection to the database, but by doing so, Access can only handle queries. On the other hand, users are unable to add a secondary data connection that sends form data to a Microsoft Access database.

How to Setup Microsoft Access Database Connection

You can create a form template based on a Microsoft Access database and then enable the submit data connection in the primary data connection if you want your users to submit form data to an Access database. As an alternative, you can extend a Web service that integrates with your Access database using a secondary data connection.

  • Step 1: Add Secondary Data Connection

    Go to the Data Connections on the Tools menu and click Add. In the Data Connection Wizard, select “Create a new connection to”, then “Receive data” and “Next”. Then, hit Database (Microsoft SQL Server or Microsoft Office Access only), and click Next. Choose “Select Database”. In the Select Data Source dialog box, locate your database and click Open. Next, select the primary table or query you wish to use in the Select Table dialog box, then click OK. Check the Show table columns checkbox. Clear the check boxes next to any fields that you don’t want the secondary data source to include. You can add any additional tables or queries to the data connection. Click Next. Select the “Store a copy of the data in the form template” checkbox, then click Next to make the results of the query data connection accessible even when the form is not connected to a network. Assign the secondary data connection a name, and then make sure the data in the Summary section is accurate. Check the “Automatically retrieve data when a form is opened” checkbox to set up the form to automatically receive data when it is opened.

  • Step 2: Setup the Data Connection in the Form Template

    To use the data connections, you can either set up a rule or a button. To add a rule, click “View name” on the View menu. Double-click on the Control to add a rule. Select the Data tab and choose Rules under Validation and Rules. Select Add from the Rules dialog box and type the rule name in the Name field. After clicking Set Condition, enter the condition to determine under what circumstances the rule should be run. Select “Add Action” and in the Action list, click “Query using a data connection”. Then, click the query data connection that you want to use, and click OK. On the other hand, to add a button, click “View name” on the View menu. If the Controls task pane is not visible, press ALT+I, C or select More Controls from the Insert menu. Then, drag a button control onto your form template and double-click it. Hit the General tab and click Refresh under the Action list. In the Label box, type the button name. After that, select Settings and choose one of the following actions in the Refresh dialog box: Either click “All secondary data sources” or “One secondary data connection” and then click a secondary data source in the Choose the secondary data source list. Finally, click OK.

FAQs

How do I add additional tables or queries to the data connection?

Under the Select Table dialog box of the Data Connections wizard, click Add Table, then in the Query dialog box, click the name of the child table, and then click Finish and repeat the steps to add another table.

How can the form template be set up to use the data connection?

You may add a rule to the form template that utilizes this data connection only in particular circumstances, or you can add a button to the form template that your users can click to use this data connection.

What is a secondary data connection?

A secondary data connection is a data connection that you include in a form template.