PDA

Click to See Complete Forum and Search --> : Parameter Query

rockies1
September 24th, 2001, 08:18 AM
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
September 24th, 2001, 08:48 AM
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
iouri@hotsheet.com
rockies1
September 24th, 2001, 08:55 AM
That would be fine, but I don't know how to do either...

Can you shed some light on that?

Thanks!
Iouri
September 24th, 2001, 09:14 AM
'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
iouri@hotsheet.com
rockies1
September 24th, 2001, 09:21 PM
I get an error when I try to execute....

SYNTAX ERROR IN DROP TABLE OR DROP INDEX...

Ugh....
Iouri
September 25th, 2001, 07:32 AM
On my computer it runs fine. I am using Access97. Check help for using SQL statement DROP

Iouri Boutchkine
iouri@hotsheet.com
rockies1
September 25th, 2001, 07:38 AM
Would you mind providing your full code that shows the connection information, also?

I think that may be the difference...

Thanks!
Iouri
September 25th, 2001, 08:49 AM
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
iouri@hotsheet.com
rockies1
September 25th, 2001, 10:23 AM
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...