SQL pass thru query from code always propting for DSN

[Matt Williamson]

I'm creating a SQL passthrough query to run a stored proc from code. When I
do this after creating the query, I'm getting prompted for a Data source any
time I run it and it doesn't always take, so sometime I get prompted
multiple times before it runs. Since it's the source for a cross tab query
that is the datasource for a report, it's getting very annoying. I was
changing the datasource to a local table just so I can edit the report and
then changing it back but that has stopped working since I dynamically
started creating the query from code. If I create the Pass through from
Access through the menu, it works without the prompting but as soon as I do
it from code, it starts the prompting. Here is the code I'm using to create
the query:

Private Sub cmdRunReport_Click()

lblDateForReport.Caption = CStr(Me.dFrom.Value & " - " & Me.dTo.Value)

Select Case Me.ReportSelect.Value

Case 1
Call Runstoredproc("pContDist")
DoCmd.OpenReport "Contributions & Distributions Totals", acViewPreview
Case 2
Call Runstoredproc("pContDist")
DoCmd.OpenReport "Details by SalesOffice", acViewPreview
Case Else
MsgBox "Select One of the reports to run."

End Select

Public Function Runstoredproc(sProcName As String) As Boolean

Dim sConnect As String, sSQL As String
Dim dbs As Database, qdf As QueryDef

sConnect = "ODBC;Provider=SQLNCLI; Server=Server\Instance; Database=Scratch;
Trusted_Connection=yes;"

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryCurrentProc")
sSQL = "exec " & sProcName & " @StartDate=" & _
"'" & Me.dFrom.Value & "'" & ",@EndDate=" & "'" & Me.dTo.Value & "'"
Debug.Print sSQL
qdf.SQL = sSQL
qdf.Connect = sConnect

End Function


Do I need to add something to the code to get the connection to persist?

TIA

Matt

 

[Mark Andrews]

I know I used to do this all the time, but all my code did was change the
sql of an existing querydef.

So if you create a pass-thru query that doesn't prompt and then just use
code to change the sql I think that will work.

I would have to dig to figure out what all needs to be set to create a
pass-thru query that doesn't prompt in code.

HTH,
Mark

 

[AG]

If the passthrough query previously worked without prompting and you are not
changing the connection, you can either not touch it or just reset it to
what it was.

sConnect = qdf.Connect
qdf.Connect = sConnect

 

[Matt Williamson]

I know I used to do this all the time, but all my code did was change the
sql of an existing querydef.

So if you create a pass-thru query that doesn't prompt and then just use
code to change the sql I think that will work.

I would have to dig to figure out what all needs to be set to create a
pass-thru query that doesn't prompt in code.

That's kind of what I did. I made a pass through that worked fine and didn't
prompt. I made a copy of it and tested and it worked with no prompt. The
first time I ran that code that modified the copy of the pass through, it
started prompting. I've re-created it 3x now and the results are always the
same.

I also just tried the code from the MS KB
http://support.microsoft.com/kb/131534 and it prompts too. I'm wondering if
it's the connection string I'm using, it works but maybe there is a bug or
"feature" I'm missing.

-Matt

 

[Matt Williamson]

That's kind of what I did. I made a pass through that worked fine and
didn't prompt. I made a copy of it and tested and it worked with no
prompt. The first time I ran that code that modified the copy of the pass
through, it started prompting. I've re-created it 3x now and the results
are always the same.

I also just tried the code from the MS KB
http://support.microsoft.com/kb/131534 and it prompts too. I'm wondering
if it's the connection string I'm using, it works but maybe there is a bug
or "feature" I'm missing.

I changed part of the connection string to "ODBC;Driver={SQL Native Client};
from "ODBC;Provider=SQLNCLI; and now it works without prompting.

 

[Mark Andrews]

Glad you got it working. I would just not change the connection string in
code (only change the sql of the query).
Mark