Help in using pass-thru query

[Lara]

How can I use the query results returned
by a pass thru' query in other forms/reports?

Or Is there any way to run the pass thru' query
using vbscript and save results in global variables?

When I try to run the pass thru' query directly
in vb script, I get error "Query or Table not found".

Thanks for your help in advance!
-Lara

 

[Mary Chipman]

You can use a saved pass-through query as the basis for any form or
report. Just make sure you set the Returns Records property to True.
You can manipulate the querydef's .SQL property dynamically in code.
Opening the form or report then executes the query, displaying the
results.

--Mary

 

[Lara]

Hi Mary,

When I entered the sql statements manually it didn't work.
But after your this email, I tried to create the
query using wizard, it worked. Thanks for your help!

But still I would like to know why if it will work
in VBscript or not. I tried using sql statement in
vbScript, I kept on getting error. I may be doing
something wrong there too.

My select is 'SELECT CURRENT_USER' it doesn't have
the from clause, so I get syntax error.

Do you have idea?

Thanks again to all!!!
-Lara

 

[Ron Hinds]

Are you using this from a web page? And are you using ADO or DAO? If ADO,
which object are you using - Connection, Command or Recordset? Post the
VBScript code and we can probably help you.

 

[Lara]

Ron,

I am using this from client/server not a web page.
Here is the code snippet -
Dim sSQL As String
Dim Result1 As String
sSQL = "<my select statement>"
Me.RecordSource = sSQL
Me.Requery
Result1 = Me.RecordSource

1) I get error for my pass thru' query
2) Will it store the output of the query in Result1 above?
3) Is this the correct way to do it?

My query returns record with only one column.

Thanks for your help!
-Lara

 

[Van T. Dinh]

* Set up a ADO Connection to your SQL2K Database.
* Create a ADO Recordset "rs" with your SQL statement
using the above Connection.
* rs.Fields(0) will give you the CURRENT_USER.

Somthing like:

***Untested code****
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim Result1 As String

Set cnn = New ADODB.Connection
cnn.Open {Your connection string here}
strSQL = "SELECT CURRENT_USER"
Set rs = New ADODB.Recordset
rs.Open strSQL, cnn, adOpenStatic, adLockReadOnly
If (rs.RecordCount > 0) Then
Result1 = rs.Fields(0).Value
End If
********

HTH
Van T. Dinh
MVP (Access)

 

[Lara]

Van,

Thank you very much!
-Lara

-----Original Message-----
* Set up a ADO Connection to your SQL2K Database.
* Create a ADO Recordset "rs" with your SQL statement
using the above Connection.
* rs.Fields(0) will give you the CURRENT_USER.

Somthing like:

***Untested code****
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim Result1 As String

Set cnn = New ADODB.Connection
cnn.Open {Your connection string here}
strSQL = "SELECT CURRENT_USER"
Set rs = New ADODB.Recordset
rs.Open strSQL, cnn, adOpenStatic, adLockReadOnly
If (rs.RecordCount > 0) Then
Result1 = rs.Fields(0).Value
End If
********

HTH
Van T. Dinh
MVP (Access)





.