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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Philippe Lang Sent: Friday, May 13, 2005 3:10 AM To: Zlatko Matic; pgsql-general@xxxxxxxxxxxxxx Subject: Re: 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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] De la part de Zlatko Matic Envoyé : vendredi, 13. mai 2005 00:07 À : Hervé Inisan; pgsql-general@xxxxxxxxxxxxxx Objet : Re: 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@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 ---------------------------(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
