Search Postgresql Archives

Re: MS-Access and Stored procedures

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Sent: Thursday, May 12, 2005 11:06 PM
Subject: Re: 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux