×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

What FrontEnd would you recomended for a PGSQL DB ?

What FrontEnd would you recomended for a PGSQL DB ?

What FrontEnd would you recomended for a PGSQL DB ?

(OP)
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 !

RE: What FrontEnd would you recomended for a PGSQL DB ?

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)

RE: What FrontEnd would you recomended for a PGSQL DB ?

(OP)
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).

RE: What FrontEnd would you recomended for a PGSQL DB ?

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"

RE: What FrontEnd would you recomended for a PGSQL DB ?

(OP)
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


RE: What FrontEnd would you recomended for a PGSQL DB ?

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

RE: What FrontEnd would you recomended for a PGSQL DB ?

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

RE: What FrontEnd would you recomended for a PGSQL DB ?

(OP)
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)





RE: What FrontEnd would you recomended for a PGSQL DB ?

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 user:password, something. My current method isn't secure. But for now, I'm the only one using the thing, so it's not critical

----
JBR

RE: What FrontEnd would you recomended for a PGSQL DB ?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close