How do I pass parameters from VB6 to a Query in MS Access?

[javeddli]

I have got a query in my MS Access database which requires 2 parameters. From my VB6 program, I need to call this MS Access Query by passing the 2 parameters dynamically. How can this be done?

And, thanking in advance...


************************************************** *******************************
"If an experiment works fine, something has gone wrong"
************************************************** *******************************

[CaptainD]

I have got a query in my MS Access database which requires 2 parameters. From my VB6 program, I need to call this MS Access Query by passing the 2 parameters dynamically. How can this be done?

And, thanking in advance...


************************************************** *******************************
"If an experiment works fine, something has gone wrong"
************************************************** *******************************

Do you have an Access Database that has a query in it, and you want to run that query?

VB programs are usually the "Front end" that can have an Access Database as a "Back end". You usually build the query in VB taking values from the form as your Parameters and then run the SQL statement against the database.

[Anix]

Hi CaptainD

I have a very similar problem....

I have an Access 2000 Query that expects 2 parameters. Currently when the query is run with Access pop-up windows request the parameters. However i would like to run this query from VB6.
So i need to call/run the query and pass it the 2 values.

Any help is much appreciated..

SJ

[Hemant Pathak]

Hi........................

it is so simple.... it is the part of my PMS(Production managment system) Project

Dim sQueryName As String
Dim rs As New ADODB.Recordset
Dim Com As New ADODB.Command
sQueryName = "STOCK_VIEW_G_TOTAL"
With Com
Set .ActiveConnection = Cnn
.CommandText = sQueryName
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ENTER_ITEM_NAME", adVarChar, adParamInput, Len(CmbSType.Text), CmbSType.Text)
.Parameters.Append .CreateParameter("FINANCE_YEAR", adVarChar, adParamInput, Len(CStr(FNYear)), FNYear)
End With
set rs=com.excute

[CaptainD]

Hi CaptainD

I have a very similar problem....

I have an Access 2000 Query that expects 2 parameters. Currently when the query is run with Access pop-up windows request the parameters. However i would like to run this query from VB6.
So i need to call/run the query and pass it the 2 values.

Any help is much appreciated..

SJ

I think you missed the point of the first answer. Access is a database program that provides you with an interface to the database as well as a means of creating a database. VB is a program that allows you to create an interface to a database. The database can be an MDB (Access database) DBF, SQL server etc. The query to pull information from the database can come from anywhere that can access the database. In VB you write the query and pass that to the database to return information to your VB application. So write you wuery in code getting your parameters from your application and join those parameters to the SQL string to be passed to the database.

[Hemant Pathak]

------------------------------------------------------------------------------------------
Note:
CmbSType.Text=send ur input in Query it is my Combo Box Value
FNYear= it string Value Like Sep/2006 Like this

i have the the two input of my access query.
---------------------------------------------------------------------------------------------
My Query
---------------
SELECT SM.ITEMNAME, FORMAT(Sum(IIf(FLAG='O',WEIGHT,0)),'0.000') AS OPENING, FORMAT(SUM(IIf(FLAG='P',WEIGHT,0)),'0.000') AS PURCHASE, FORMAT(SUM(IIF(FLAG='IFA',WEIGHT,0)),'0.000') AS TRANS_IN, FORMAT(SUM(IIF(FLAG='PR',WEIGHT,0)),'0.000') AS PUR_RETURN, FORMAT(SUM(IIF(FLAG='TF' OR FLAG='TO',Weight,0)),'0.000') AS DISPATCH, FORMAT(((VAL(OPENING)+VAL(PURCHASE)+VAL(TRANS_IN))-(VAL(PUR_RETURN)+VAL(DISPATCH))),'0.000') AS BALANCE
FROM StockMaster AS SM
WHERE (((SM.IType)=[ENTER_ITEM_NAME]) AND ((SM.Place)='Godown') AND (FYEAR=[FINANCE_YEAR]))
GROUP BY SM.ItemName;
--------------------------------------------------------------------------------------------------------------
i think it is a right think.

[CaptainD]

Hi........................

it is so simple.... it is the part of my PMS(Production managment system) Project

Dim sQueryName As String
Dim rs As New ADODB.Recordset
Dim Com As New ADODB.Command
sQueryName = "STOCK_VIEW_G_TOTAL"
With Com
Set .ActiveConnection = Cnn
.CommandText = sQueryName
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ENTER_ITEM_NAME", adVarChar, adParamInput, Len(CmbSType.Text), CmbSType.Text)
.Parameters.Append .CreateParameter("FINANCE_YEAR", adVarChar, adParamInput, Len(CStr(FNYear)), FNYear)
End With
set rs=com.excute

What is the advantage of running a query that resides in Access as opposed to just putting it (The SQL string) in your VB program?

[CaptainD]

------------------------------------------------------------------------------------------
Note:
CmbSType.Text=send ur input in Query it is my Combo Box Value
FNYear= it string Value Like Sep/2006 Like this

i have the the two input of my access query.
---------------------------------------------------------------------------------------------
My Query
---------------
SELECT SM.ITEMNAME, FORMAT(Sum(IIf(FLAG='O',WEIGHT,0)),'0.000') AS OPENING, FORMAT(SUM(IIf(FLAG='P',WEIGHT,0)),'0.000') AS PURCHASE, FORMAT(SUM(IIF(FLAG='IFA',WEIGHT,0)),'0.000') AS TRANS_IN, FORMAT(SUM(IIF(FLAG='PR',WEIGHT,0)),'0.000') AS PUR_RETURN, FORMAT(SUM(IIF(FLAG='TF' OR FLAG='TO',Weight,0)),'0.000') AS DISPATCH, FORMAT(((VAL(OPENING)+VAL(PURCHASE)+VAL(TRANS_IN))-(VAL(PUR_RETURN)+VAL(DISPATCH))),'0.000') AS BALANCE
FROM StockMaster AS SM
WHERE (((SM.IType)=[ENTER_ITEM_NAME]) AND ((SM.Place)='Godown') AND (FYEAR=[FINANCE_YEAR]))
GROUP BY SM.ItemName;
--------------------------------------------------------------------------------------------------------------
i think it is a right think.

Only thing I see that might be a problem is your where cluase has items that are not in the Group by, but if it runs in Access that way, great.

I VB it will not prompt you for inputs the way Access does when it open. you need to pull the parameters from your VB form through input boxes or tect fields, combos, some means and place that in the query string.

[Hemant Pathak]

Hi...........

Q. 1 : What is Cnn?
Ans : yes u r right it is AdoConnection.
Like
Dim CNN as New ADODB.Connection
i think it it clear ur first quistion.

Q 2 How do you tell VB where the access.mdb is located?
Ans : Put ur mdb file in ur Exe path and call app.path it is return ur Current application path.

like ur mdb name=MyDatabase.mdb
dbpath=app.path & "\MyDatabase.mdb"

Q 3 : About Query?
Ans : When u call the query and not pass the proper parameter then vb error is raised some parameter is missing.

and u know when u create the query this time u well know to what is the parameter to paas.

and any problem..................

[Twinprabu]

Only thing I see that might be a problem is your where cluase has items that are not in the Group by, but if it runs in Access that way, great.

I VB it will not prompt you for inputs the way Access does when it open. you need to pull the parameters from your VB form through input boxes or tect fields, combos, some means and place that in the query string.


Hi

I have created a Button called 'Reports' behind which a stored procedure is running with the help of wizard.If I click on Reports it will prompt for the start and end date.Once both dates are given a report will be generated.Now Am planning to do some modification like instead of prompting for the parameters am gonna create two text boxes with start and end date which should be passed to the stored procedure.Do I need to write stored procedure without wizard for passing arguments or else is there any other way?