Client applications

How to work with Access data in some example applications and programming languages:

Oracle
LibreOffice
Go
Node.js
Perl
PHP
Python
R

Oracle

Install the Easysoft ODBC-Access Driver on same computer as Oracle.
Configure an ODBC data source.
Follow the instructions for your Oracle platform.

Connecting Access to Oracle on Linux and UNIX

Create a DG4ODBC init file on your Oracle machine. To do this, change to the $ORACLE_HOME\hs\admin directory. Create a copy of the file initdg4odbc.ora. Name the new file initAccess.ora.

In these instructions, replace $ORACLE_HOME with the location of your Oracle HOME directory. For example, /u01/app/oracle/product/21c/dbhome_1.
Ensure these parameters and values are present in your init file:
```
HS_FDS_CONNECT_INFO = "Access"
```
Replace Access with the name of your Easysoft ODBC-Access Driver data source.
Comment out the line that enables DG4ODBC tracing. For example:
```
#HS_FDS_TRACE_LEVEL = <trace_level>
```

Add an entry to $ORACLE_HOME/network/admin/listener.ora that creates a SID_NAME for DG4ODBC. For example:

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
     (SID_NAME=Access)
     (ORACLE_HOME=$ORACLE_HOME)
     (PROGRAM=dg4odbc)
     (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:
            /usr/local/easysoft/lib)
   )
 )

Replace oracle_home_directory with the value of $ORACLE_HOME. For example, /u01/app/oracle/product/21c/dbhome_1.

Add a DG4ODBC entry to $ORACLE_HOME/network/admin/tnsnames.ora that specifies the SID_NAME created in the previous step. For example:
```
Access =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_host)(PORT = 1521))
    (CONNECT_DATA =
      (SID = Access)
    )
    (HS = OK)
  )
```
Replace oracle_host with the host name of your Oracle machine.

Start (or restart) the Oracle Listener:

cd $ORACLE_HOME/bin
./lsnrctl stop
./lsnrctl start

Connect to your Oracle database in SQL*Plus.
In SQL*Plus, create a database link for Access. For example:
```
CREATE PUBLIC DATABASE LINK AccessLink
    CONNECT TO "dbuser" IDENTIFIED BY "dbpassword"
    USING 'Access';
```
Replace dbuser and dbpassword with your backend user name and password, if applicable.

Try querying and updating your Access data. For example:

SELECT "Surname" FROM "Customers"@AccessLink;

DECLARE
  num_rows integer;
BEGIN
num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@AccessLink
('INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (''Devlin'', ''Michaels'', ''Kingston'', ''2015558966'', ''PowerGroup'')');
END;
/

DECLARE
  num_rows integer;
BEGIN
num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@AccessLink
('UPDATE "Customers" SET "Surname" = ''Jones'' WHERE "CompanyName" = ''PowerGroup''');
END;
/

DECLARE
  num_rows integer;
BEGIN
num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@AccessLink
('DELETE from "Customers" WHERE CompanyName = ''PowerGroup''');
END;
/

Notes

If you have problems connecting to Access from Oracle, enable DG4ODBC tracing and check the trace files written to the $ORACLE_HOME/hs/trace directory. To enable DG4ODBC tracing, add the line HS_FDS_TRACE_LEVEL = DEBUG to initAccess.ora and then start or restart the Oracle listener. If the trace directory does not exist, create it.

LibreOffice

Install the Easysoft ODBC-Access Driver on same computer as LibreOffice.
Configure an ODBC data source.
Choose File > New > Database.
Choose Connect to an existing database.
Choose ODBC in the list, and then choose Next.
Choose Browse, double-click your data source, and then choose Next.
If your database requires a database user name, enter it in the User name box. If this user needs to supply a password choose the Password required check box.
Choose Finish.
Save the database when prompted.

The database opens in a new Base window. From here you can access your data.
In the left pane of the database window, choose the Tables icon to display a hierarchy of tables. Enter the database password if prompted, and then choose OK.
To retrieve the data in a table, in the Tables pane, double-click a table.
Choose the Queries icon to create a query.

Use any of the methods listed in the Tasks pane to create a query.

Go

Install the Easysoft ODBC-Access Driver on same computer as Go.
Configure an ODBC data source.

Install the odbc package for Go:

go mod init test
go get github.com/alexbrainman/odbc

Create and then use Go to run this script, which retrieves some Access data:

package main

import (
	_ "github.com/alexbrainman/odbc"
	"database/sql"
        "log"
)

func main() {
        // Replace the DSN value with the name of your ODBC data source.
        db, err := sql.Open("odbc",
                "DSN=Access")
        if err != nil {
                log.Fatal(err)
        }

        var (
                name string
        )

        rows, err := db.Query("SELECT Surname FROM Customers")
        if err != nil {
                log.Fatal(err)
        }
        defer rows.Close()
        for rows.Next() {
                err := rows.Scan(&name)
                if err != nil {
                        log.Fatal(err)
                }
                log.Println(name)
        }
        err = rows.Err()
        if err != nil {
                log.Fatal(err)
        }

        defer db.Close()
}

Node.js

Install the Easysoft ODBC-Access Driver on same computer as Node.js.
Configure an ODBC data source.
Install the odbc module for Node.js:
```
npm install odbc
```

Create and then use Node.js to run this script, which retrieves some Access data:

const odbc = require('odbc');
// Replace Access with the name of your Easysoft ODBC-Access Driver
// data source.
const connection = odbc.connect('DSN=Access', (error, connection) => {
    connection.query('SELECT Surname FROM Customers', (error, result) => {
        if (error) { console.error(error) }
        console.log(result);
    });
});

This script retrieves the tables and views in your Easysoft ODBC-Access Driver data source:

const odbc = require('odbc');
const connection = odbc.connect('DSN=Access', (error, connection) => {
    connection.tables(null, null, null, null, (error, result) => {
        if (error) { return; }
        const util = require('util');
        console.log(util.inspect(result, {maxArrayLength: null, depth:null}))
    });
});

This script retrieves the names of the columns in these tables and views:

const odbc = require('odbc');
const connection = odbc.connect('DSN=Access', (error, connection) => {
    connection.columns(null, null, null, null, (error, result) => {
        if (error) { return; }
        const util = require('util');
        console.log(util.inspect(result, {maxArrayLength: null, depth:null}))
    });
});

These scripts insert, update, and then delete some Access data:

const odbc = require("odbc");
const connection = odbc.connect("DSN=Access", (error, connection) => {
    connection.query("INSERT INTO
    Customers (
        Surname,
        GivenName,
        City,
        Phone,
        CompanyName
    )
VALUES
    (
        'Devlin',
        'Michaels',
        'Kingston',
        '2015558966',
        'PowerGroup'
    )", (error, result) => {
        if (error) { console.error(error) }
        console.log(result);
    });
});

const odbc = require("odbc");
const connection = odbc.connect("DSN=Access", (error, connection) => {
    connection.query("UPDATE Customers SET Surname = 'Jones' WHERE CompanyName = 'PowerGroup'", (error, result) => {
        if (error) { console.error(error) }
        console.log(result);
    });
});

const odbc = require("odbc");
const connection = odbc.connect("DSN=Access", (error, connection) => {
    connection.query("DELETE FROM Customers WHERE CompanyName = 'PowerGroup'", (error, result) => {
        if (error) { console.error(error) }
        console.log(result);
    });
});

Perl

Install the Easysoft ODBC-Access Driver on same computer as Perl.
Configure an ODBC data source.
Check whether your Perl distribution supports ODBC:
```
perl -e 'use DBD::ODBC;'
```
Do one of the following:
- If you get no output, your Perl distribution supports ODBC. Skip to the next step.
- If you get:
  Can't locate DBD/ODBC.pm
  you need to install DBD::ODBC before you can use the Easysoft ODBC-Access Driver to connect to Access.

Create and then use Perl to run this script, which retrieves some Access data:

use strict;
use DBI;
# Replace Access with the name of your Easysoft ODBC-Access Driver data source.
my $dbh = DBI-> connect('dbi:ODBC:Access');

my $sql = "SELECT Surname FROM Customers";

my $sth = $dbh->prepare($sql)
    or die "Can't prepare statement: $DBI::errstr";

$sth->execute();

my($Col);

# Fetch and display the result set values.
while(($Col) = $sth->fetchrow()){
   print("$Col\n");
}

$dbh->disconnect if ($dbh);

This script retrieves the tables and views in your Easysoft ODBC-Access Driver data source:

use strict;
use DBI;
my $dbh = DBI-> connect('dbi:ODBC:Access');

my $sth = $dbh->table_info()
    or die "Can't prepare statement: $DBI::errstr";

my @row;

while (@row = $sth->fetchrow_array) {
    print join(", ", @row), "\n";
}

$dbh->disconnect if ($dbh);

This script retrieves the names of the columns in these tables and views:

use strict;
use DBI;
my $dbh = DBI-> connect('dbi:ODBC:Access');

my $sth = $dbh->column_info('','','','')
    or die "Can't prepare statement: $DBI::errstr";

my @row;
while (@row = $sth->fetchrow_array) {
    print join(", ", @row), "\n";
}

$dbh->disconnect if ($dbh);

These scripts insert, update, and then delete some Access data:

use strict;
use DBI;
my $dbh = DBI-> connect('dbi:ODBC:Access');

my $sth = $dbh->prepare(q/INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (?, ?, ?, ?, ?)/)
    or die "Can't prepare statement: $DBI::errstr";

$sth->execute('Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup');

$dbh->disconnect if ($dbh);

use strict;
use DBI;
my $dbh = DBI-> connect('dbi:ODBC:Access');

my $sth = $dbh->prepare('UPDATE Customers SET Surname = \'Jones\' WHERE CompanyName = ?')
    or die "Can't prepare statement: $DBI::errstr";

$sth->execute('PowerGroup');

$dbh->disconnect if ($dbh);

use strict;
use DBI;
my $dbh = DBI-> connect('dbi:ODBC:Access');

my $sth = $dbh->prepare('DELETE FROM Customers WHERE CompanyName = ?')
    or die "Can't prepare statement: $DBI::errstr";

$sth->execute('PowerGroup');

$dbh->disconnect if ($dbh);

Further information

Perl DBI DBD::ODBC tutorial: Drivers, data sources, and connection

PHP

Install the Easysoft ODBC-Access Driver on same computer as PHP.
Configure an ODBC data source.
Check whether your PHP distribution supports ODBC. In php.ini, make sure there is no comment character (;) before the extension_dir and extension=odbc settings (;extension_dir=directory becomes extension_dir=directory and ;extension=odbc becomes extension=odbc).

Create and then use PHP to run this script, which retrieves some Access data:

<?php
  // Replace Access with the name of your Easysoft ODBC-Access Driver data source.
  //  If your database requires a user name and password, supply them in the odbc_connect_call.
  $con = odbc_connect("Access", "", "");
  $stmt = odbc_exec($con, "SELECT * FROM Customers");
  // You may need to change the capitalisation of Surname to all upper case or all lower case.
  while ($row = odbc_fetch_array($stmt)) {
      echo "Surname = ", $row["Surname"], "\n";
  }
  odbc_close($con);
?>

This script retrieves the tables and views in your Easysoft ODBC-Access Driver data source:

<?php
$con = odbc_connect("Access", "", "");
$tables = odbc_tables($con);
while (($row = odbc_fetch_array($tables))) {
    print_r($row);
}
odbc_close($con);
?>

This script retrieves the names of the columns in these tables and views:

<?php
$con = odbc_connect("Access", "", "");
$columns = odbc_columns($con);
while (($row = odbc_fetch_array($columns))) {
    print_r($row);
}
odbc_close($con);
?>

These scripts insert, update, and then delete some Access data:

<?php
  $cnx = odbc_connect("Access", "", "");
  $stmt = odbc_prepare($cnx, "INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (?, ?, ?, ?, ?)");
  $success = odbc_execute($stmt, array('Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup'));
  odbc_close($cnx);
?>

<?php
  $cnx = odbc_connect("Access", "", "");
  $stmt = odbc_prepare($cnx, "UPDATE Customers SET Surname = 'Jones' WHERE CompanyName = ?");
  $success = odbc_execute($stmt, array('PowerGroup'));
  odbc_close($cnx);
?>

<?php
  $cnx = odbc_connect("Access", "", "");
  $stmt = odbc_prepare($cnx, "DELETE FROM Customers WHERE CompanyName = ?");
  $success = odbc_execute($stmt, array('PowerGroup'));
  odbc_close($cnx);
?>

Further information

Easysoft PHP tutorials and code samples

Python

Install the Easysoft ODBC-Access Driver on same computer as Python.
Configure an ODBC data source.
Check whether your Python distribution supports ODBC.
```
pip list
```
If you don’t have pip installed:
```
curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py
python get-pip.py
```
Do one of the following:
- If the output contains pyodbc, your Python distribution supports ODBC. Skip to the next step.
- If the output does not contain pyodbc, use pip to install this module:
  pip install pyodbc

Create and then use Python to run this script, which retrieves some Access data:

import pyodbc

# Replace Access with the name of your Easysoft ODBC-Access Driver data source.
cnxn = pyodbc.connect("DSN=Access")
cursor = cnxn.cursor()
sql = "SELECT Surname FROM Customers"
cursor.execute(sql)
rows = cursor.fetchall()
# You may need to change the capitalisation of Surname to all upper case or all lower case.
for row in rows:
     print(row.Surname)
exit()

This script retrieves the tables and views in your Easysoft ODBC-Access Driver data source:

import pyodbc

# Replace Access with the name of your Easysoft ODBC-Access Driver data source.
cnxn = pyodbc.connect("DSN=Access")
cursor = cnxn.cursor()
cursor.tables()
rows = cursor.fetchall()
for row in rows:
     print(row.table_name)
exit()

This script retrieves the names of the columns in these tables and views:

import pyodbc

# Replace Access with the name of your Easysoft ODBC-Access Driver data source.
cnxn = pyodbc.connect("DSN=Access")
cursor = cnxn.cursor()
cursor.columns()
rows = cursor.fetchall()
for row in rows:
     print(row.table_name, row.column_name)
exit()

These scripts insert, update, and then delete some Access data:

import pyodbc

cnxn = pyodbc.connect("DSN=Access")
cursor = cnxn.cursor()
sql = "INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (?, ?, ?, ?, ?)"
cursor.execute(sql, 'Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup')
cursor.commit()
exit()

import pyodbc

cnxn = pyodbc.connect("DSN=Access")
cursor = cnxn.cursor()
sql = "UPDATE Customers SET Surname = 'Jones' WHERE CompanyName = ?"
cursor.execute(sql, 'PowerGroup')
cursor.commit()
exit()

import pyodbc

cnxn = pyodbc.connect("DSN=Access")
cursor = cnxn.cursor()
sql = "DELETE FROM Customers WHERE CompanyName = ?"
cursor.execute(sql, 'PowerGroup')
cursor.commit()
exit()

Further information

Easysoft Python tutorials and code samples

R

Install the Easysoft ODBC-Access Driver on same computer as R.
Configure an ODBC data source.
In R Console, check whether your R distribution supports ODBC.
```
library("RODBC")
```
Do one of the following:
- If you get no output, you have the ODBC library for R. Skip to the next step.
- If you get an "there is no package" error, install the ODBC library for R:
  install.packages("RODBC")

Create and then use R to run this script, which retrieves some Access data:

library("RODBC")
# Replace Access with the name of your Easysoft ODBC-Access Driver data source.
ch <- odbcConnect("Access")
sqlQuery(ch, paste("SELECT Surname FROM Customers"))
odbcClose(ch)
quit()

This script retrieves the tables and views in your Easysoft ODBC-Access Driver data source:

library("RODBC")
# Replace Access with the name of your Easysoft ODBC-Access Driver data source.
ch <- odbcConnect("Access")
sqlTables(ch)
odbcClose(ch)
quit()

This script retrieves the names of the columns in the specified table or view:

library("RODBC")
# Replace Access with the name of your Easysoft ODBC-Access Driver data source.
ch <- odbcConnect("Access")
# You may need to change the capitalisation of Customers to all upper case or all lower case.
sqlColumns(ch, sqtable="Customers")
odbcClose(ch)
quit()

These scripts insert, update, and then delete some Access data:

library("RODBC")
ch <- odbcConnect("Access")
sqlQuery(ch, paste("INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES ('Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup')"))
odbcClose(ch)
quit()

library("RODBC")
ch <- odbcConnect("Access")
sqlQuery(ch, paste("UPDATE Customers SET Surname = 'Jones' WHERE CompanyName = 'PowerGroup'"))
odbcClose(ch)
quit()

library("RODBC")
ch <- odbcConnect("Access")
sqlQuery(ch, paste("DELETE FROM Customers WHERE CompanyName = 'PowerGroup'"))
odbcClose(ch)
quit()