Postgres Pro
Downloads
Home   >   mailing lists

Thread: MS-Access and Stored procedures

MS-Access and Stored procedures

From
"Ets ROLLAND"
Date:
Hello !
 
How can I use stored procedures (functions) with MS-Access 2002 connected to PostgreSQL 8.0 ?
 
Best regards.
 
Luc
 

Re: MS-Access and Stored procedures

From
"Philippe Lang"
Date:
Hi,

1) The simplest way to call a function from MS Access is to use a "pass-through query", like:

SELECT * FROM public."search_article"();


2) If the parameter is/are dynamic, that's more complicated. You have to edit the query at run-time, like with this
kindof code: 

----------------
Sub search_store(query As String, p As String)
On Error GoTo search_storeError

    Dim MyDatabase As DAO.DataBase
    Dim MyQueryDef As DAO.QueryDef

    cmdSourisSablier

    Set MyDatabase = CurrentDb()
    If (QueryExists(query)) Then MyDatabase.QueryDefs.Delete query
    Set MyQueryDef = MyDatabase.CreateQueryDef(query)

    MyQueryDef.Connect = "ODBC;DSN=" & global_dsn_name() & ";"
    MyQueryDef.SQL = "SELECT * FROM public." & """" & query & """" & "('" & p & "');"
    MyQueryDef.ReturnsRecords = True

    MyQueryDef.Close
    Set MyQueryDef = Nothing

    MyDatabase.Close
    Set MyDatabase = Nothing

search_storeExit:
    cmdSourisNormal
    Exit Sub

search_storeError:
    MsgBox "Error in search_store."
    Resume search_storeExit
End Sub
----------------

That's fine if your query is linked to a report, for example.


3) You can also call a function from code without using a pass-through query, just to retreive a result:

----------------
Function charge_disponible_semaine(code_etape As String, semaine As Integer, année As Integer) As Double
On Error GoTo charge_disponible_semaineError

    Dim MyWorkspace As DAO.Workspace
    Dim MyConnection As DAO.Connection
    Dim MyRecordset As DAO.Recordset
    Dim MySQLString As String
    Dim MyODBCConnectString As String
    Dim query As String

    query = "charge_disponible_semaine"

    Set MyWorkspace = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
    MyODBCConnectString = "ODBC;DSN=" & global_dsn_name() & ";"
    Set MyConnection = MyWorkspace.OpenConnection("Connection1", dbDriverNoPrompt, , MyODBCConnectString)
    MySQLString = "SELECT * FROM public." & """" & query & """" & "('" & code_etape & "', " & semaine & ", " & année &
");"
    Set MyRecordset = MyConnection.OpenRecordset(MySQLString, dbOpenDynamic)

    With MyRecordset
        If Not .EOF Then
            charge_disponible_semaine = MyRecordset("charge_disponible_semaine")
        Else
            charge_disponible_semaine = 0
        End If
    End With

    MyRecordset.Close
    Set MyRecordset = Nothing

    MyConnection.Close
    Set MyConnection = Nothing

    MyWorkspace.Close
    Set MyWorkspace = Nothing

charge_disponible_semaineExit:
    Exit Function

charge_disponible_semaineError:
    MsgBox "Error in charge_disponible_semaine."
    Resume charge_disponible_semaineExit
End Function
----------------


I hope this helps. One or two utility function are needed:

----------------
Public Function global_dsn_name() As String
    global_dsn_name = "you_dsn_name"
End Function

Public Function QueryExists(QueryName As String) As Boolean
On Error Resume Next

    QueryExists = IsObject(CurrentDb().QueryDefs(QueryName))

End Function
----------------


Philippe Lang

________________________________

De : pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] De la part de Ets ROLLAND
Envoyé : jeudi, 12. mai 2005 17:28
À : pgsql-general@postgresql.org
Objet : [GENERAL] MS-Access and Stored procedures


Hello !

How can I use stored procedures (functions) with MS-Access 2002 connected to PostgreSQL 8.0 ?

Best regards.

Luc

Re: MS-Access and Stored procedures

From
Hervé Inisan
Date:
> How can I use stored procedures (functions) with MS-Access
> 2002 connected to PostgreSQL 8.0 ?

An alternative to Philippe's solution is to use ADO.
Here is an sample function :
(assuming ActiveX Data Object lib is checked in the Tools/References menu)

Function ADO_PG()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strSQL As String

' Open connection
Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.ConnectionString = "DSN=<your ODBC DSN here>"
cnn.Open

' Display resultset (SELECT...)
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM a_function_returning_rows()"
rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
While Not rst.EOF
    Debug.Print rst("one column name here")

    ' Next record
    rst.MoveNext
Wend
rst.Close
Set rst = Nothing

' Execute function (e.g.: INSERT, UPDATE...)
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "another_pg_function()"
cmd.CommandType = adCmdStoredProc
cmd.Execute
Set cmd = Nothing

' Close resources
cnn.Close
Set cnn = Nothing
End Function

Of course, parameters can be sent to stored procedures.

HTH,
-- Hervé Inisan, www.self-access.com

Re: MS-Access and Stored procedures

From
"Zlatko Matic"
Date:
Hello...This is very interesting. I have also asked myself how to prepare
and execute stored procedures on POstgre from MS Access.
Could you, please, give some example of Postgre function with parameters
that is executed as stored procedure from MS Access? How would you pass
parameters ? Using ADO Command object?

Greetings,

Zlatko



----- Original Message -----
From: "Hervé Inisan" <typo3@self-access.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, May 12, 2005 6:15 PM
Subject: Re: [GENERAL] MS-Access and Stored procedures


>> How can I use stored procedures (functions) with MS-Access
>> 2002 connected to PostgreSQL 8.0 ?
>
> An alternative to Philippe's solution is to use ADO.
> Here is an sample function :
> (assuming ActiveX Data Object lib is checked in the Tools/References menu)
>
> Function ADO_PG()
> Dim cnn As ADODB.Connection
> Dim rst As ADODB.Recordset
> Dim cmd As ADODB.Command
> Dim strSQL As String
>
> ' Open connection
> Set cnn = New ADODB.Connection
> cnn.CursorLocation = adUseClient
> cnn.ConnectionString = "DSN=<your ODBC DSN here>"
> cnn.Open
>
> ' Display resultset (SELECT...)
> Set rst = New ADODB.Recordset
> strSQL = "SELECT * FROM a_function_returning_rows()"
> rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
> While Not rst.EOF
>    Debug.Print rst("one column name here")
>
>    ' Next record
>    rst.MoveNext
> Wend
> rst.Close
> Set rst = Nothing
>
> ' Execute function (e.g.: INSERT, UPDATE...)
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = cnn
> cmd.CommandText = "another_pg_function()"
> cmd.CommandType = adCmdStoredProc
> cmd.Execute
> Set cmd = Nothing
>
> ' Close resources
> cnn.Close
> Set cnn = Nothing
> End Function
>
> Of course, parameters can be sent to stored procedures.
>
> HTH,
> -- Hervé Inisan, www.self-access.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: MS-Access and Stored procedures

From
Hervé Inisan
Date:
> Hello...This is very interesting. I have also asked myself
> how to prepare and execute stored procedures on POstgre from
> MS Access.
> Could you, please, give some example of Postgre function with
> parameters that is executed as stored procedure from MS
> Access? How would you pass parameters ? Using ADO Command object?

AFAIK, there are 2 ways to send parameters from Access to a PG function,
using ADO:

1. Write the parameters as the CommandText string:
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "mypgfunction('this is a parameter', 25)"
cmd.CommandType = adCmdStoredProc
cmd.Execute
Set cmd = Nothing

The CommandText string can be the result of a concatenation:
Cmd.CommandText = "mypgfunction('" & strMyString & "', " & intMyValue & ")"

2. Another way is to use "true" ADO parameters:
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "mypgfunction"
cmd.CommandType = adCmdStoredProc

Dim prm1 As ADODB.Parameter
Set prm1 = New ADODB.Parameter
With prm1
    .Type = adVarChar
    .Direction = adParamInput
    .Value = "another string sent to PG"
    .Name = "param1"
    .Size = 30
End With

Dim prm2 As ADODB.Parameter
Set prm2 = New ADODB.Parameter
With prm2
    .Type = adInteger
    .Direction = adParamInput
    .Value = 25
    .Name = "param2"
    .Size = 0
End With
cmd.Parameters.Append prm1
cmd.Parameters.Append prm2
cmd.Execute
Set cmd = Nothing

Voilà!
-- Hervé Inisan, www.self-access.com

Re: MS-Access and Stored procedures

From
"Zlatko Matic"
Date:
I was using ADO command object and both refresh method and method with
creating parameter object while working with Access Project...but I didn't
try to use it with PostgreSQL...
I would rather like to have all queries on client side anyway. Therefore I
use pass-through queries. But it doesn't allow using parameters (execept by
concatenation). Also, you can't base subforms on pass-through queries, so
now I use strange combination of local tables, append queries with
parameters based on pass-through queries etc. It works but I'm aware that it
is not very clever:)...
I think that it would be great if pass-through queries could accept
parameters. That would be a powerfull way for executing queries on client,
while keeping all the code on front-end side...But I doubt that Microsoft
will work on further Access improving anymore. It seems that Access is left
behind while VS.NET is top technology. Too bad...

IS there any good book covering MS Access usage as front-end for different
database servers except MSDE ?

Do you have form/subform/subform...based on stored procedures ? If so, how
do you synchronize form with subform ?


Greetings,

Zlatko


----- Original Message -----
From: "Hervé Inisan" <typo3@self-access.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, May 12, 2005 11:06 PM
Subject: Re: [GENERAL] MS-Access and Stored procedures


>> Hello...This is very interesting. I have also asked myself
>> how to prepare and execute stored procedures on POstgre from
>> MS Access.
>> Could you, please, give some example of Postgre function with
>> parameters that is executed as stored procedure from MS
>> Access? How would you pass parameters ? Using ADO Command object?
>
> AFAIK, there are 2 ways to send parameters from Access to a PG function,
> using ADO:
>
> 1. Write the parameters as the CommandText string:
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = cnn
> cmd.CommandText = "mypgfunction('this is a parameter', 25)"
> cmd.CommandType = adCmdStoredProc
> cmd.Execute
> Set cmd = Nothing
>
> The CommandText string can be the result of a concatenation:
> Cmd.CommandText = "mypgfunction('" & strMyString & "', " & intMyValue &
> ")"
>
> 2. Another way is to use "true" ADO parameters:
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = cnn
> cmd.CommandText = "mypgfunction"
> cmd.CommandType = adCmdStoredProc
>
> Dim prm1 As ADODB.Parameter
> Set prm1 = New ADODB.Parameter
> With prm1
>    .Type = adVarChar
>    .Direction = adParamInput
>    .Value = "another string sent to PG"
>    .Name = "param1"
>    .Size = 30
> End With
>
> Dim prm2 As ADODB.Parameter
> Set prm2 = New ADODB.Parameter
> With prm2
>    .Type = adInteger
>    .Direction = adParamInput
>    .Value = 25
>    .Name = "param2"
>    .Size = 0
> End With
> cmd.Parameters.Append prm1
> cmd.Parameters.Append prm2
> cmd.Execute
> Set cmd = Nothing
>
> Voilà!
> -- Hervé Inisan, www.self-access.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

Re: MS-Access and Stored procedures

From
"Philippe Lang"
Date:
Hi,

You can use pass-through queries with parameters. You have to edit the pass-through querydef at run-time before opening
it,and it works. That's fine if you want to use this query as a datasource for a form or a report. 

----------------
Sub search_store(query As String, p As String) On Error GoTo search_storeError

    Dim MyDatabase As DAO.DataBase
    Dim MyQueryDef As DAO.QueryDef

    cmdSourisSablier

    Set MyDatabase = CurrentDb()
    If (QueryExists(query)) Then MyDatabase.QueryDefs.Delete query
    Set MyQueryDef = MyDatabase.CreateQueryDef(query)

    MyQueryDef.Connect = "ODBC;DSN=" & global_dsn_name() & ";"
    MyQueryDef.SQL = "SELECT * FROM public." & """" & query & """" & "('" & p & "');"
    MyQueryDef.ReturnsRecords = True

    MyQueryDef.Close
    Set MyQueryDef = Nothing

    MyDatabase.Close
    Set MyDatabase = Nothing

search_storeExit:
    cmdSourisNormal
    Exit Sub

search_storeError:
    MsgBox "Error in search_store."
    Resume search_storeExit
End Sub
----------------

Regarding DAO/ADO, I suggest you have a look a performances. The fastest way for me to call PG functions was to use
DAO,which is a bit obsolete, I agree. But there was an initial overhead with ADO that made me use DAO instead. Since I
putall the logic on the server, this is only "glue code", so using DAO is not a problem, even if ADO is supposed to be
thefuture... If you put logic on the client, that's another problem maybe. 


Philippe Lang



-----Message d'origine-----
De : pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] De la part de Zlatko Matic
Envoyé : vendredi, 13. mai 2005 00:07
À : Hervé Inisan; pgsql-general@postgresql.org
Objet : Re: [GENERAL] MS-Access and Stored procedures
Importance : Haute

I was using ADO command object and both refresh method and method with creating parameter object while working with
AccessProject...but I didn't try to use it with PostgreSQL... 
I would rather like to have all queries on client side anyway. Therefore I use pass-through queries. But it doesn't
allowusing parameters (execept by concatenation). Also, you can't base subforms on pass-through queries, so now I use
strangecombination of local tables, append queries with parameters based on pass-through queries etc. It works but I'm
awarethat it is not very clever:)... 
I think that it would be great if pass-through queries could accept parameters. That would be a powerfull way for
executingqueries on client, while keeping all the code on front-end side...But I doubt that Microsoft will work on
furtherAccess improving anymore. It seems that Access is left behind while VS.NET is top technology. Too bad... 

IS there any good book covering MS Access usage as front-end for different database servers except MSDE ?

Do you have form/subform/subform...based on stored procedures ? If so, how do you synchronize form with subform ?


Greetings,

Zlatko


----- Original Message -----
From: "Hervé Inisan" <typo3@self-access.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, May 12, 2005 11:06 PM
Subject: Re: [GENERAL] MS-Access and Stored procedures


>> Hello...This is very interesting. I have also asked myself
>> how to prepare and execute stored procedures on POstgre from
>> MS Access.
>> Could you, please, give some example of Postgre function with
>> parameters that is executed as stored procedure from MS
>> Access? How would you pass parameters ? Using ADO Command object?
>
> AFAIK, there are 2 ways to send parameters from Access to a PG function,
> using ADO:
>
> 1. Write the parameters as the CommandText string:
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = cnn
> cmd.CommandText = "mypgfunction('this is a parameter', 25)"
> cmd.CommandType = adCmdStoredProc
> cmd.Execute
> Set cmd = Nothing
>
> The CommandText string can be the result of a concatenation:
> Cmd.CommandText = "mypgfunction('" & strMyString & "', " & intMyValue &
> ")"
>
> 2. Another way is to use "true" ADO parameters:
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = cnn
> cmd.CommandText = "mypgfunction"
> cmd.CommandType = adCmdStoredProc
>
> Dim prm1 As ADODB.Parameter
> Set prm1 = New ADODB.Parameter
> With prm1
>    .Type = adVarChar
>    .Direction = adParamInput
>    .Value = "another string sent to PG"
>    .Name = "param1"
>    .Size = 30
> End With
>
> Dim prm2 As ADODB.Parameter
> Set prm2 = New ADODB.Parameter
> With prm2
>    .Type = adInteger
>    .Direction = adParamInput
>    .Value = 25
>    .Name = "param2"
>    .Size = 0
> End With
> cmd.Parameters.Append prm1
> cmd.Parameters.Append prm2
> cmd.Execute
> Set cmd = Nothing
>
> Voilà!
> -- Hervé Inisan, www.self-access.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Re: MS-Access and Stored procedures

From
"Ets ROLLAND"
Date:
Many thanks to Philippe Lang and Hervé Inisan for all these very interseting  tips !
I've solved all my problems...
 
Best regards.
 
Luc
----- Original Message -----
Sent: Thursday, May 12, 2005 5:28 PM
Subject: [GENERAL] MS-Access and Stored procedures

Hello !
 
How can I use stored procedures (functions) with MS-Access 2002 connected to PostgreSQL 8.0 ?
 
Best regards.
 
Luc
 

Re: MS-Access and Stored procedures

From
"Relyea, Mike"
Date:
I do the same thing with DAO and changing my querydef at run time, but I've added a few 'enhancements'.  First, I use a
DSNLessconnection - that way I don't have to set up a DSN on each client's PC.  Check out
http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-accessvbafor more info on it.  Essentially, the
connectionstring is generated from a form that requests the username and password of the user.  It looks like this: 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim strConnInfo as string, strConnUserPass as string, strConnParms as string, strConnection as string
strConnInfo = "ODBC;Driver={PostgreSQL};Server=MyServer;Port=5432;Database=MyDB;"
strConnUserPass = "Uid=" & Me.UserName.Value & ";Pwd=" & Me.Password.Value & ";"
strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=;A7=100;A8=4096;A9=1;" & _
        "B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=0;B8=0;B9=1;" & _
        "C0=0;C1=0;C2=dd_"

strConnection = strConnInfo & strConnUserPass & strConnParms
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Next, I created a function to create the query because I do it frequently:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Function DefineQuery(strName As String, _
                    strConnect As String, _
                    intTimeout As Integer, _
                    strSQL As String, _
                    boolReturnsRecords As Boolean _
                    )
'A function to create a query given the listed parameters
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim qrydef As DAO.QueryDef

Set db = CurrentDb
db.QueryDefs.Delete (strName) 'Delete the query first if it exists
'Create the query
create_query:
Set qrydef = db.CreateQueryDef(strName)
    qrydef.Connect = strConnect
    qrydef.ODBCTimeout = intTimeout
    qrydef.SQL = strSQL
    qrydef.ReturnsRecords = boolReturnsRecords

ErrorHandler:
Select Case Err.Number
    Case 0
        Err.Clear
    Case 2501
        Err.Clear
    Case 3265
        GoTo create_query
    Case 3151
        MsgBox "Connection to database was lost.  Please close and reopen this program."
    Case Else
        MsgBox "An error occured in the function 'DefineQuery': " & Err.Number & " " & Err.Description
End Select
End Function
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Lastly, I dump the results of my passthrough query to a local table because I found I got _much_ better response time
thatway when opening the report that the data is used for.  Again, I created a function to do that: 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Function TransferQueryToTable(strqryName As String, strtblName As String)
On Error GoTo ErrorHandler
Dim qryrs As DAO.Recordset, tblrs As DAO.Recordset
Dim I As Integer

'Define the recordsets we're working with
Set qryrs = CurrentDb.QueryDefs(strqryName).OpenRecordset
Set tblrs = CurrentDb.TableDefs(strtblName).OpenRecordset

'Make sure the table is empty before we fill it
If tblrs.RecordCount = 0 Then
    qryrs.MoveFirst 'Make sure we start with the first record in the query
    tblrs.AddNew 'Prepare the table for the first record
Else
    tblrs.MoveFirst
    Do Until tblrs.EOF
        tblrs.Delete 'Delete all records in the table
        tblrs.MoveNext
    Loop
    qryrs.MoveFirst 'Make sure we start with the first record in the query
    tblrs.AddNew 'Prepare the table for the first record
End If

'Loop through records
Do Until qryrs.EOF
    For I = 0 To qryrs.Fields.count - 1
    tblrs(I) = qryrs(I) 'Set each field in the table equal to each field in the query
    Next I
    qryrs.MoveNext 'Move to the next record in the query
    tblrs.Update 'Update the table
    tblrs.AddNew 'Prepare the table for the next record
Loop
'close the recordsets
qryrs.Close
tblrs.Close

ErrorHandler:
Select Case Err.Number
    Case 0
        Err.Clear
    Case 3021
        MsgBox "No data available"
    Case Else
        MsgBox "An error occured in the function 'TransferQueryToTable': " & Err.Number & " " & Err.Description
End Select
End Function
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Just thought I'd share in case it helps anyone.

Mike

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Philippe Lang
Sent: Friday, May 13, 2005 3:10 AM
To: Zlatko Matic; pgsql-general@postgresql.org
Subject: Re: [GENERAL] MS-Access and Stored procedures

Hi,

You can use pass-through queries with parameters. You have to edit the pass-through querydef at run-time before opening
it,and it works. That's fine if you want to use this query as a datasource for a form or a report. 

----------------
Sub search_store(query As String, p As String) On Error GoTo search_storeError

    Dim MyDatabase As DAO.DataBase
    Dim MyQueryDef As DAO.QueryDef

    cmdSourisSablier

    Set MyDatabase = CurrentDb()
    If (QueryExists(query)) Then MyDatabase.QueryDefs.Delete query
    Set MyQueryDef = MyDatabase.CreateQueryDef(query)

    MyQueryDef.Connect = "ODBC;DSN=" & global_dsn_name() & ";"
    MyQueryDef.SQL = "SELECT * FROM public." & """" & query & """" & "('" & p & "');"
    MyQueryDef.ReturnsRecords = True

    MyQueryDef.Close
    Set MyQueryDef = Nothing

    MyDatabase.Close
    Set MyDatabase = Nothing

search_storeExit:
    cmdSourisNormal
    Exit Sub

search_storeError:
    MsgBox "Error in search_store."
    Resume search_storeExit
End Sub
----------------

Regarding DAO/ADO, I suggest you have a look a performances. The fastest way for me to call PG functions was to use
DAO,which is a bit obsolete, I agree. But there was an initial overhead with ADO that made me use DAO instead. Since I
putall the logic on the server, this is only "glue code", so using DAO is not a problem, even if ADO is supposed to be
thefuture... If you put logic on the client, that's another problem maybe. 


Philippe Lang



-----Message d'origine-----
De : pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] De la part de Zlatko Matic
Envoyé : vendredi, 13. mai 2005 00:07
À : Hervé Inisan; pgsql-general@postgresql.org
Objet : Re: [GENERAL] MS-Access and Stored procedures
Importance : Haute

I was using ADO command object and both refresh method and method with creating parameter object while working with
AccessProject...but I didn't try to use it with PostgreSQL... 
I would rather like to have all queries on client side anyway. Therefore I use pass-through queries. But it doesn't
allowusing parameters (execept by concatenation). Also, you can't base subforms on pass-through queries, so now I use
strangecombination of local tables, append queries with parameters based on pass-through queries etc. It works but I'm
awarethat it is not very clever:)... 
I think that it would be great if pass-through queries could accept parameters. That would be a powerfull way for
executingqueries on client, while keeping all the code on front-end side...But I doubt that Microsoft will work on
furtherAccess improving anymore. It seems that Access is left behind while VS.NET is top technology. Too bad... 

IS there any good book covering MS Access usage as front-end for different database servers except MSDE ?

Do you have form/subform/subform...based on stored procedures ? If so, how do you synchronize form with subform ?


Greetings,

Zlatko


----- Original Message -----
From: "Hervé Inisan" <typo3@self-access.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, May 12, 2005 11:06 PM
Subject: Re: [GENERAL] MS-Access and Stored procedures


>> Hello...This is very interesting. I have also asked myself
>> how to prepare and execute stored procedures on POstgre from
>> MS Access.
>> Could you, please, give some example of Postgre function with
>> parameters that is executed as stored procedure from MS
>> Access? How would you pass parameters ? Using ADO Command object?
>
> AFAIK, there are 2 ways to send parameters from Access to a PG function,
> using ADO:
>
> 1. Write the parameters as the CommandText string:
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = cnn
> cmd.CommandText = "mypgfunction('this is a parameter', 25)"
> cmd.CommandType = adCmdStoredProc
> cmd.Execute
> Set cmd = Nothing
>
> The CommandText string can be the result of a concatenation:
> Cmd.CommandText = "mypgfunction('" & strMyString & "', " & intMyValue &
> ")"
>
> 2. Another way is to use "true" ADO parameters:
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = cnn
> cmd.CommandText = "mypgfunction"
> cmd.CommandType = adCmdStoredProc
>
> Dim prm1 As ADODB.Parameter
> Set prm1 = New ADODB.Parameter
> With prm1
>    .Type = adVarChar
>    .Direction = adParamInput
>    .Value = "another string sent to PG"
>    .Name = "param1"
>    .Size = 30
> End With
>
> Dim prm2 As ADODB.Parameter
> Set prm2 = New ADODB.Parameter
> With prm2
>    .Type = adInteger
>    .Direction = adParamInput
>    .Value = 25
>    .Name = "param2"
>    .Size = 0
> End With
> cmd.Parameters.Append prm1
> cmd.Parameters.Append prm2
> cmd.Execute
> Set cmd = Nothing
>
> Voilà!
> -- Hervé Inisan, www.self-access.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: MS-Access and Stored procedures

From
"Jeff Eckermann"
Date:
""Zlatko Matic"" <zlatko.matic1@sb.t-com.hr> wrote in message
news:003401c5573f$10f1b7c0$61841dc3@zlatkovyfkpgz6...
>I was using ADO command object and both refresh method and method with
>creating parameter object while working with Access Project...but I didn't
>try to use it with PostgreSQL...
> I would rather like to have all queries on client side anyway. Therefore I
> use pass-through queries. But it doesn't allow using parameters (execept
> by concatenation). Also, you can't base subforms on pass-through queries,
> so now I use strange combination of local tables, append queries with
> parameters based on pass-through queries etc. It works but I'm aware that
> it is not very clever:)...

I find it hard to imagine why you would want to do it that way.  Perhaps if
you explain what you are trying to do, others can offer suggestions.

> I think that it would be great if pass-through queries could accept
> parameters. That would be a powerfull way for executing queries on client,
> while keeping all the code on front-end side...But I doubt that Microsoft
> will work on further Access improving anymore. It seems that Access is
> left behind while VS.NET is top technology. Too bad...
>
> IS there any good book covering MS Access usage as front-end for different
> database servers except MSDE ?

I find the "Access XX Developer's Handbook" (where XX is the Access version)
gives the best all round coverage of Access development issues, including
client-server.

>
> Do you have form/subform/subform...based on stored procedures ? If so, how
> do you synchronize form with subform ?
>
>
> Greetings,
>
> Zlatko
>
>
> ----- Original Message -----
> From: "Herv� Inisan" <typo3@self-access.com>
> To: <pgsql-general@postgresql.org>
> Sent: Thursday, May 12, 2005 11:06 PM
> Subject: Re: [GENERAL] MS-Access and Stored procedures
>
>
>>> Hello...This is very interesting. I have also asked myself
>>> how to prepare and execute stored procedures on POstgre from
>>> MS Access.
>>> Could you, please, give some example of Postgre function with
>>> parameters that is executed as stored procedure from MS
>>> Access? How would you pass parameters ? Using ADO Command object?
>>
>> AFAIK, there are 2 ways to send parameters from Access to a PG function,
>> using ADO:
>>
>> 1. Write the parameters as the CommandText string:
>> Set cmd = New ADODB.Command
>> cmd.ActiveConnection = cnn
>> cmd.CommandText = "mypgfunction('this is a parameter', 25)"
>> cmd.CommandType = adCmdStoredProc
>> cmd.Execute
>> Set cmd = Nothing
>>
>> The CommandText string can be the result of a concatenation:
>> Cmd.CommandText = "mypgfunction('" & strMyString & "', " & intMyValue &
>> ")"
>>
>> 2. Another way is to use "true" ADO parameters:
>> Set cmd = New ADODB.Command
>> cmd.ActiveConnection = cnn
>> cmd.CommandText = "mypgfunction"
>> cmd.CommandType = adCmdStoredProc
>>
>> Dim prm1 As ADODB.Parameter
>> Set prm1 = New ADODB.Parameter
>> With prm1
>>    .Type = adVarChar
>>    .Direction = adParamInput
>>    .Value = "another string sent to PG"
>>    .Name = "param1"
>>    .Size = 30
>> End With
>>
>> Dim prm2 As ADODB.Parameter
>> Set prm2 = New ADODB.Parameter
>> With prm2
>>    .Type = adInteger
>>    .Direction = adParamInput
>>    .Value = 25
>>    .Name = "param2"
>>    .Size = 0
>> End With
>> cmd.Parameters.Append prm1
>> cmd.Parameters.Append prm2
>> cmd.Execute
>> Set cmd = Nothing
>>
>> Voil�!
>> -- Herv� Inisan, www.self-access.com
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>>               http://www.postgresql.org/docs/faq
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>

Re: MS-Access and Stored procedures

From
"Zlatko Matic"
Date:
Hello Mike.
I have found your code to be very usefull for me.
I combined it with some other codes in order to establich a procedure for
startup on client.
The problem apers with relinking tables. It seems that Access creates fake
indexes automaticcaly whern relinking using your proposed conncetion string.
So, I should disable that option, but don't know which option is that ?
Where can I find description of these constants in connection string (A, B,
C)?


----- Original Message -----
From: "Relyea, Mike" <Mike.Relyea@xerox.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, May 13, 2005 2:12 PM
Subject: Re: [GENERAL] MS-Access and Stored procedures


>I do the same thing with DAO and changing my querydef at run time, but I've
>added a few 'enhancements'.  First, I use a DSNLess connection - that way I
>don't have to set up a DSN on each client's PC.  Check out
>http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-accessvba
>for more info on it.  Essentially, the connection string is generated from
>a form that requests the username and password of the user.  It looks like
>this:
>
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Dim strConnInfo as string, strConnUserPass as string, strConnParms as
> string, strConnection as string
> strConnInfo =
> "ODBC;Driver={PostgreSQL};Server=MyServer;Port=5432;Database=MyDB;"
> strConnUserPass = "Uid=" & Me.UserName.Value & ";Pwd=" & Me.Password.Value
> & ";"
> strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=;A7=100;A8=4096;A9=1;"
> & _
>        "B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=0;B8=0;B9=1;" & _
>        "C0=0;C1=0;C2=dd_"
>
> strConnection = strConnInfo & strConnUserPass & strConnParms
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Next, I created a function to create the query because I do it frequently:
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Function DefineQuery(strName As String, _
>                    strConnect As String, _
>                    intTimeout As Integer, _
>                    strSQL As String, _
>                    boolReturnsRecords As Boolean _
>                    )
> 'A function to create a query given the listed parameters
> On Error GoTo ErrorHandler
> Dim db As DAO.Database
> Dim qrydef As DAO.QueryDef
>
> Set db = CurrentDb
> db.QueryDefs.Delete (strName) 'Delete the query first if it exists
> 'Create the query
> create_query:
> Set qrydef = db.CreateQueryDef(strName)
>    qrydef.Connect = strConnect
>    qrydef.ODBCTimeout = intTimeout
>    qrydef.SQL = strSQL
>    qrydef.ReturnsRecords = boolReturnsRecords
>
> ErrorHandler:
> Select Case Err.Number
>    Case 0
>        Err.Clear
>    Case 2501
>        Err.Clear
>    Case 3265
>        GoTo create_query
>    Case 3151
>        MsgBox "Connection to database was lost.  Please close and reopen
> this program."
>    Case Else
>        MsgBox "An error occured in the function 'DefineQuery': " &
> Err.Number & " " & Err.Description
> End Select
> End Function
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Lastly, I dump the results of my passthrough query to a local table
> because I found I got _much_ better response time that way when opening
> the report that the data is used for.  Again, I created a function to do
> that:
>
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Function TransferQueryToTable(strqryName As String, strtblName As String)
> On Error GoTo ErrorHandler
> Dim qryrs As DAO.Recordset, tblrs As DAO.Recordset
> Dim I As Integer
>
> 'Define the recordsets we're working with
> Set qryrs = CurrentDb.QueryDefs(strqryName).OpenRecordset
> Set tblrs = CurrentDb.TableDefs(strtblName).OpenRecordset
>
> 'Make sure the table is empty before we fill it
> If tblrs.RecordCount = 0 Then
>    qryrs.MoveFirst 'Make sure we start with the first record in the query
>    tblrs.AddNew 'Prepare the table for the first record
> Else
>    tblrs.MoveFirst
>    Do Until tblrs.EOF
>        tblrs.Delete 'Delete all records in the table
>        tblrs.MoveNext
>    Loop
>    qryrs.MoveFirst 'Make sure we start with the first record in the query
>    tblrs.AddNew 'Prepare the table for the first record
> End If
>
> 'Loop through records
> Do Until qryrs.EOF
>    For I = 0 To qryrs.Fields.count - 1
>    tblrs(I) = qryrs(I) 'Set each field in the table equal to each field in
> the query
>    Next I
>    qryrs.MoveNext 'Move to the next record in the query
>    tblrs.Update 'Update the table
>    tblrs.AddNew 'Prepare the table for the next record
> Loop
> 'close the recordsets
> qryrs.Close
> tblrs.Close
>
> ErrorHandler:
> Select Case Err.Number
>    Case 0
>        Err.Clear
>    Case 3021
>        MsgBox "No data available"
>    Case Else
>        MsgBox "An error occured in the function 'TransferQueryToTable': "
> & Err.Number & " " & Err.Description
> End Select
> End Function
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Just thought I'd share in case it helps anyone.
>
> Mike
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Philippe Lang
> Sent: Friday, May 13, 2005 3:10 AM
> To: Zlatko Matic; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] MS-Access and Stored procedures
>
> Hi,
>
> You can use pass-through queries with parameters. You have to edit the
> pass-through querydef at run-time before opening it, and it works. That's
> fine if you want to use this query as a datasource for a form or a report.
>
> ----------------
> Sub search_store(query As String, p As String) On Error GoTo
> search_storeError
>
>    Dim MyDatabase As DAO.DataBase
>    Dim MyQueryDef As DAO.QueryDef
>
>    cmdSourisSablier
>
>    Set MyDatabase = CurrentDb()
>    If (QueryExists(query)) Then MyDatabase.QueryDefs.Delete query
>    Set MyQueryDef = MyDatabase.CreateQueryDef(query)
>
>    MyQueryDef.Connect = "ODBC;DSN=" & global_dsn_name() & ";"
>    MyQueryDef.SQL = "SELECT * FROM public." & """" & query & """" & "('" &
> p & "');"
>    MyQueryDef.ReturnsRecords = True
>
>    MyQueryDef.Close
>    Set MyQueryDef = Nothing
>
>    MyDatabase.Close
>    Set MyDatabase = Nothing
>
> search_storeExit:
>    cmdSourisNormal
>    Exit Sub
>
> search_storeError:
>    MsgBox "Error in search_store."
>    Resume search_storeExit
> End Sub
> ----------------
>
> Regarding DAO/ADO, I suggest you have a look a performances. The fastest
> way for me to call PG functions was to use DAO, which is a bit obsolete, I
> agree. But there was an initial overhead with ADO that made me use DAO
> instead. Since I put all the logic on the server, this is only "glue
> code", so using DAO is not a problem, even if ADO is supposed to be the
> future... If you put logic on the client, that's another problem maybe.
>
>
> Philippe Lang
>
>
>
> -----Message d'origine-----
> De : pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] De la part de Zlatko Matic
> Envoyé : vendredi, 13. mai 2005 00:07
> À : Hervé Inisan; pgsql-general@postgresql.org
> Objet : Re: [GENERAL] MS-Access and Stored procedures
> Importance : Haute
>
> I was using ADO command object and both refresh method and method with
> creating parameter object while working with Access Project...but I didn't
> try to use it with PostgreSQL...
> I would rather like to have all queries on client side anyway. Therefore I
> use pass-through queries. But it doesn't allow using parameters (execept
> by concatenation). Also, you can't base subforms on pass-through queries,
> so now I use strange combination of local tables, append queries with
> parameters based on pass-through queries etc. It works but I'm aware that
> it is not very clever:)...
> I think that it would be great if pass-through queries could accept
> parameters. That would be a powerfull way for executing queries on client,
> while keeping all the code on front-end side...But I doubt that Microsoft
> will work on further Access improving anymore. It seems that Access is
> left behind while VS.NET is top technology. Too bad...
>
> IS there any good book covering MS Access usage as front-end for different
> database servers except MSDE ?
>
> Do you have form/subform/subform...based on stored procedures ? If so, how
> do you synchronize form with subform ?
>
>
> Greetings,
>
> Zlatko
>
>
> ----- Original Message -----
> From: "Hervé Inisan" <typo3@self-access.com>
> To: <pgsql-general@postgresql.org>
> Sent: Thursday, May 12, 2005 11:06 PM
> Subject: Re: [GENERAL] MS-Access and Stored procedures
>
>
>>> Hello...This is very interesting. I have also asked myself
>>> how to prepare and execute stored procedures on POstgre from
>>> MS Access.
>>> Could you, please, give some example of Postgre function with
>>> parameters that is executed as stored procedure from MS
>>> Access? How would you pass parameters ? Using ADO Command object?
>>
>> AFAIK, there are 2 ways to send parameters from Access to a PG function,
>> using ADO:
>>
>> 1. Write the parameters as the CommandText string:
>> Set cmd = New ADODB.Command
>> cmd.ActiveConnection = cnn
>> cmd.CommandText = "mypgfunction('this is a parameter', 25)"
>> cmd.CommandType = adCmdStoredProc
>> cmd.Execute
>> Set cmd = Nothing
>>
>> The CommandText string can be the result of a concatenation:
>> Cmd.CommandText = "mypgfunction('" & strMyString & "', " & intMyValue &
>> ")"
>>
>> 2. Another way is to use "true" ADO parameters:
>> Set cmd = New ADODB.Command
>> cmd.ActiveConnection = cnn
>> cmd.CommandText = "mypgfunction"
>> cmd.CommandType = adCmdStoredProc
>>
>> Dim prm1 As ADODB.Parameter
>> Set prm1 = New ADODB.Parameter
>> With prm1
>>    .Type = adVarChar
>>    .Direction = adParamInput
>>    .Value = "another string sent to PG"
>>    .Name = "param1"
>>    .Size = 30
>> End With
>>
>> Dim prm2 As ADODB.Parameter
>> Set prm2 = New ADODB.Parameter
>> With prm2
>>    .Type = adInteger
>>    .Direction = adParamInput
>>    .Value = 25
>>    .Name = "param2"
>>    .Size = 0
>> End With
>> cmd.Parameters.Append prm1
>> cmd.Parameters.Append prm2
>> cmd.Execute
>> Set cmd = Nothing
>>
>> Voilà!
>> -- Hervé Inisan, www.self-access.com
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>>               http://www.postgresql.org/docs/faq
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

ODBC connection string-constants A,B,C ?

From
"Zlatko Matic"
Date:
Hello.
When building ODBC connection string for PostgreSQL there are constants
beginning with A, B and C. Where can I find description of each of these ?
Thanks.

Re: ODBC connection string-constants A,B,C ?

From
Andreas
Date:
Zlatko Matic wrote:

> Hello.
> When building ODBC connection string for PostgreSQL there are
> constants beginning with A, B and C. Where can I find description of
> each of these ?


There is a HowTo on the psqlODBC site that mentions those codes.
Keep in mind that those are MS-Access specific abrevations for ODBC
parameter names and that the HowTo is a bit dated so probably a newer
Access version might not need or might not support those codes at all.

http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-accessvba

Re: MS-Access and Stored procedures

From
rahul143
Date:
Hello Mike.
I have found your code to be very usefull for me.
I combined it with some other codes in order to establich a procedure for
startup on client.
The problem apers with relinking tables. It seems that Access creates fake
indexes automaticcaly whern relinking using your proposed conncetion string.
So, I should disable that option, but don't know which option is that ?
Where can I find description of these constants in connection string (A, B,
C)?




-----




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Re-GENERAL-MS-Access-and-Stored-procedures-tp5734652.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.