By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,645 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

pass-through query: reuse existing connection?

P: n/a
Hi,

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?

Thanks,
Stephan
==============
Function GetVal(pQuery) As String

Dim db As Database
Dim LPassThrough As 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("qryTemp")

LPassThrough.Connect =
"ODBC;DSN=myDSNr;UID=myUID;PWD=myPWD;SERVER=mySERV ER"
LPassThrough.SQL = pQuery
LPassThrough.ReturnsRecords = True

Set Lrs = LPassThrough.OpenRecordset(dbOpenSnapshot)

If Lrs.EOF = False Then
resString = Lrs("NAME")
Lrs.MoveNext
If Lrs.EOF = False Then
resString = ""
End If
Else
resString = ""
End If

GetVal = resString

CurrentDb.QueryDefs.Delete "qryTemp"

Exit Function
==============

Jan 23 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jan 23 '06 #2

P: n/a
Tim Marshall wrote:
db.Close
Set db = nothing
Exit Function
Err_Execute: 'Your error handling label

Exit Function

I left out the exit function before the error handler, sorry.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Jan 23 '06 #3

P: n/a
Hey, thanks for the elaborate answer! Very much appreciated! There is
certainliy something there for me to use.
regards,
stephan

Jan 24 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.