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.
LVL 1
sknightAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fridomCEO/ProgrammerCommented:
You can dump it with pg_dump such that inserts are used,  or you can save it as some csv file. However I would expect some tweaking nevertheless. However in the end you do have some text file and you'll be able to modify it with anything capable of changing string in a file ;-)


Regards
Friedrich
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gheistCommented:
pgadmin3 does that via visual interface.
0
earth man2Commented:
You could use a PC client local to the server to import the data into a table using ODBC driver.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

sknightAuthor Commented:
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?
0
m1tk4Commented:
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.
0
sknightAuthor Commented:
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',
0
fridomCEO/ProgrammerCommented:
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
0
gheistCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.