Transfer datas from Access to PostgreSQL

Question

I'm trying to insert some data from Access to PostgreSQL database, but it's not a simple query, because it's a nested query.

For example :

rst.Open "INSERT INTO car(id, model, color, type) SELECT (id, model, color, type) FROM t_car"

Where rst is my recordset, and t_car is my Access table.

I know it should look like to a Postgresql request and t_car has nothing to do here, but I don't know how can I specify to this request :

go take informations from my Access table and insert these to my Postgres.

So I'm little bit lost with it, does someone can help me?

• Evan Carroll: You should upload a copy of your MDB if at all possible.

Answer 1

Install the PosgresQL ODBC driver and then use that to register Postgres tables in Access. Issuing the SQL you have given in Access will then transfer data from Access to PosgresQL.

• user120533: Actually I've already established the connection between both databases, I can insert a variable if I set it a string for example but not the result of another request : Dim v_color As String v_color = "brown" rst.Open "INSERT INTO animal(color) SELECT "& v_color &"", cnn (cnn is my connection) That works like a charm, but not when it's the result of another request

Answer 2

If you have established the connection between your Access database and PostgreSQL via ODBC, you can just create a VBA module (I called mine test_remote_insert).

I've got two tables:

On PostgreSQL side:

CREATE TABLE cars_remote
(
 car_id serial PRIMARY KEY,
 make text,
 model text
) ;

... and an equivalent one in Access, that I call cars_local.

The module has just one very simple Sub:

Option Compare Database

Sub insert_from_local_to_remote()
 CurrentDb.Execute _
 "INSERT INTO cars_remote(make, model) " & _
 "SELECT make, model " & _
 "FROM cars_local"
End Sub

I can execute it, and it copies the contents of the cars_local into cars_remote without a glitch.

I am not using any recordset, but just the CurrentDb method that returns the current database, which I just make Execute the given SQL.

---

For the record:

• I'm using "PostgreSQL 9.6.2 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit" on macOS 10.12.3.
• Access 2016 MSO 16.0.7766.7080 32 bits on Windows 10, 32 bit, on a Parallels VM on the same Mac.
• The ODBC driver is version 9.03.04 PostgreSQL Unicode.
• The PostgreSQL database encoding is UTF-8.

Answer 3

The PostgreSQL Wiki cover this. I would read it entirely before picking a solution. However I highly suggest trying the Foreign Data Wrapper for Access MDB method first. Essentially, the GDAL project tackles this because an ESRI Shapefile is an Access MDB under the hood. It's the self-contained database format ESRI used that the GIS industry standardized on (by convention). So they created the ability to read MDBs so people could import the ESRI files. Under the hood, GDAL uses Jackcess. This is a fringe benefit of those projects.

You can get a brief overview of FDW in the docs.

You can see a tutorial of it here. You can likely skip in the tutorial to where it says CREATE SERVER. You may also want to check out 9.5+'s IMPORT FOREIGN SCHEMA.

Anyway, even if you don't use the advanced options PostGIS makes this very simple.

SELECT ogr_fdw_sql_table('C:/fdw_data/northwind.mdb','Orders');