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"
************************************************** *******************************
9 20609
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.
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
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
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.
------------------------------------------------------------------------------------------
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.
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?
------------------------------------------------------------------------------------------
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.
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..................
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?
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Tcs |
last post by:
I've been stumped on this for quite a while. I don't know if it's so simple
that I just can't see it, or it's really possible. (Obviously, I HOPE it IS
possible.)
I'm trying to get my queries to run from VB. My pass-thru query retrieves
data from our AS/400 that I use to build a local table (on my PC). My pass-thru
and local do in fact...
|
by: Zlatko Matić |
last post by:
Let's assume that we have a database on some SQL server (let it be MS SQL
Server) and that we want to execute some parameterized query as a
pass.through query. How can we pass parameters to the server ?
Is it possible to use parameters in pass-through queries ?
An additional question: Is it possible to connect to a database on MySQL or...
|
by: Zlatko Matić |
last post by:
Hello.
I'm wondernig what is happennig whith saved pass-through queries nested in
regular JET query if regular JET query just filtrates result by start/end
date...Does pass-through query first returns all rows from server and then
JET filtrates result by start/end date, or JET first communicates with
server so that server returns only those...
|
by: Zlatko Matić |
last post by:
Hi everybody!
Recently I was struggling with client/server issues in MS Access/PostgreSQL
combination.
Although Access is intuitive and easy to use desktop database solution, many
problems appear when someone is trying to use it as front-end for real
server database systems such as PostgreSQL or MySQL.
One of these problems is regarding...
|
by: JezB |
last post by:
I know I can use the querystring mechanism to pass simple parameters to a
Page using Response.Redirect, but Im having problems getting this to work
using Server.Transfer or Server.Execute - is this by design ?
What is a simple alternative for passing optional parameters to a Page using
Server.Transfer or Server.Execute?
I don't want to...
| |
by: Woody Splawn |
last post by:
I am using SQL Server 2000 as a back-end to a VS.net Client/Server app. In
a certain report I use a view as part of the query spec. For the view, at
present, I am querying for all the records in the table. But I am wondering
if there is a way, at runtime, to pass values to the View (like start date
and end date) so that I don't have to...
|
by: ILCSP |
last post by:
Hello, I'm in the process of changing our 'normal' Access 2000 update
queries to Update Pass Through Queries. We have a SQL server 2000
database and we're using an Access 2000 database as our front end.
In the criteria of one of our update query fields, we use both the
isnull(field1) and Not IsNull(field2) then Field3, otherwise, set the...
|
by: skinnybloke |
last post by:
Hi - I have 3 access queries which I run via 1 macro.
Each of the queries now requires 2 parameters when they the run. The
parameters are start and end dates.
I have built the parameters into the queries but on running the macro
I have to enter each of the dates 3 times - once for each query.
Is there an easy way to ask for the...
|
by: Mayhem05 |
last post by:
I have an Access 2003 database that I need to write some VBA code for
to populate a table. The table is based on a query I have built in
Access queries. Right now I have 2 parameters that are passed to the
query from a form (DateFrom and DateTo). When I open the form and
populate the variables (DateFrom and DateTo) then open the query it...
|
by: gumby |
last post by:
I would like to call this stored procedure, but I am unable to pass
parameters to the @Start and @End.
Is thier a way to pass parameters to a pass through query from MS
Access?
SELECT COUNT(dbo.tblPersActionHistory.PersActionID) AS ,
.fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersActionID, 2)
AS
FROM ...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |