Connecting to a Cloud Database through Microsoft Access

Connecting to a Cloud Database through Microsoft Access

Overview

This article is for connecting to a cloud database in order to edit non-spatial data through Microsoft Access. This guide is for those using Windows.

Method

Required Installations

  1. Install Microsoft Access (if not installed already)
  2. Install the ODBC PostgreSQL driver. This is so your computer can talk to the remote database.
    1. Go to https://www.postgresql.org/ftp/odbc/versions/msi/ and find most recent x64 zip file.
    2. Download the file
    3. Unzip it
    4. Run the .msi file
    5. Run through the installation menu, leaving everything as default.

Set up the database connection settings

  1. Search the Windows Start menu for "ODBC" and open the "ODBC Data Sources (64 bit)" program
  2. Click 'Add' under the "User DSN" tab
  3. Select "PostgreSQL Unicode x64"
  4. Click 'Finish'
  5. A new box pops up. Enter the details as provided. You can click the 'Test' button to try out your settings.
  6. Click 'Save'
  7. Click 'OK' to close the ODBC Administrator.

Configure Access

  1. Open Microsoft Access
  2. Click 'Blank Database'
  3. Select a location for your new file then click 'Create'
  4. Go to the 'External Data' tab and select 'New Data Source' then 'From Other Sources' then 'ODBC Database'
  5. Select 'Link to the data source by creating a linked table', then 'OK'
  6. Select 'Machine Data Source and select the database you configured earlier, then 'OK'.
  7. Select the Table you need to load and click 'OK'

Making Edits

The database table will appear on the left-hand side. Right-click it and click 'Open' to open a view of the table.
You can make changes to the table as required. To commit the changes:
  1. Right-click the table in the left-hand panel
  2. Click 'Refresh Link'