Thread: @#$%^& Access and PostgreSQL
I have spent two days now trying to get MS Access to play with PostgreSQL.
Searched the archives of this list and Google'd myself crazy but either
I'm the only one having problems with this :-) or I haven't figured out
the right keywords to search.
Short story. Pg 7.2 running on a Linux box. MS Access 2000 on WinXP Pro.
I can query and fiddle with the data in Access but I haven't figured out
how to get the changes back into Pg.
There is nothing worth a darn in the Access help and working my way
through the menus has got me no success.
Quick; please someone hit me with a cluestick! Or if it's too far to
reach is there a tutorial on using MS Access and Pg together?
Oh yeah, I looked at pgAccess but the site is down and the registrar
indicates it is being deleted. PgAdmin II (since I'm using Pg 7.2) is ...
OK, but too slow for editing by the folks I work with.
TIA,
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
Roderick A. Anderson wrote: > Short story. Pg 7.2 running on a Linux box. MS Access 2000 on WinXP Pro. > I can query and fiddle with the data in Access but I haven't figured out > how to get the changes back into Pg. > > Quick; please someone hit me with a cluestick! Or if it's too far to > reach is there a tutorial on using MS Access and Pg together? It's been a long time since I've messed with MSAccess/pgsql, but IIRC, you need to be sure your table has a primary key defined to get a writeable recordset. Also, make sure the linked table on the Access side reflects that. HTH, Joe
can't get changes back into pg. That is not a lot to go on. We don't know if your are using a simple action query, a bound form, or VBA code. You might supply some detail about what you've done and what failed. Remember - with MS Access and PostgreSQL you are in a client-server situation. One in which PostgreSQL doesn't really do attached recordsets. Think read is one step, write is another step, being linked is an illusion. With that in mind, you may want to try a simple append query. (You might want to do a make table query to get the schema right and use that table to populate what you want to append). OBDC will simulate an attached recordset (the illusion bound table part) if your source has a primary key, and better yet OIDs. This will help in the case of bound forms. On the PostgreSQL server side you may want to see if postgreSQL is logging on startup (the pg_ctl -l filename) option. Look for errors. On the ODBC side you may want to turn on logging and trace the communication flow (the logs get pretty big pretty quick). Good Luck Roderick A. Anderson wrote: >I have spent two days now trying to get MS Access to play with PostgreSQL. >Searched the archives of this list and Google'd myself crazy but either >I'm the only one having problems with this :-) or I haven't figured out >the right keywords to search. > >Short story. Pg 7.2 running on a Linux box. MS Access 2000 on WinXP Pro. >I can query and fiddle with the data in Access but I haven't figured out >how to get the changes back into Pg. > >There is nothing worth a darn in the Access help and working my way >through the menus has got me no success. > >Quick; please someone hit me with a cluestick! Or if it's too far to >reach is there a tutorial on using MS Access and Pg together? > >Oh yeah, I looked at pgAccess but the site is down and the registrar >indicates it is being deleted. PgAdmin II (since I'm using Pg 7.2) is ... >OK, but too slow for editing by the folks I work with. > > >TIA, >Rod > >
Attachment
Hello,
Firstly, beleive me, Access linked to Postgresql through ODBC works really well. The driver works fine, I'm using it
everyday, several clients too.
How do you "query and fiddle with the data in Access"? You have to use a ***linked ODBC table***, and in this case,
updatesare transparent. You simply modify the data, and the updates are generated for you. Is that what you are trying
todo?
Philippe
-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Roderick A. Anderson
Envoyé : mardi, 15. juin 2004 01:50
À : pgsql-odbc@postgresql.org
Objet : [ODBC] @#$%^& Access and PostgreSQL
I have spent two days now trying to get MS Access to play with PostgreSQL.
Searched the archives of this list and Google'd myself crazy but either I'm the only one having problems with this :-)
orI haven't figured out the right keywords to search.
Short story. Pg 7.2 running on a Linux box. MS Access 2000 on WinXP Pro.
I can query and fiddle with the data in Access but I haven't figured out how to get the changes back into Pg.
There is nothing worth a darn in the Access help and working my way through the menus has got me no success.
Quick; please someone hit me with a cluestick! Or if it's too far to reach is there a tutorial on using MS Access and
Pgtogether?
Oh yeah, I looked at pgAccess but the site is down and the registrar indicates it is being deleted. PgAdmin II (since
I'musing Pg 7.2) is ...
OK, but too slow for editing by the folks I work with.
TIA,
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
--- Joe Conway <mail@joeconway.com> wrote: > Roderick A. Anderson wrote: > > Short story. Pg 7.2 running on a Linux box. MS > Access 2000 on WinXP Pro. > > I can query and fiddle with the data in Access but > I haven't figured out > > how to get the changes back into Pg. > > > > > Quick; please someone hit me with a cluestick! Or > if it's too far to > > reach is there a tutorial on using MS Access and > Pg together? > > It's been a long time since I've messed with > MSAccess/pgsql, but IIRC, > you need to be sure your table has a primary key > defined to get a > writeable recordset. Also, make sure the linked > table on the Access side > reflects that. Good points: always refresh (or relink) your linked tables after making changes to tables on the server side; also, I find life becomes much easier with Access when I use a serial primary key on every table as a matter of course. Have you created your tables without oid's? I believe that could cause you a problem, because the ODBC driver expects oid's in some circumstances. In any case, I suggest you set the "row versioning" driver setting to true. The suggestion elsewhere to turn on logging, on both client and server sides, is a good one. > > HTH, > > Joe > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to > majordomo@postgresql.org so that your > message can get through to the mailing list > cleanly > __________________________________ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail
On Tue, 15 Jun 2004, Philippe Lang wrote:
> Hello,
>
> Firstly, beleive me, Access linked to Postgresql through ODBC works
> really well. The driver works fine, I'm using it every day, several
> clients too.
Used to be for me also a year or three ago. This new go-round (I spent
three plus years not having to deal with MS Access) it has been anything
but smooth. The few times before it was a slam dunk.
> How do you "query and fiddle with the data in Access"? You have to use
> a ***linked ODBC table***, and in this case, updates are transparent.
> You simply modify the data, and the updates are generated for you. Is
> that what you are trying to do?
Philippe, this is the term I was missing. 'Linked' ODBC tables. For some
reason when I created the Access portion last week there was no ODBC data
sources for linked tables ... I think. Probably had something else in
mind when I tried using this option. No there wasn't. It's working now.
Thanks to all for suggestions and a couple of clue-sticks.
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
Are you getting any error messages. Are getting the dreaded #DELETED in the cells after to insert. You may need to turn row versioning on in the ODBC settings. Michael Joe Conway wrote: > Roderick A. Anderson wrote: > >> Short story. Pg 7.2 running on a Linux box. MS Access 2000 on WinXP >> Pro. I can query and fiddle with the data in Access but I haven't >> figured out how to get the changes back into Pg. > > >> >> Quick; please someone hit me with a cluestick! Or if it's too far to >> reach is there a tutorial on using MS Access and Pg together? > > > It's been a long time since I've messed with MSAccess/pgsql, but IIRC, > you need to be sure your table has a primary key defined to get a > writeable recordset. Also, make sure the linked table on the Access > side reflects that. > > HTH, > > Joe > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
On Thu, 17 Jun 2004, Michael Calabrese wrote:
> Are you getting any error messages. Are getting the dreaded #DELETED in
> the cells after to insert. You may need to turn row versioning on in
> the ODBC settings.
> Michael
Sorry to all for not updating you.
I sorted it out. It was the linked table issue -- mine were not. I'm
hooked up and editing now. I haven't had a need to insert anything yet as
most of the entry is done via a web form and scripts.
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"