Connecting Access to Postgres

[dakota81]

Was working on a network where they have been running an Access appliction that has grown quite large over the years. It's been running slow, and I was thinking it might make sense to replace the backend .mdb data file with a postgres database where the sql queries are executed on the server, thus reducing network traffic by a lot.

The frontend database files have link tables set up to get at the backend data tables, so I was wondering if an easy way exists to just make the link tables come from Postgres, so then the frontend Access forms will not have to be altered at all. This business is running a mix of Access 2000 & Access XP across their computers.

Any links to outisde web-sites maybe? Thanks

 

[PHV]

with a postgres database where the sql queries are executed on the server
You have to convert your queries to Passthru queries with postgres SQL syntax.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dakota81]

Thanks for the help, I'm gathering some information here so that hopefully in a week or two we could take a weekend to make these changes. What I believe is happening is that the forms are taking care of all the queries - in other words the forms are set up to "pull data from this table" and using the built-in Access "save" and "new" buttons, so the sql queries are not like built through vb and executed.

Now that I've been thinking about this more, a couple years ago I had tried to test out a similar setup to this with creating link tables in Access to connect to Postgres, but I remember it not working correctly, like maybe the data would not be updated properly, or there was a large delay before Access recognized the changes. It was like the data would get edited in one form, then I would pull up a second form, but that would still see the old data, I would have to wait ten or so seconds before I could see the new data in the forms. Does this sound familiar to anyone?


So if I try this again, and create link tables to the posgres tables, would Access automatically default to pass-through sql queries? I do not see any options in the form properties.