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(dot)Relyea(at)xerox(dot)com>
To: <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Philippe Lang
> Sent: Friday, May 13, 2005 3:10 AM
> To: Zlatko Matic; pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] De la part de Zlatko Matic
> Envoyé : vendredi, 13. mai 2005 00:07
> À : Hervé Inisan; pgsql-general(at)postgresql(dot)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(at)self-access(dot)com>
> To: <pgsql-general(at)postgresql(dot)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
>