What FrontEnd would you recomended for a PGSQL DB ?

[src23]

Hello everybody,


I am new to Postgresql. I'm currently using a multiuser MsAccess database, and I want to migrate to PG because of the errors that I have to deal with, when more than 20-25 users simultaneoulsy access that database.

Details about Access database (so that you know what I want from PG Frontend):

-app is used in a bank, so it needs to be VERY safe and secure (safe transactions, no data corruption and no unauthorised access)
-database has about 60 tables;
-the main tables have about 8000 / 32000 / 25000 records
-accessed by 30-40 users simultaneously (soon it will grow in complexity and number of users)
-it has the back-end located in a network share, and the front-ends are located on workstations
-I DO NOT use linked tables; I use ADO + Microsoft Jet for OLEDB 4.0 and transactions(BeginTrans/Commit/Rollback)
-a single ADO connection opened when launching application and closed at the end
-for data manipulation I used ONLY "INSERT INTO" and "UPDATE" SQL statements, called with ADOConnection.Execute (SQL_STRING)
-ALL the edit/add record operations consists of the following:
* the user search for a record;
* then opens a client-side ADO recordset with only one record which then is displayed, field by field, through VBA code, in a modal form, like this:

forms("frmXYZ").controls("txtabc")= rs.fields("abc")
forms("frmXYZ").controls("txtbcde")= rs.fields("bcde")
...............and so on............................;

* the modal form can be closed ONLY with the following actions:
- SAVE AND CLOSE -> calls a procedure with INSERT/UPDATE
- CLOSE WITHOUT SAVE ->simply close the form

-the ADO Connection is initialised in a SINGLE Module, which makes very easy to configure the connection parameters,since they can edited in a single place;


BELOW ARE MY QUESTIONS (I need you to give me a point to start from, because there's plenty on information over the net and I don't know where to begin from ) :


?????????????????????????????????????????????????????????

Q1. Would it be safe and efficient enough to use the same Access app. as FrontEnd, and only rewrite the code to connect ADOConnecton to PGSQL database ?

Q2. If Q1=yes, then how should I connect ? (using DSN, ODBC / OLEDB etc) ; and how much does the PGSQL differ from AccessSQL (I mean the INSERT and UPDATE Statements) ?

Q3. If Q1=NO, then what interface would be the most recommended to develop for adding/edit records in a PG database, which allows THE FASTEST and EASIEST development (in the same 'single record edit mode' described above) ?

Q4. (opposite to Q3)If Q1=NO, then what interface would be the most recommended to develop for adding/edit records in a PG database, which allows building a COMPLEX interface, nu matter how difficult and time-consuming the development would be ?

Q4. What about TCL/TK (I know nothing about it, but I'm willing to learn), or a web-based interface using PHP ? In this case, what software will be needed / recommended ? (web server- ex APACHE, Websphere, etc)

Q5. What about an interface built in VB6 ? (I have good knowledge of both VBA and VB6)

?????????????????????????????????????????????????????


*** NOTE: At my job, I use WinXP / Win2k; For installing Postgres server, most likely it would be a Windows machine, though I know it will be better to use FreeBSD or Linux



Any help would be much appreciated; I need you to tell me where should I start with learning, cause right now I'm a little bit confused.


Thanx in advance !

 

[ceco]

the easiest way to port the application is to dump the data and restore it in postgresql to make it easyer you can use one of the following

http://www.sqlmanager.net/en/products/postgresql/dataimport,

http://www.sqlmanager.net/en/products/postgresql/datapump,

http://data-conversions.net/access-to-postgresql-pro-converter.html

(

and after that change all the queries, forms etc to work through ODBC with the postgresql server

later on you can change the frontend to something else, practicaly whatever you like (if you decide to switch to web interface you should consider that it is more difficult to make it easy to use then native executable client)

 

[src23]

You mean that if I keep that ADO Connection open, and I only change the open parameters to connect to a PGSQL database, it will work with the insert/update queries the same as with an AccessDB ? I don't know the differences between AccessSQL and pg SQL, but I think the insert/update clauses must be the same.

If what I said above is true, this will be great, because there are only a few modifications to be made.


But there two more things I need to know:
1. does anyone know how should I open an ADO Connection from VBA to a Postgresql database ?

2. is there a way I can create functions on server, functions that will be visible on the client side ? I need this because I currently process all the data on the client-side, and I want it to pass the data to the functions on server in order to be processed and only send the answer to the client (because there are some calculations which must be secret - and I cannot make mde, because I load controls at runtime).

 

[ceco]

yes there might be some small modifications to the sql, but if they are simple insert/update/select statements you should not have problems

I don't know ADO what exactly use, but here

http://www.postgresql.org/download/

in the part "Application Programming Interfaces" there are drivers for odbc .net and etc

you can create server function, but not in VB, you can use plain sql, plpgsql, plperl and many more see

http://www.postgresql.org/docs/8.0/interactive/server-programming.html

(also for additional server-side languages see the

http://www.postgresql.org/download/

page the part "Server-side Procedural Languages"

 

[src23]

Thank you ceco for your answers;

In this weekend I'll start playng with the stored procedures, and try to open an ADO connection to a postgresql database.

I'll be back on monday with some questions....

BTW, what o.s. do you recommend for the server machine ? Should I risk with a PostgreSQL v8.0.3 installation on Windows, or is better a FreeBSD or Linux ? I've installed FreeBSD and Linux several times before, but only for fun, didn't need serious configuration, so i'm not too experienced. Never touched any Unix before

 

[ceco]

Since you are familiar with windows as a startup you better use it and later if you wish you can try linux or bsd

 

[flugh]

I am in a similar situation. I used postgres to replace the backend of an access database I built (then split into front-end and back-end). It's a happy enough situation, minus the part where I can't get access to play nice with boolean's (how to you get a checkbox to try to put a true or 1 in the backend instead of a stupid -1? who's idea was that??). Cruddy dirty hacks have overcome the few drawbacks.

On the backend, I wouldn't think of running postgres on windows in a production environment. I can't give a good technically supported reason why. It just makes me feel queasy, and seems wrong. Let windows be the desktop system it's designed to be, and run postgres on a server with some sense about it. I use Linux, others may suggest a BSD. But either way, I'm not trusting my data to windows.

Just my gut.

----
JBR

 

[src23]

Hi flugh !

In Access VBA, True=-1, and when you have a checkbox checked, it hat the value True (-1); if the checkbox is unchecked, it is false (0 - zero). The same is with Yes/No fields.
If you want to store 1 in a field for a checked chkbox, you should use VBA. The easiest way is to use
ABS(your_checkbox.value)

BTW, how do you connect to your postgres back-end ? Using linked tables ? If yes, do you have bound forms ?
I'm interested if it works well with bound forms (I haven't tested yet)

 

[flugh]

I have mostly bound forms. I just link them via a system DSN using psqlodbc driver (available at gborg.postgresql.org). Works well enough for me. Maybe someday soon I will implement some security and only use a user dsn, or prompt for userassword, something. My current method isn't secure. But for now, I'm the only one using the thing, so it's not critical

----
JBR

 

[flugh]

Oh, and as for the true/false boolean thing, I dont' see why Access doesn't just do a 1/0 or something. If I bind a checkbox to a boolean field, and the value is true, I click, it's false, no problem. BUT!!!! if value is false, and I click, I get the dreaded 'value is too big for the field' error. Proof positive that Access sucks in such a way no mortal can properly explain, but must be dealt with because we use Windows and Access is the easiest way (I stole that from the boss' executive summary).

Someone must have said 'solitaire was written with Access' or something at the last board meeting.

----
JBR