What is the optimal way to show custom data from MSAccess DB at Wordpress site?

Question

I need an advice from skilled Wordpress developers. My organization has internal MS Access database which contains numerous tables, reports and input forms. The structure of DB is not too complicated (persons information, events, third parties info and different relations between them). We woild like to show some portion of this info at our Wordpress site, which currently has only news section.

Because information in our DB updated each day, also we would like to make simple synchronization between MS Access DB and Wordpress (MySQL DB). Now I try to find the best way how to connect MS Access and Wordpress.

At present I see only these ways how to do this:

1. Make export requests and save to XML files.
2. Import to MySQL DB of Wordpress.
3. Show content on Wordpress site using Custom fields feature (or develop own plugin).

-OR-

3. Build own informational system on some very light PHP engine (for example CodeIgniter) on same domain as Wordpress site, which will actually show imported content.

These variants needs manual transfer info between DB each day. And I don't know possibilities of Wordpress to show custom data from DB. Would you suggest me what ways will you prefer to use in my case?

P.S. MS Access used is ver 2007+ (file .accdb). Name of fields, db's and content is on Russian language. In future we planning to add 2 new languages (English, Ukrainian). MS access DB also contains persons photos included.

---Updated info---

I was able to make semi-atomatic import/export operations using following technique:

• Javascript library ACCESSdb (little bit modified for new DB format)
• Internet Explorer 11 (for running ADODB ActiveX)
• small VBS script for extracting attached files from MSAccess tables.
• latest jQuery
• Wordpress plugins for custom data (Advanced Custom Fields, Custom Post Type UI)
• Wordpress Rest-API enabled (with plugins JSON Basic Authentication, ACF to REST API)

At first I've constructed data scheme at Wordpress site using custom post and custom fields technique. Then I locally run JS queries to MSAccess DB, received info I sending via jQuery to WP Rest-API endpoints. Whole transfer operation can be made with in 1 click. But I can't upload files automatically via JS due to security limitations. This can be done in +1 click per file.

Answer 1

Your question is too broad. It consist of two parts: 1. export from Access and 2. import to Wordpress. Since i'm not familiar with Wordpress I can only give you advice about 1 part. At least google shows that there is some plugins that able to import from CSV like this one: https://ru.wordpress.org/plugins/wp-ultimate-csv-importer/

You can create a scheduled task that runs Access that runs macro that runs VBA function as described here: Running Microsoft Access as a Scheduled Task

In that VBA function you can use ADODB.Stream object to create a UTF-8 CSV file with you data and make upload to FTP of your site.

OR

Personally i use a python script to do something similar. I prefer this way because it is more straitforward and reliable. There is my code. Notice, that i have two FTP servers: one of them is for testing only.

# -*- coding: utf-8 -*-
# 2018-10-31
# 2018-11-28

import os
import csv
from time import sleep
from ftplib import FTP_TLS
from datetime import datetime as dt
import msaccess

FTP_REAL = {'FTP_SERVER':r'your.site.com',
            'FTP_USER':r'username',
            'FTP_PW':r'Pa$$word'
            }

FTP_WIP = {'FTP_SERVER':r'192.168.0.1',
            'FTP_USER':r'just_test',
            'FTP_PW':r'just_test'
            }

def ftp_upload(fullpath:str, ftp_folder:str, real:bool):
    ''' Upload file to FTP '''
    try:
        if real:
            ftp_set = FTP_REAL
        else:
            ftp_set = FTP_WIP
        with FTP_TLS(ftp_set['FTP_SERVER']) as ftp:
            ftp.login(user=ftp_set['FTP_USER'], passwd=ftp_set['FTP_PW'])
            ftp.prot_p()
            # Passive mode off otherwise there will be problem
            # with another upload attempt
            # my site doesn't allow active mode :(
            ftp.set_pasv(ftp_set['FTP_SERVER'].find('selcdn') > 0)
            ftp.cwd(ftp_folder)
            i = 0
            while i < 3:
                sleep(i * 5)
                i += 1
                try:
                    with open(fullpath, 'br') as f:
                        ftp.storbinary(cmd='STOR ' + os.path.basename(fullpath),
                                        fp=f)
                except OSError as e:
                    if e.errno != 0:
                        print(f'ftp.storbinary error:\n\t{repr(e)}')
                except Exception as e:
                    print(f'ftp.storbinary exception:\n\t{repr(e)}')
                filename = os.path.basename(fullpath)
                # Check if uploaded file size matches local file:
                # IDK why but single ftp.size command sometimes returns None,
                # run this first:
                ftp.size(filename)
                #input(f'overwrite it: {filename}')
                ftp_size = ftp.size(os.path.basename(fullpath))
                # import pdb; pdb.set_trace()
                if ftp_size != None:
                    if ftp_size == os.stat(fullpath).st_size:
                        print(f'File \'{filename}\' successfully uploaded')
                        break
                    else:
                        print('Transfer failed')
                        # input('Press enter for another try...')
    except OSError as e:
        if e.errno != 0:
            return False, repr(e)
    except Exception as e:
        return False, repr(e)
    return True, None

def make_file(content:str):
    ''' Make CSV file in temp directory and return True and fullpath '''
    fullpath = os.environ['tmp'] + f'\\{dt.now():%Y%m%d%H%M}.csv'
    try:
        with open(fullpath, 'wt', newline='', encoding='utf-8') as f:
            try:
                w = csv.writer(f, delimiter=';')
                w.writerows(content)
            except Exception as e:
                return False, f'csv.writer fail:\n{repr(e)}' 
    except Exception as e:
        return False, repr(e)
    return True, fullpath

def query_upload(sql:str, real:bool, ftp_folder:str, no_del:bool=False):
    ''' Run query and upload to FTP '''
    print(f'Real DB: {real}')
    status, data = msaccess.run_query(sql, real=real, headers=False)
    rec_num = len(data)
    if not status:
        print(f'run_query error:\n\t{data}')
        return False, data
    status, data = make_file(data)
    if not status:
        print(f'make_file error:\n\t{data}')
        return False, data
    fi = data
    status, data = ftp_upload(fi, ftp_folder, real)
    if not status:
        print(f'ftp_upload error:\n\t{data}')
        return False, data
    print(f'Done: {rec_num} records')
    if no_del: input('\n\nPress Enter to exit and delete file')
    os.remove(fi)
    return True, rec_num