PostgreSQL and MS Access

[Zlatko Matiæ]

Is it possible to use Access as front-end for POstgreSQL and how ?
What about Access Projects (.adp) and PostgreSQL ?

[MGFoster]

Zlatko Matiæ wrote:

Is it possible to use Access as front-end for POstgreSQL and how ?
What about Access Projects (.adp) and PostgreSQL ?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

As an experiment I once set up an Access/PostgreSQL (Windows/Linux,
respectively). It worked. You have to have the Windows ODBC driver for
PostgreSQL. Use the following search words at Google.com to find some
drivers:

postgresql "odbc driver"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjX+9oechKqOuFEgEQJXpwCg+9gKJ8XJmv1c1lzRjYRBSE +4Dx8AoIEj
49GWU6D+yE6axnMNznWmCJsb
=+aXy
-----END PGP SIGNATURE-----

[Ruben Baumann]

"Zlatko Matiæ" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...

Is it possible to use Access as front-end for POstgreSQL and how ?
What about Access Projects (.adp) and PostgreSQL ?

================================================== ====================

Yes, it's possible to use Access as a frontend to PostgreSQL. You'll need
to find an ODBC driver for PostgreSQL. I don't know if PostgreSQL itself
now runs under windows; as far as I know, it only runs under Linux. Your
Access frontend(containing the forms, pass-thru queries, reports, etc.)
would run on a windows machine networked to a Linux machine running the
PostgreSQL server containing your tables.

It is NOT possible to use an ADP with PostgreSQL. ADP's work strictly (at
this time, and for the fore-seeable future, anyway) with MS SQLServer.

HTH
Ruben

[Steve Jorgensen]

On Mon, 14 Mar 2005 15:25:58 -0800, "Ruben Baumann" <ry**@yahoo9.com> wrote:

"Zlatko Matiæ" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...

Is it possible to use Access as front-end for POstgreSQL and how ?
What about Access Projects (.adp) and PostgreSQL ?

================================================= =====================

Yes, it's possible to use Access as a frontend to PostgreSQL. You'll need
to find an ODBC driver for PostgreSQL. I don't know if PostgreSQL itself
now runs under windows; as far as I know, it only runs under Linux. Your
Access frontend(containing the forms, pass-thru queries, reports, etc.)
would run on a windows machine networked to a Linux machine running the
PostgreSQL server containing your tables.

It is NOT possible to use an ADP with PostgreSQL. ADP's work strictly (at
this time, and for the fore-seeable future, anyway) with MS SQLServer.

... to the extent they work at all.

[David W. Fenton]

"Ruben Baumann" <ry**@yahoo9.com> wrote in
news:sb********************@megapath.net:

I don't know if PostgreSQL itself
now runs under windows

Yes, it does.

But, last I checked, it runs on top of the CYGWIN libraries, and as
Steve J. has pointed out, this leads to certain security
vulnerabilities. From my perspective this means that for prototyping
it's great to run it on Windows, but for production use, it still
belongs on a non-Windows server.

Now, the project to create a full Windows version that isn't based
on CYGWIN has been around for a long time. I don't know if it's made
any progress. According to this:

http://techdocs.postgresql.org/guides/Windows

the conversion is not done yet.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Tue, 15 Mar 2005 02:59:56 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"Ruben Baumann" <ry**@yahoo9.com> wrote in
news:sb********************@megapath.net:

I don't know if PostgreSQL itself
now runs under windows

Yes, it does.

But, last I checked, it runs on top of the CYGWIN libraries, and as
Steve J. has pointed out, this leads to certain security
vulnerabilities. From my perspective this means that for prototyping
it's great to run it on Windows, but for production use, it still
belongs on a non-Windows server.

Now, the project to create a full Windows version that isn't based
on CYGWIN has been around for a long time. I don't know if it's made
any progress. According to this:

http://techdocs.postgresql.org/guides/Windows

the conversion is not done yet.

The Beta running on Windows has been out for months now with fairly good
reviews. I see that now, the production release is out as well.

http://www.postgresql.org/docs/whatsnew

[Zlatko Matic]

Thank you all for answers!

My additional question is concerning migration from MSDE to PostgreSQL. Is
there any easy way to migrate, at least tables (if not possible to migrate,
for example, stored procedures) ?
I am devoloping an .adp/MSDE aplication for a quite long time. The prototype
was in mdb, but then I migrated to MSDE because of triggers (for audit
trail) etc. I belived it will be easy to attach it to SQL Server if needed.
Now, I tried to attach it to new SQLEXPRESS and was told that it is not
possible and it was not intended to make it possible to use any variant of
SQL Server 2005 with Access. It makes me wondering whether Access Projects
have future. What if I won't be able to use my .adp aplications with new SQL
Server ? That would make my .adp aplication obsolete...
That is the reason why I consider migration to PostgreSQL...

Greetings,

Zlatko

"Steve Jorgensen" <no****@nospam.nospam> je napisao u poruci interesnoj
grupi:2r********************************@4ax.com.. .

On Tue, 15 Mar 2005 02:59:56 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"Ruben Baumann" <ry**@yahoo9.com> wrote in
news:sb********************@megapath.net:

I don't know if PostgreSQL itself
now runs under windows

Yes, it does.

But, last I checked, it runs on top of the CYGWIN libraries, and as
Steve J. has pointed out, this leads to certain security
vulnerabilities. From my perspective this means that for prototyping
it's great to run it on Windows, but for production use, it still
belongs on a non-Windows server.

Now, the project to create a full Windows version that isn't based
on CYGWIN has been around for a long time. I don't know if it's made
any progress. According to this:

http://techdocs.postgresql.org/guides/Windows

the conversion is not done yet.

The Beta running on Windows has been out for months now with fairly good
reviews. I see that now, the production release is out as well.

http://www.postgresql.org/docs/whatsnew

[Alan Webb]

Steve & Everybody,
Yay! If you have followed my posts over the years you will remember I tend
to answer most queries with "anything but Access." PostGRE here I come!

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:2r********************************@4ax.com...

On Tue, 15 Mar 2005 02:59:56 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"Ruben Baumann" <ry**@yahoo9.com> wrote in
news:sb********************@megapath.net:

I don't know if PostgreSQL itself
now runs under windows

Yes, it does.

But, last I checked, it runs on top of the CYGWIN libraries, and as
Steve J. has pointed out, this leads to certain security
vulnerabilities. From my perspective this means that for prototyping
it's great to run it on Windows, but for production use, it still
belongs on a non-Windows server.

Now, the project to create a full Windows version that isn't based
on CYGWIN has been around for a long time. I don't know if it's made
any progress. According to this:

http://techdocs.postgresql.org/guides/Windows

the conversion is not done yet.

The Beta running on Windows has been out for months now with fairly good
reviews. I see that now, the production release is out as well.

http://www.postgresql.org/docs/whatsnew

[David W. Fenton]

"Alan Webb" <kn*****@hotmail.com> wrote in
news:QO********************@comcast.com:

Yay! If you have followed my posts over the years you will
remember I tend to answer most queries with "anything but Access."
PostGRE here I come!

Well, if you're talking back end, Access is not a back end -- *Jet*
is a db engine that can be used as a back end, and if that's what
you mean, you should say Jet, not Access.

I reviewed PostgreSQL a while back and it seemed vastly superior to
MySQL at the time (and performed well in comparison, too). If I were
embarking on a project that needed a server back end, I'd definitely
start with PostgreSQL at the top of my list of back ends, especially
because I don't like being tied to Microsoft at every level of a
product.

I also find that SQL Server is not a simple product, and the
complexities of other server DBs don't look too daunting in
comparison to those of SQL Server.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Larry Linson]

"Alan Webb" wrote

Yay! If you have followed my posts
over the years you will remember I tend
to answer most queries with "anything
but Access." PostGRE here I come!

I'd say that is about as self-defeating an attitude as "always Access" (no
matter what the requirements).

However, just as a reminder: Access is the UI for user-direct use of the
product and the tool for database application generation; it can be used
with the Jet database engine that is installed by default, or with any
ODBC-compliant database engine.

PostgreSQL is a database engine. It does not provide the end-user interface
nor database application generation of Access, so you cannot use it to
replace Access. It would be an alternative to MySQL, MS SQL Server, Oracle,
DB2, Informix, or the various Sybase server products, not an alternative to
Access.

Larry Linson
Microsoft Access MVP

[Zlatko Matiæ]

Let's talk about Access as front-end to PostgreSQL...
Deployment of Access Projects with MSDE seems to be easy, since Access part
has a Connection wizard by which client can eysily connect to any MS SQL
Server.
When using MDB as a client front-end, it can be used only by ODBC drivers
either by linked tables or pass-through queries. In any case, ODBC string
must be specified. If that is true, it seems to be complex for installation
on client...
My question is: if I will use linked tables to PostgreSQL, for example, how
can I install front-end on clients without linking tables separately on
every client ?
I have never used linked tables and ODBC connections to other databases, so
forgive me if my question is trivial.

Thanks in advance.
"Larry Linson" <bo*****@localhost.not> je napisao u poruci interesnoj
grupi:ILOZd.5528$b_6.2228@trnddc01...

"Alan Webb" wrote

Yay! If you have followed my posts
over the years you will remember I tend
to answer most queries with "anything
but Access." PostGRE here I come!

I'd say that is about as self-defeating an attitude as "always Access" (no
matter what the requirements).

However, just as a reminder: Access is the UI for user-direct use of the
product and the tool for database application generation; it can be used
with the Jet database engine that is installed by default, or with any
ODBC-compliant database engine.

PostgreSQL is a database engine. It does not provide the end-user
interface
nor database application generation of Access, so you cannot use it to
replace Access. It would be an alternative to MySQL, MS SQL Server,
Oracle,
DB2, Informix, or the various Sybase server products, not an alternative
to
Access.

Larry Linson
Microsoft Access MVP

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When using ODBC connection strings you can use the IP address of the
database server instead of the server name. You can connect to a
PostgreSQL db server that way, also.

For distributing an Access front-end to clients you can use a DSN-less
connection string in the linked tables. E.g.(just guessing for the
PostgreSQL connection string. Used Local Host as the IP address. Use
your correct IP address, instead.):

ODBC;Driver=PostgreSQL;Server=127.0.0.1;Database=M yDatabase

The default port that PostgreSQL uses is 5432.

Using MSDE (all one line):

ODBC;Driver=SQL Server;Server=127.0.0.1,1433;Database=MyDatabase;
Trusted_Connection=Yes

SQL Server (MSDE) listens on port 1433. If you've changed the port
number, be sure to change the connect string.

With each ODBC linked table having a DSN-less connection each client you
give the front-end to, if they have an Internet/Intranet connection, can
access the db.

If you have a local network w/ PostgreSQL on a Linux server you can use
Samba (a connection program for *nix to other OS) to link to PostgreSQL.
I did this about 5 years ago: Linux -> Windows NT 4. I was able to
link PostgreSQL tables to Access 97, but did nothing more than the proof
of-concept set up. I never used PostgreSQL as a production data source.

I downloaded the Windows version of PostgreSQL 8.0.1 today & am having
problems linking Access XP to it. Still reading the PostgreSQL
documentation & trying out ADO OleDB connection (not working 'cuz of
start up permissions).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjf9tIechKqOuFEgEQLuNgCgnaWM9mCMvlZcb8ighCX/J3ACyaMAoNUD
TIHVbUZwXpgc+ucKHe0QPnT+
=gRh9
-----END PGP SIGNATURE-----
Zlatko Matiæ wrote:

Let's talk about Access as front-end to PostgreSQL...
Deployment of Access Projects with MSDE seems to be easy, since Access part
has a Connection wizard by which client can eysily connect to any MS SQL
Server.
When using MDB as a client front-end, it can be used only by ODBC drivers
either by linked tables or pass-through queries. In any case, ODBC string
must be specified. If that is true, it seems to be complex for installation
on client...
My question is: if I will use linked tables to PostgreSQL, for example, how
can I install front-end on clients without linking tables separately on
every client ?
I have never used linked tables and ODBC connections to other databases, so
forgive me if my question is trivial.

< SNIP >

[Zlatko Matic]

Try ODBC driver instead OLE DB...
I migrated tables from MSDE to PostgreSQL succesfully today...but I had to
set keys manually.
Also, I linked tables from MDB to database on PostgreSQL using ODBC driver.
You can download ODBC driver from http://www.postgresql.org/download/

I didn't have enough time to read PostgreSQL documentation, but I'm
wondering what is the Postgre's equivavalent of stored procedures...

Greetings,

Zlatko

"MGFoster" <[e-mail]> je napisao u poruci interesnoj
grupi:Z4******************@newsread1.news.pas.eart hlink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When using ODBC connection strings you can use the IP address of the
database server instead of the server name. You can connect to a
PostgreSQL db server that way, also.

For distributing an Access front-end to clients you can use a DSN-less
connection string in the linked tables. E.g.(just guessing for the
PostgreSQL connection string. Used Local Host as the IP address. Use
your correct IP address, instead.):

ODBC;Driver=PostgreSQL;Server=127.0.0.1;Database=M yDatabase

The default port that PostgreSQL uses is 5432.

Using MSDE (all one line):

ODBC;Driver=SQL Server;Server=127.0.0.1,1433;Database=MyDatabase;
Trusted_Connection=Yes

SQL Server (MSDE) listens on port 1433. If you've changed the port
number, be sure to change the connect string.

With each ODBC linked table having a DSN-less connection each client you
give the front-end to, if they have an Internet/Intranet connection, can
access the db.

If you have a local network w/ PostgreSQL on a Linux server you can use
Samba (a connection program for *nix to other OS) to link to PostgreSQL.
I did this about 5 years ago: Linux -> Windows NT 4. I was able to
link PostgreSQL tables to Access 97, but did nothing more than the proof
of-concept set up. I never used PostgreSQL as a production data source.

I downloaded the Windows version of PostgreSQL 8.0.1 today & am having
problems linking Access XP to it. Still reading the PostgreSQL
documentation & trying out ADO OleDB connection (not working 'cuz of
start up permissions).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjf9tIechKqOuFEgEQLuNgCgnaWM9mCMvlZcb8ighCX/J3ACyaMAoNUD
TIHVbUZwXpgc+ucKHe0QPnT+
=gRh9
-----END PGP SIGNATURE-----
Zlatko Matiæ wrote:

Let's talk about Access as front-end to PostgreSQL...
Deployment of Access Projects with MSDE seems to be easy, since Access
part has a Connection wizard by which client can eysily connect to any MS
SQL Server.
When using MDB as a client front-end, it can be used only by ODBC drivers
either by linked tables or pass-through queries. In any case, ODBC string
must be specified. If that is true, it seems to be complex for
installation on client...
My question is: if I will use linked tables to PostgreSQL, for example,
how can I install front-end on clients without linking tables separately
on every client ?
I have never used linked tables and ODBC connections to other databases,
so forgive me if my question is trivial.

< SNIP >

[MGFoster]

Zlatko Matic wrote:

Try ODBC driver instead OLE DB...
I migrated tables from MSDE to PostgreSQL succesfully today...but I had to
set keys manually.
Also, I linked tables from MDB to database on PostgreSQL using ODBC driver.
You can download ODBC driver from http://www.postgresql.org/download/

I didn't have enough time to read PostgreSQL documentation, but I'm
wondering what is the Postgre's equivavalent of stored procedures...

They have functions that can act as SPs.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)