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'




    • Related Articles

    • Publishing Layers from a Remote Database

      Overview XMAP supports connections to any PostGIS database of version 9.6 or greater. This database can be hosted and managed by us or you. If you have a remote database you can use this to connect to many software applications to improve access to ...

    • Restrict Access via Entra/Global Secure Access

      Conditional Access for XMAP Using Entra and Global Secure Access (GSA) you can apply access policies to XMAP which can evolve over time with your own security requirements. For example, you can restrict authentication to XMAP to only occur within ...

    • Database Manager

      Overview The Database Manager is made available if your organisation has 1 or more dedicated (private) PostgreSQL databases linked to it. It exposes administration functionality for you to manage your database at a high level without the need to ...

    • Database Best Practices

      This short article is designed to help you better understand how to get the most out of your data in both XMAP and QGIS by applying simple but effective 'best practice' principles to your PostGIS database. The golden rules: Schema names, table names ...

    • Connecting to XMAP Layers externally

      Overview XMAP has the functionality to give access to XMAP-hosted Map Layers through three methods: QGIS QLR file (using WFS-T) WMS WFS These options give you the ability to view and edit XMAP-hosted Map Layers in a variety of desktop GIS software. ...