#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    20
    Rep Power
    0

    Access procedure to Postgres, Where to start?


    Hi, I have a procedure in Access that runs on the click event of a button. I now want to run/convert this procedure in Postgres and was wondering if anyone can tell me where or how to start.

    Do I need to make it into a Postgres function?

    Do I need to re-write it in a different language.

    If I convert it how do I call the function in from the button in Access?

    Thanks for any advice

    This is what the procedure looks like in Access:

    Private Sub FilterForm()

    blnFirstCondition = True
    strCriteria = ""

    strCriteriaFields1 = "SELECT tblOrders.OrderNo, tblOrders.Signed_Order, tblOrders.Current_Order,tblOrders.Back_To_Rep, tblOrders.Notes, tblOrders.Comp_Code, tblCompanies.Comp_Name, tblOrders.Order_Date, tblOrders.Rep_Code, tblReps.Name, tblOrders.Prod_Price, tblOrders.Prod_Code, tblProducts.MainProductCode, tblProducts.Description, tblOrders.Sub_Prod_Code, tblSubProducts.AST, tblOrders.Status, tblStatus.Order, tblProdManagers.ProdMngCode, tblProdManagers.Name, tblSalesManagers.SalesMngCode, tblSalesManagers.Name, tblOrders.Designer_Code, tblDesigners.Name, IIf([Prod_Price]>=0,[Prod_Price],0) AS Valid_Price, IIf([Prod_Price]<0,-[Prod_Price],0) AS Cancelled_Price FROM tblStatus"
    strCriteriaFields2 = " INNER JOIN (tblSubProducts RIGHT JOIN (tblSalesManagers RIGHT JOIN ((tblProdManagers INNER JOIN tblReps ON tblProdManagers.ProdMngCode = tblReps.ProdMngCode) INNER JOIN (tblProducts INNER JOIN (tblDesigners RIGHT JOIN (tblCompanies INNER JOIN tblOrders ON tblCompanies.Comp_Code = tblOrders.Comp_Code) ON tblDesigners.Code = tblOrders.Designer_Code) ON tblProducts.Code = tblOrders.Prod_Code) ON tblReps.Code = tblOrders.Rep_Code) ON tblSalesManagers.SalesMngCode = tblReps.SalesMngCode) ON tblSubProducts.Code = tblOrders.Sub_Prod_Code) ON tblStatus.Status = tblOrders.Status"
    strCriteriaWhere = " WHERE "
    strCriteriaOrderBy = " ORDER BY tblOrders.Order_Date DESC"
    strCriteriaTerminate = ";"

    If Me.cboCustomer.Value <> 0 Then
    If blnFirstCondition = True Then
    If Me.cboCustomer.Value <> 0 Then
    strCriteriaCustomer = "((tblOrders.Comp_Code)=" & Me.cboCustomer.Value & ")"
    blnFirstCondition = False
    Else
    strCriteriaCustomer = ""
    End If
    Else

    End If
    Else
    strCriteriaCustomer = ""
    End If

    If Me.cboManager.Value <> 0 Then
    If blnFirstCondition = True Then
    strCriteriaManager = "((tblProdManagers.ProdMngCode)=" & Me.cboManager.Value & ")"
    blnFirstCondition = False
    Else
    strCriteriaManager = " AND " & "((tblProdManagers.ProdMngCode)=" & Me.cboManager.Value & ")"
    End If
    Else
    strCriteriaManager = ""
    End If

    If Me.cboSalesMng.Value <> 0 Then
    If blnFirstCondition = True Then
    strCriteriaSalesManager = "((tblSalesManagers.SalesMngCode)=" & Me.cboSalesMng.Value & ")"
    blnFirstCondition = False
    Else
    strCriteriaSalesManager = " AND " & "((tblSalesManagers.SalesMngCode)=" & Me.cboSalesMng.Value & ")"
    End If
    Else
    strCriteriaSalesManager = ""
    End If

    If Me.cboRep.Value <> 0 Then
    If blnFirstCondition = True Then
    strCriteriaRep = "((tblOrders.Rep_Code)=""" & Me.cboRep.Value & """)"
    blnFirstCondition = False
    Else
    strCriteriaRep = " AND " & "((tblOrders.Rep_Code)=""" & Me.cboRep.Value & """)"
    End If
    Else
    strCriteriaRep = ""
    End If

    If (Me.cboIssue.Value <> 0) And (Me.cboIssue.Visible = True) Then
    If blnFirstCondition = True Then
    strCriteriaIssue = "((tblOrders.Prod_Code)=""" & Me.cboIssue.Value & """)"
    blnFirstCondition = False
    Else
    strCriteriaIssue = " AND " & "((tblOrders.Prod_Code)=""" & Me.cboIssue.Value & """)"
    End If
    ElseIf (Me.cboIssue.Value = 0) And (Me.cboIssue.Visible = True) Then
    If blnFirstCondition = True Then
    strCriteriaIssue = "((tblProducts.MainProductCode)=""" & Me.cboProduct.Value & """)"
    blnFirstCondition = False
    Else
    strCriteriaIssue = " AND " & "((tblProducts.MainProductCode)=""" & Me.cboProduct.Value & """)"
    End If
    Else
    strCriteriaIssue = ""
    End If

    If Me.cboStatus.Value <> " All" Then
    If blnFirstCondition = True Then
    strCriteriaStatus = "((tblOrders.Status)=""" & Me.cboStatus.Value & """)"
    blnFirstCondition = False
    Else
    strCriteriaStatus = " AND " & "((tblOrders.Status)=""" & Me.cboStatus.Value & """)"
    End If
    Else
    strCriteriaStatus = ""
    End If


    If Me.cboWhichOrder.Value <> " All" Then
    If Me.cboWhichOrder.Value = "Current Orders" Then
    If blnFirstCondition = True Then
    strCriteriaWhich = "((tblOrders.Current_Order)=False)"
    blnFirstCondition = False
    Else
    strCriteriaWhich = " AND " & "((tblOrders.Current_Order)=False)"
    End If
    ElseIf Me.cboWhichOrder.Value = "Finished Orders" Then
    If blnFirstCondition = True Then
    strCriteriaWhich = "((tblOrders.Current_Order)=True)"
    blnFirstCondition = False
    Else
    strCriteriaWhich = " AND " & "((tblOrders.Current_Order)=True)"
    End If
    Else

    End If
    Else
    strCriteriaWhich = ""
    End If




    If Me.cboAdSpace.Value <> " All" Then
    If blnFirstCondition = True Then
    strCriteriaAdSpace = "((tblOrders.Sub_Prod_Code)=""" & Me.cboAdSpace.Value & """)"
    blnFirstCondition = False
    Else
    strCriteriaAdSpace = " AND " & "((tblOrders.Sub_Prod_Code)=""" & Me.cboAdSpace.Value & """)"
    End If
    Else
    strCriteriaAdSpace = ""
    End If




    If Not IsNull(Me.dtpStart.Value) Then
    If blnFirstCondition = True Then
    strCriteriaStart = "((tblOrders.Order_Date)>=#" & Format(Me.dtpStart.Value, "mm/dd/yy") & "#)"
    blnFirstCondition = False
    Else
    strCriteriaStart = " AND " & "((tblOrders.Order_Date)>=#" & Format(Me.dtpStart.Value, "mm/dd/yy") & "#)"
    End If
    Else
    strCriteriaStart = ""
    End If

    If Not IsNull(Me.dtpEnd.Value) Then
    If blnFirstCondition = True Then
    strCriteriaEnd = "((tblOrders.Order_Date)<=#" & Format(Me.dtpEnd.Value, "mm/dd/yy") & "#)"
    blnFirstCondition = False
    Else
    strCriteriaEnd = " AND " & "((tblOrders.Order_Date)<=#" & Format(Me.dtpEnd.Value, "mm/dd/yy") & "#)"
    End If
    Else
    strCriteriaEnd = ""
    End If

    If blnFirstCondition = True Then
    strCriteria = strCriteria & strCriteriaFields1 & strCriteriaFields2 & strCriteriaOrderBy & strCriteriaTerminate
    'MsgBox "strCriteria=" & Right(strCriteria, 900)
    Else
    strCriteria = strCriteria & strCriteriaFields1 & strCriteriaFields2 & strCriteriaWhere & strCriteriaCustomer & strCriteriaManager & strCriteriaSalesManager & strCriteriaRep & strCriteriaIssue & strCriteriaStatus & strCriteriaWhich & strCriteriaAdSpace & strCriteriaStart & strCriteriaEnd & strCriteriaOrderBy & strCriteriaTerminate
    'MsgBox "strCriteria=" & Right(strCriteria, 900)
    End If

    Me.RecordSource = strCriteria
    Me.Requery
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Hi Meltdown,

    I take it by reading between the lines that you want to continue using Access for the front-end, but you want PostgreSQL to be your server, via ODBC.

    PostgreSQL procedures and the features it supports are very different from Access. There is no reason you cannot accomplish the same logic, although you might have to split up logic between Access as the front end and PostgreSQL as the backend.

    You will have to start by identifying for yourself very clearly what is client-side and what is server-side. Remember, PostgreSQL is a database server, whereas Access is a database client and server put together. This means that procedures can be written that affect what happens in a form when a user clicks things, etc... as well as what data is stored in tables.

    Anything involving event-handling in the Access forms front-end must be done with Access scripting. But any queries called by the access script may be handled in a number of ways by PostgreSQL (direct calls to user-defined functions, triggers which fire upon table updates, or RULE definitions).

    So the steps you have to take involve the following questions:

    1. What do I need to have happen in the form itself?

    2. What do I need to have happen after the form data is sent to the database?

    3. What do I need as a response from the database after that happens?

    It is going to take some learning, though. Spend some time reading the PostgreSQL procedural languages documentation. Have you had any experience with stored procedures in SQL Server, or some other SQL system?
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    20
    Rep Power
    0
    Hi rycamor, thanks for taking the time to reply, I'm really just learning SQL Server now and using it on my local machine, I don't have any experience using it in a network enviroment. I understand most of the terminology and can create SP's and views etc.

    My interest in Postgres stems from my wish to offer my business customers an alternative to MS SQL. All my work involves buliding databases for SME's.

    The code above looks at the values of 8 combo boxes on a form then builds the sql string form those values.

    Are you saying the If...Else statement must always be on the front end in the form module?

    How would you break that code up?

    Thanks for any advice
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Originally posted by Meltdown
    Hi rycamor, thanks for taking the time to reply, I'm really just learning SQL Server now and using it on my local machine, I don't have any experience using it in a network enviroment. I understand most of the terminology and can create SP's and views etc.

    My interest in Postgres stems from my wish to offer my business customers an alternative to MS SQL. All my work involves buliding databases for SME's.


    PostgreSQL is a good choice as an alternative to MS SQL. However, you will find that it uses some different concepts and syntax for certain things. Actually, the syntax used by PostgreSQL is much more similar to Oracle. (SEQUENCEs instead of Autonumber, etc...)


    The code above looks at the values of 8 combo boxes on a form then builds the sql string form those values.

    Are you saying the If...Else statement must always be on the front end in the form module?


    Not at all. If-then statements can be used in either cases. I'm saying that what happens in the client or "form" environment does NOT affect the database in any way until a query is actually sent to the database. So, if you want to check the value of combo boxes, there is no way for PostgreSQL procedures to know those values, or to respond to click events, etc... It is a server. It only knows the values of data sent to it in SQL queries, or in function calls, such as "SELECT my_custom_function(param1,param2,param3);".

    So, Access needs to do a little work on the front end to determine what the user is doing, and what data from forms and other interaction will be sent to PostgreSQL. In the case of your question, it would seem the logical thing to either 1) have an Access function check those values and build an SQL string, and then send that SQL to PostgreSQL, or 2) have an Access function that merely creates a string in the form of "SELECT my_postgres_function('" + form_value1 + "', '" + form_value2"');" etc... then Access would send that string as an SQL query to PostgreSQL, which would use that string to access your stored procedure called my_postgres_function, and which would then do whatever you instruct with those values. But remember, in a PostgreSQL stored procedure, you can't *directly* reference any Access form elements, nor can you use a PostgreSQL procedure to *directly* update a PostgreSQL form element, or access any Access form properties, or handle any form events. The client is responsible for the "visual" stuff, and the server is responsible for the data, and the logic the data must play by. So, custom updating of form elements will have to occur by Access procedures which send queries, wait for responses, and then update based on those responses.

    Of course, you understand that basic form element data binding will work just as before, because that is a question of Access transparently using the form elements to make SQL queries to PostgreSQL via ODBC. But any custom logic you do will have to be divided between the user interaction stuff on one side, and the database logic on the other side. This is the same for any SQL system.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    20
    Rep Power
    0
    Thanks rycamor, I'll keep working on it.

    One thing I do find a bit strange is that Postgres appears to be slower than Access in returning data from my queries. Probably something I've missed.
  10. #6
  11. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Originally posted by Meltdown
    Thanks rycamor, I'll keep working on it.

    One thing I do find a bit strange is that Postgres appears to be slower than Access in returning data from my queries. Probably something I've missed.
    The problem more likely has to do with ODBC, and the speed of your network. Remember, if you query Access locally, there is no network involved. Also, Access may seem quicker at returning data from small tables, but watch what happens when your tables gets large.

    Remember, returning large datasets to Access, to be further processed by Access, is really counterproductive. Better to keep the processing on the server side (as much as possible), and only return the minimum you need to present data to the user. The more intelligence you can push to the server side, the faster and more reliable your system will be.

    For example, it's possible that you could eliminate Access scripting completely in the problem you posted above, simply by binding the drop-down boxes to a PostgreSQL view, or even a stored procedure. In PostgreSQL 7.3+, stored procedures can "masquerade" as tables, even returning rows as datasets. So, you might just be able to simplify the Access front-end to straightforward data binding, and then have each box bound to a "column" in the stored procedure, which is really not a table, but code that reads those values and executes the desired query. Cool, huh ?

    Anyway, if you would care to post your server and client configuration details (including operating system, RAM, disk, etc...) we can probably give you some advice to speed things up.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo