Link to home
Start Free TrialLog in
Avatar of sknight
sknight

asked on

Downloading and importing PostgreSQL to Access via the Web

I have a client that needs to download data from a remote PostgreSQL database, and import that data into the current database (which is access), is there any particular script in existance that can make this process smooth?  The data is not available remotely for access, so it must be downloaded first, then imported (preferably through a script online) into the new database.

Any help / links would be greatly appreciated.
ASKER CERTIFIED SOLUTION
Avatar of F. Dominicus
F. Dominicus
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
pgadmin3 does that via visual interface.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sknight
sknight

ASKER

I have no experience using PostgreSQL, but I am reading up on it now.  In what format can I "download" the remote data, and once I have that data, what can I use to remotely upload (or add) that data to an existing database?
You don't need to download or upload the data. Once you install the ODBC drivers for PostgreSQL (http://pgfoundry.org/projects/psqlodbc/) you can link tables in the SQL server directly into your Access DB and use them there as you would use native tables.

In your access go to File-Get External Data - Link tables, in the dialog select "ODBC Databases()" in the dropdown that prompts you for "Files of type:" (it has .mdb by default), then follow on to create a DSN, etc. as usual. When configuring, don't forget to enable "with OIDs" and "simulate index" options in pgSQL driver options so Windows ODBC would work properly when (and IF) you try to update the data.

Once you are done with linking, Postgres tables (and/or views) will be available to you in Access just like your local tables.
Avatar of sknight

ASKER

Having never done any sort of cross-database imports, I'm not sure how to go about this.  I appreciate the help.

The data sample they are sending looks something like this:

--
-- PostgreSQL database dump
--


--
-- Data for Name: address; Type: TABLE DATA; Schema: sample; Owner: norman
--


--
-- Data for Name: br_acct; Type: TABLE DATA; Schema: sample; Owner: norman
--

INSERT INTO br_acct (br_acctid, broker_id, acct_execid, lastname, firstname, middleinit, suffix, salutation, address_id, notes) VALUES (25, 1, '013-164', 'Marvin', 'Lee', ' ', ' ', NULL, NULL, NULL);
INSERT INTO br_acct (br_acctid, broker_id, acct_execid, lastname, firstname, middleinit, suffix, salutation, address_id, notes) VALUES (213, 1, '013-559', 'Johnson', 'Sam', ' ', ' ', NULL, NULL, NULL);


--
-- Data for Name: broker; Type: TABLE DATA; Schema: sample; Owner: norman
--

INSERT INTO broker (broker_id, broker_abbrev, broker_name, commrpt_freq, upfront, mgmtfee_comm, address_id, default_feerate, market_comm, market_trail_comm, tax_id) VALUES (1, 'SAM', 'Sample Broker Guy', 'M', 'Y', 'N', 1, 250, 1, 1, '00-0000000');


--
-- Data for Name: capacct; Type: TABLE DATA; Schema: sample; Owner: norman
--

INSERT INTO capacct (capacct_id, pid, fund_id, act_date, activity, inception, active, mgmt_fee, incent_fee, flat, assigned, accredited, slp, notes, assign_id, redeem_date, created_date, created_by, edit_date, edited_by, link_capacct_id) VALUES (8519, 3131, 6, '2004-10-01', 20000.0000000000000000, 'N', 'Y', 0.0200000000000000, 0.2000000000000000, 'Y', NULL, 'Y', 'N', NULL, NULL, NULL, '2004-09-29 15:12:38', 'gail', '2004-09-29 15:12:38', 'gail', 0);
INSERT INTO capacct (capacct_id, pid, fund_id, act_date, activity, inception, active, mgmt_fee, incent_fee, flat, assigned, accredited, slp, notes, assign_id, redeem_date, created_date, created_by, edit_date, edited_by, link_capacct_id) VALUES (7348, 3131, 6, '2003-02-01', 35000.0000000000000000, 'N', 'Y', 0.0450000000000000, 0.2000000000000000, 'N', NULL, 'Y', 'N', NULL, NULL, NULL, '2003-02-10 13:17:29', 'gail', '2003-02-10 13:17:29', 'gail', 0);
INSERT INTO capacct (capacct_id, pid, fund_id, act_date, activity, inception, active, mgmt_fee, incent_fee, flat, assigned, accredited, slp, notes, assign_id, redeem_date, created_date, created_by, edit_date, edited_by, link_capacct_id) VALUES (5345, 3131, 6, '2000-08-01', 10000.0000000000000000, 'N', 'Y',
If you have setup the proper tables (broker,. capacct etc a with the proper fields you probably just can run it and it will work.  It might be a good idea to check the table creation functions also to know what types exactly were used.


As you can see the table headers are alway included and the rest ist simplest SQL. So I can see not trouble.

Regards
Friedrich
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I will leave the following recommendation for this question in the Cleanup topic area:

split between fridom http:#16425939 and earthman2 http:#16431706

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

gheist
EE Cleanup Volunteer