Parameter Query

[rockies1]

I have a report in Access that I need to run from VB6.

The report is based on a query.

The query is a parameter query that needs a CustID.

I need to pass that CustID in from the VB program.

How can I give the query the CustID so the report will print properly?

Thanks!

[Iouri]

You can either send the parameter, or I found it is easier to delete the query and enter the new one with the parameter

Iouri Boutchkine
[email protected]

[rockies1]

That would be fine, but I don't know how to do either...

Can you shed some light on that?

Thanks!

[Iouri]

'delete stored proc (query)
sSQL = "DROP PROCEDURE qryRHDByMonth"
cn.Execute sSQL


'create query
sSQL = "CREATE PROCEDURE qryRHDByMonth as SELECT * from Table where ...."'your param is here

cn.Execute sSQL

where cn is connection object

Iouri Boutchkine
[email protected]

[rockies1]

I get an error when I try to execute....

SYNTAX ERROR IN DROP TABLE OR DROP INDEX...

Ugh....

[Iouri]

On my computer it runs fine. I am using Access97. Check help for using SQL statement DROP

Iouri Boutchkine
[email protected]

[rockies1]

Would you mind providing your full code that shows the connection information, also?

I think that may be the difference...

Thanks!

[Iouri]

Dim sConn As String

sConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBPATH & ";uid=;pwd=;"
Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.ConnectionString = sConn
cn.Open sConn


Iouri Boutchkine
[email protected]

[rockies1]

I'm still getting the error...

I start a new project,add a reference to "Microsoft ActiveX Data Objects 2.5 Library"

Add a command button to a form. Add this code to the button:
Dim sConn as string
Dim DBPATH as string
DBPATH = App.Path & "\test.mdb"

sConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBPATH & ";uid=;pwd=;"
set cn = new ADODB.Connection
cn.CursorLocation = adUseClient
cn.ConnectionString = sConn
cn.Open sConn

'delete stored proc (query)
sSQL = "DROP PROCEDURE qrytest"
cn.Execute sSQL


'create query
sSQL = "CREATE PROCEDURE qrytest as SELECT * from tbltest" 'your param is here

cn.Execute sSQL



Running the program, and clicking the button still gives me the error give above...