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

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?

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

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



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.

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.