0

I'm trying to set a connection between a PostgreSql 12 64bit on a Windows 10 (64 bit) with a MS Access 2013 (64 bit) on a windows 8.1 64bit using some of the fdw's. At this regard I: - Set a System DSN with MSAccess 64bit driver (msdata) Unfortunately MS removed the test option for access database connection but it seams to work. - Using ogr_fwd I was able to set the Foreign Data Wrapper but when setting the Foreign server it refused the following options: [dsn:msdata] (dsn not valid option), [datasource:'Z:/database.accdb', format:'MDB'] (Unable to find MDB format idem using 'OCDB' as format), [datasource: 'msdata'] or [datasource:'OCDB:msdata] (unable to connect to data source xxxx) Finally y gave up using ogr_fwd but I was able to connect to a MySQL database using it with a MySql
64 bit driver.

I tried ocdb_fdw with some more luck. Was able to create the Foreign Server; the User Mappings and two foreign tables but when I tried to run a simple query on a three records table it took for ever without showing a result ´- I tried to set the option "use_remote_estimate" on the foreign tables with the same results. I set it up as follows:

CREATE FOREIGN DATA WRAPPER odbc_wrapper
    VALIDATOR public.odbc_fdw_validator
    HANDLER public.odbc_fwd_handler;

CREATE FOREIGN SERVER odbc_server
    FOREIGN DATA WRAPPER odbc_wrapper
    OPTIONS(dsn 'msdata');

CREATE USER MAPPING FOR PUBLIC (Ms Access database not password protected)
    SERVER odbc_server;

CREATE FOREIGN TABLE public.sex
    id integer NOT NULL,
    sex character varying(20)[] NOT NULL
    SERVER obdc_server
    OPTIONS (use_remote_estimate 'True'); -- run with and without this option --

As mentioned above, the odbc_fdw allows to set it up, but I was unable of getting any return data from it. Linking a table from Ms Access using the Ms Access MSConnector works just fine but unfortunately it only allows to link on existing PostgreSQL tables which it's not what I need. I think that somehow this is related to the 32/64 bit mess of the Ms Access driver, but I couldn't find a workaround.
Any help would be most appreciated.

EDIT: After struggling for some time, i found out a problem with my MS Driver not related to odbc_fdw or to PostgreSQL but to windows itself. Once I fixed it, the driver ( and the DSN system data source) works just fine when tested using pyodbc. This solve the issue of freezing (or running for a long time) but when running a query on the foreign table I got another issue that was an "ERROR: Connecting to driver SQL state: 58000 - Which stands for error outside PostgreSQL. I tried several options at the Foreign Table such as odbc_DATABASE databasename; schema databasename; odbc_table tablename with the same result. I still don't have idea why ogr_fdw doesn't recognize the MsAccess 64bit driver.

Improve this question
3
  • You've read Querying MS Access and other ODBC data sources with OGR_FDW? Especially "use UNC paths" and "your PostgreSQL server service account can access that path". Of course migration of Ms Access backend to whatever RDBMS (PostgreSQL, SQL Server, MySQL, ...) should be considered. Commented Aug 20, 2020 at 19:10
  • Yes, I have, and to tell you the truth I didn't understand the issue since I can't figure out how to place the pg service in the 'path'. Actually I tried both with UNC path and with a mapped drive without any luck. Besides, I couldn't get ogr_fdw to recognize the occdb file nor the 'MDB' format so I guess it was an issue related to the version's bitness, or to not upgrading the driver so I abandoned and started looking into ocdb_fdw. Commented Aug 20, 2020 at 23:42
  • Not the service, the user account running that service needs to have read/write access to the share (folder of accdb). If not using active directory, that can be accomplished by creating the same account (username/password) on the share machine, granting rw access to folder of accdb. Never rely on mapped drives as they may change, always use UNC-Paths! Commented Aug 21, 2020 at 0:07
Related questions
8
Using MS Access & ODBC to connect to a remote PostgreSQL
0
Database Connection Problem
2
Connection error comes when connect with ms access database
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.