Re: MS-Access and Stored procedures - Mailing list pgsql-general

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