Re: MS-Access and Stored procedures

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
>