Re: [GENERAL] MS-Access and Stored procedures

[rahul143]

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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] MS-Access and Stored procedures

[Zlatko Matic]

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 [EMAIL PROTECTED]
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: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED] 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

Re: [GENERAL] MS-Access and Stored procedures

[Philippe Lang]

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 : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED]
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

Re: [GENERAL] MS-Access and Stored procedures

[Ets ROLLAND]

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 - 
  From: 
  Ets ROLLAND 
  
  To: pgsql-general@postgresql.org 
  
  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: [GENERAL] MS-Access and Stored procedures

[Relyea, Mike]

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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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

Re: [GENERAL] MS-Access and Stored procedures

[Jeff Eckermann]

Zlatko Matic [EMAIL PROTECTED] wrote in message 
news:[EMAIL PROTECTED]
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 [EMAIL PROTECTED]
 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 4: Don't 'kill -9' the postmaster

Re: [GENERAL] MS-Access and Stored procedures

[Philippe Lang]

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 kind of 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 : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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


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

Re: [GENERAL] MS-Access and Stored procedures

[Hervé Inisan]

 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 [EMAIL PROTECTED]

Re: [GENERAL] MS-Access and Stored procedures

[Zlatko Matic]

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 [EMAIL PROTECTED]
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 [EMAIL PROTECTED]

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] MS-Access and Stored procedures

[Hervé Inisan]

 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: [GENERAL] MS-Access and Stored procedures

[Zlatko Matic]

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 [EMAIL PROTECTED]
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