Thread: absolute novice wanting knowledgeable opinion about front end

I created a few rather involved databases in msaccess along with saved queries,
reports and forms.

However, because of the intuitiveness of msaccess, I have secretaries who have
figured out how to create forms and reports as well and found Access's gui easy
to use.  And I found their Basic quick and easy as well to help make more
involved forms.

I decided to look at mysql and postgresql, postgresql gives me more confidence
of utilizing my databases since I did fairly well normalizing its relational
structure.

How would I enable my secretaries to keep creating and modifying forms and
reports if I switch to an open source DB.

Is there anything out there that can match msaccess power in this  graphical
highlevel programming ability.

Or should I look at mysql.



--
Phil

"Phil" <philbaseless-postgres@yahoo.com> writes:
> How would I enable my secretaries to keep creating and modifying forms and
> reports if I switch to an open source DB.

You can use Access with postgres, and likely with mysql too.  There are
certain gotchas and things that don't work, but for the most part it
works.  Trawl our mailing list archives for some more info, but the
key point is that you connect it via an ODBC driver.

(Anyone want to start putting together a page on wiki.postgresql.org
about Access compatibility?)

            regards, tom lane

> (Anyone want to start putting together a page on wiki.postgresql.org
> about Access compatibility?)

>                       regards, tom lane

If it helps we wrote a quick one.  I think its already listed on the wiki too.

http://www.postgresonline.com/journal/index.php?/archives/24-Using-MS-Access-with-PostgreSQL.html

Hope that helps,
Regina

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.

Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.

This was interesting and the comments in the article about Access's ease of use
being a bain or boon is appropriate. But it made it easy toy to target ourselves
and not have to muck thru a generic db app.

I'm not planning to mix front and back end's.

So far I found report generators and sql builders.  Form builders will be more
difficult to find.  The ones in MSaccess integrate a lot of their GUI app
features and are very powerful. For example columns can be greyed out or not
depending on content.  The forms in Access are often used to make up for it's
lack of data security that would probably be handled by postgres's ACID
compliance.  I need to educate myself on ACID compliance and other SQL that is
new and improved over Msaccess spec.

I see I would have to rewrite a lot of Access functions also.

What would be nice is if someone had a sample DB and frontend that mimic's
Access's 'Northwind traders' sample.

Anyway thanks for the replies from everyone.


>> (Anyone want to start putting together a page on wiki.postgresql.org
>> about Access compatibility?)
>
>> regards, tom lane
>
> If it helps we wrote a quick one.  I think its already listed on the
> wiki too.
>
> http://www.postgresonline.com/journal/index.php?/archives/24-Using-MS-Access-with-PostgreSQL.html
>
> Hope that helps,
> Regina
>
>
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.

On Sun, Sep 28, 2008 at 8:42 PM, Phil <philbaseless-postgres@yahoo.com> wrote:
> Anyway thanks for the replies from everyone.

Here is a link for some MS-Access Lessons that I've learned:

http://www.utteraccess.com/forums/uafavgo.php?Cat=&F_Board=93&Thread=1494943&partnumber=1&postmarker=


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Phil,

PostgreSQL is a server side database, so not quite clear what you mean by not mixing front with back.  Regardless of what you choose for your front-end, its not going to be completely tied to PostgreSQL.

It might be a good stepping stone to stick with your Access front end and just switch all your tables to linked PostgreSQL tables especially if you have a lot of time invested in writing Access functions.

For the most part you can use all the functions you have written in MS Access if you stick with Linked Tables.  If you use pass-thrus or postgresql views then you can take advantage of PostgreSQL specific functionality. You can mix and match all 3 strategies (linked tables, linked views, sql pass-thru) in the same MS Access database.

On top of that you inherit PostgreSQL ACID, cascade update/delete, network efficiency (e..g passing statements along the pipe instead of index reads) security stuff even with linked tables.  We have a bunch of applications we have written that use PostgreSQL as a backend and MS Access as a front-end.  And also a bunch that use SQL Server as back end and MS Access as front-end.  They actually work well together and don't suffer from the network issues that a pure MS Access solution does (e.g. 15 clients, slow over slow network etc) . 


Thanks,
Regina


-----Original Message-----
From: Phil [mailto:philbaseless-postgres@yahoo.com]
Sent: Sun 9/28/2008 11:42 PM
To: Obe, Regina; Tom Lane
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] absolute novice wanting knowledgeable opinion about front end

This was interesting and the comments in the article about Access's ease of use
being a bain or boon is appropriate. But it made it easy toy to target ourselves
and not have to muck thru a generic db app.

I'm not planning to mix front and back end's.

So far I found report generators and sql builders.  Form builders will be more
difficult to find.  The ones in MSaccess integrate a lot of their GUI app
features and are very powerful. For example columns can be greyed out or not
depending on content.  The forms in Access are often used to make up for it's
lack of data security that would probably be handled by postgres's ACID
compliance.  I need to educate myself on ACID compliance and other SQL that is
new and improved over Msaccess spec.

I see I would have to rewrite a lot of Access functions also.

What would be nice is if someone had a sample DB and frontend that mimic's
Access's 'Northwind traders' sample.

Anyway thanks for the replies from everyone.


>> (Anyone want to start putting together a page on wiki.postgresql.org
>> about Access compatibility?)
>
>> regards, tom lane
>
> If it helps we wrote a quick one.  I think its already listed on the
> wiki too.
>
> http://www.postgresonline.com/journal/index.php?/archives/24-Using-MS-Access-with-PostgreSQL.html
>
> Hope that helps,
> Regina
>
>
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.

Regina, thanks for help.

What I mean to say about mixing the Front and Back was regarding OperSys.

Access and the JetDB suits my databases fine and we don't have more than one
user at a time and other reasons why it is working for us. But I'm always
looking at the possiblitiy of moving away from MS.  Without a suitable
replacement for Access I can't even look into openoffice for example.  But since
it's been a few years since I did my last research on this, things have
advanced.  Mysql wasn't even a relational Db before and now even they seem to be
improving past access2000.  OpenOffice has a DB called Base. And I would guess
it could be a front end to look at for Postgres.

Anyway, as I have time to look further I'll stay in touch.

thanks for the info.

Phil



> Phil,
>
> PostgreSQL is a server side database, so not quite clear what you
> mean by not mixing front with back.  Regardless of what you choose
> for your front-end, its not going to be completely tied to
> PostgreSQL.
>
> It might be a good stepping stone to stick with your Access front end
> and just switch all your tables to linked PostgreSQL tables
> especially if you have a lot of time invested in writing Access
> functions.
>
> For the most part you can use all the functions you have written in
> MS Access if you stick with Linked Tables.  If you use pass-thrus or
> postgresql views then you can take advantage of PostgreSQL specific
> functionality. You can mix and match all 3 strategies (linked tables,
> linked views, sql pass-thru) in the same MS Access database.
>
> On top of that you inherit PostgreSQL ACID, cascade update/delete,
> network efficiency (e..g passing statements along the pipe instead of
> index reads) security stuff even with linked tables.  We have a bunch
> of applications we have written that use PostgreSQL as a backend and
> MS Access as a front-end.  And also a bunch that use SQL Server as
> back end and MS Access as front-end.  They actually work well
> together and don't suffer from the network issues that a pure MS
> Access solution does (e.g. 15 clients, slow over slow network etc) .
>
>
> Thanks,
> Regina
>
>
> -----Original Message-----
> From: Phil [mailto:philbaseless-postgres@yahoo.com]
> Sent: Sun 9/28/2008 11:42 PM
> To: Obe, Regina; Tom Lane
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] absolute novice wanting knowledgeable opinion
> about front end
>
> This was interesting and the comments in the article about Access's
> ease of use
> being a bain or boon is appropriate. But it made it easy toy to
> target ourselves
> and not have to muck thru a generic db app.
>
> I'm not planning to mix front and back end's.
>
> So far I found report generators and sql builders.  Form builders
> will be more
> difficult to find.  The ones in MSaccess integrate a lot of their GUI
> app
> features and are very powerful. For example columns can be greyed out
> or not
> depending on content.  The forms in Access are often used to make up
> for it's
> lack of data security that would probably be handled by postgres's
> ACID
> compliance.  I need to educate myself on ACID compliance and other
> SQL that is
> new and improved over Msaccess spec.
>
> I see I would have to rewrite a lot of Access functions also.
>
> What would be nice is if someone had a sample DB and frontend that
> mimic's
> Access's 'Northwind traders' sample.
>
> Anyway thanks for the replies from everyone.
>
>
>>> (Anyone want to start putting together a page on wiki.postgresql.org
>>> about Access compatibility?)
>>
>>> regards, tom lane
>>
>> If it helps we wrote a quick one.  I think its already listed on the
>> wiki too.
>>
>> http://www.postgresonline.com/journal/index.php?/archives/24-Using-MS-Access-with-PostgreSQL.html
>>
>> Hope that helps,
>> Regina
>>
>>
>> -----------------------------------------
>> The substance of this message, including any attachments, may be
>> confidential, legally privileged and/or exempt from disclosure
>> pursuant to Massachusetts law. It is intended
>> solely for the addressee. If you received this in error, please
>> contact the sender and delete the material from any computer.

There is another product people have been talking about that is a possible replacement for Access.  I think its a commercial product. Can't remember what the name of it is.  If it comes to me I'll let you know.

OpenOffice OOBase, has some nice features like the relational design window, basic reports, a knockoff of visual basic for doing macro functions, but it still needs a little bit more work to compete with MS Access advanced features.  I would say in a year or so I would start thinking about it seriously as an alternative for high-end MS Access apps as well.  For simple apps, I think it works fine as a  front end to PostgreSQL.

I basically use it as an ADP (Access project) for postgresql since it connects in much the same way as MS Access projects connect to SQL Server.


-----Original Message-----
From: Phil [mailto:philbaseless-postgres@yahoo.com]
Sent: Tue 9/30/2008 7:51 PM
To: Obe, Regina; Tom Lane
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] absolute novice wanting knowledgeable opinion about front end

Regina, thanks for help.

What I mean to say about mixing the Front and Back was regarding OperSys.

Access and the JetDB suits my databases fine and we don't have more than one
user at a time and other reasons why it is working for us. But I'm always
looking at the possiblitiy of moving away from MS.  Without a suitable
replacement for Access I can't even look into openoffice for example.  But since
it's been a few years since I did my last research on this, things have
advanced.  Mysql wasn't even a relational Db before and now even they seem to be
improving past access2000.  OpenOffice has a DB called Base. And I would guess
it could be a front end to look at for Postgres.

Anyway, as I have time to look further I'll stay in touch.

thanks for the info.

Phil



> Phil,
>
> PostgreSQL is a server side database, so not quite clear what you
> mean by not mixing front with back.  Regardless of what you choose
> for your front-end, its not going to be completely tied to
> PostgreSQL.
>
> It might be a good stepping stone to stick with your Access front end
> and just switch all your tables to linked PostgreSQL tables
> especially if you have a lot of time invested in writing Access
> functions.
>
> For the most part you can use all the functions you have written in
> MS Access if you stick with Linked Tables.  If you use pass-thrus or
> postgresql views then you can take advantage of PostgreSQL specific
> functionality. You can mix and match all 3 strategies (linked tables,
> linked views, sql pass-thru) in the same MS Access database.
>
> On top of that you inherit PostgreSQL ACID, cascade update/delete,
> network efficiency (e..g passing statements along the pipe instead of
> index reads) security stuff even with linked tables.  We have a bunch
> of applications we have written that use PostgreSQL as a backend and
> MS Access as a front-end.  And also a bunch that use SQL Server as
> back end and MS Access as front-end.  They actually work well
> together and don't suffer from the network issues that a pure MS
> Access solution does (e.g. 15 clients, slow over slow network etc) .
>
>
> Thanks,
> Regina
>
>
> -----Original Message-----
> From: Phil [mailto:philbaseless-postgres@yahoo.com]
> Sent: Sun 9/28/2008 11:42 PM
> To: Obe, Regina; Tom Lane
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] absolute novice wanting knowledgeable opinion
> about front end
>
> This was interesting and the comments in the article about Access's
> ease of use
> being a bain or boon is appropriate. But it made it easy toy to
> target ourselves
> and not have to muck thru a generic db app.
>
> I'm not planning to mix front and back end's.
>
> So far I found report generators and sql builders.  Form builders
> will be more
> difficult to find.  The ones in MSaccess integrate a lot of their GUI
> app
> features and are very powerful. For example columns can be greyed out
> or not
> depending on content.  The forms in Access are often used to make up
> for it's
> lack of data security that would probably be handled by postgres's
> ACID
> compliance.  I need to educate myself on ACID compliance and other
> SQL that is
> new and improved over Msaccess spec.
>
> I see I would have to rewrite a lot of Access functions also.
>
> What would be nice is if someone had a sample DB and frontend that
> mimic's
> Access's 'Northwind traders' sample.
>
> Anyway thanks for the replies from everyone.
>
>
>>> (Anyone want to start putting together a page on wiki.postgresql.org
>>> about Access compatibility?)
>>
>>> regards, tom lane
>>
>> If it helps we wrote a quick one.  I think its already listed on the
>> wiki too.
>>
>> http://www.postgresonline.com/journal/index.php?/archives/24-Using-MS-Access-with-PostgreSQL.html
>>
>> Hope that helps,
>> Regina
>>
>>
>> -----------------------------------------
>> The substance of this message, including any attachments, may be
>> confidential, legally privileged and/or exempt from disclosure
>> pursuant to Massachusetts law. It is intended
>> solely for the addressee. If you received this in error, please
>> contact the sender and delete the material from any computer.