How to get/create SQL statement from access table to put in Postgres DB

Question

I currently have an access database. Basically what I'm trying to do is take all of the tables from the access DB and put them in a postgres DB.

Now I could write out an sql statement for each table, or manually create each table in postgres. But I was wondering if there is a program, or an addon for access that will help speed up the process.

Below is the kind of sql statement I'm looking for.

CREATE TABLE public.Dept
(
  Deptid integer NOT NULL,
  DeptName text,
  SupDeptid integer,
  CONSTRAINT Dept_pkey PRIMARY KEY (Deptid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.Dept
  OWNER TO postgres;

I've unfortunately had no luck finding anything to do something like this on google or stackoverflow.

OS x64 Windows 7
Postgres x64
Office (access) x32

@Cetin Basoz - Thanks for the link, some helpful information. I suffer from the "big gotcha" he was talking about with a x64 operating system x32 office, and x64 postgres, which made the whole ordeal kind of a hassle. — Scriven
– Scriven, Commented Oct 15, 2015 at 18:42

Scriven · Accepted Answer · 2015-10-15 18:28:04Z

I have found a quick and easy way to do it.

The simple step by step

Create the DSN in ODBC Data Source Administrator (ensure that its a system DSN not a user)
Open Access database.
Right Click on table you want to export.
In the list of options that come up select Export>ODBC Database
Select Machine Data Source and Select the DSN you created.

If you have x32 Ms-Access and x64 Operating system, access will show only DSN that were entered in odbcad32 (x32 Data Sources) which is not where the control panel takes you to by default. To add the x32 DSN you must go into C:\windows\SysWOW64\odbcad32.exe and recreate it there.

Again make sure its a system DSN or you will get an error from access when trying to export the file.

To answer the Title of the question more specifically you can now open PgAdmin go in to the database you exported the tables to, and postgres will show you the SQL statement to create the table.

Collectives™ on Stack Overflow

How to get/create SQL statement from access table to put in Postgres DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related