CODEWITHSUNDEEP
sqlpostgresqlms-access
Scriven
Scriven

Reputation: 438

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

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.

Upvotes: 0

Views: 141

Answers (1)

Scriven
Scriven

Reputation: 438

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

The simple step by step

  1. Create the DSN in ODBC Data Source Administrator (ensure that its a system DSN not a user)
  2. Open Access database.
  3. Right Click on table you want to export.
  4. In the list of options that come up select Export>ODBC Database
  5. 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.

Upvotes: 1

Related Questions