steph wrote:
I've got below function to dynamically define and run pass-through
queries. This works OK. But I don't want to have the connection string
hard-coded, instead I want to reuse the existing connection. How can I
achieve this?
LPassThrough.Connect =
"ODBC;DSN=myDSNr;UID=myUID;PWD=myPWD;SERVER=mySERV ER"
I have a small problem with your procedure, see below after my comments.
If all you have are pass through queries created dynamically, you can't,
as far as I know - I'm talking from an Oracle background. You've got to
somehow specify the connect string.
If you keep a ptq saved as a saved querydef, you can always, of course, use:
LPassThrough.Connect = db.QueryDefs("SavedPtq").connect
I often specify a connect string as a constant in a standard module, say:
Public Const cConnect =
"ODBC;DSN=myDSNr;UID=myUID;PWD=myPWD;SERVER=mySERV ER"
Which means it's only in one place, so your line I quote above would be:
LPassThrough.Connect = cConnect
Another option is to include a linked table as one of your tabledefs.
You could the refer to your connection string that way:
LPassThrough.Connect = db.TableDefs("Linked_Table").connect
Hopefully somewhere in my mumbo jumbo above is something that will help
you! 8)
ONe other thing not directly related to what you asked. YOu are
creating a deleting a querydef in your procedure and deleting it. This
will cause bloat. Why not just use a temporary query? Rewritten, your
proc would be (I assume you're using Access 97 as you're not
differentiating between ADO and DAO - this is a good habit even in A97)
- note air code modifications and note some of the comments I put in here:
Function GetVal(pQuery) As String
Dim db As DAO.Database
Dim LPassThrough As DAO.QueryDef
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim resString As String
On Error GoTo Err_Execute
Set db = CurrentDb()
Set LPassThrough = db.CreateQueryDef("")
with LPassThrough
.Connect = <see options above>
.SQL = pQuery
.ReturnsRecords = True
Set Lrs = LPassThrough.OpenRecordset(dbOpenSnapshot)
end with
with Lrs
If .EOF = False Then
'The following is a very poor choice for a field name!
resString = .fields!NAME
.MoveNext
If .EOF = False Then
resString = ""
End If
Else
resString = ""
End If
End With
GetVal = resString
Exit_Proc:
'One thing you may have left out was the closing and
'clean up of the objects you created. If you did, then
'ignore me here, otherwise take note your app WILL leak
'memory like a sieve if you don't.
Lrs.CLose
Set Lrs = Nothing
LPassThrough.CLose
Set LPassThrough = Nothing
db.Close
Set db = nothing
Err_Execute: 'Your error handling label
Exit Function
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
