473,325 Members | 2,774 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

How to pass a parameter to an Access query from VB

I have a query in an Access db that prompts the user for info. (e.g. a primary key representing a company) before compiling the relevant table of results. This works fine in Access but I want to use this query from within VB.Net. But how do I, in code, pass a parameter to the Access query?

[I have noticed that .Net won't let me add this parameter query to my VB project whereas it will allow me to add other queries from the db that do not ask for a parameter.]
Aug 13 '06 #1
2 20393
Hemant Pathak
92 Expert
here is an example that works
------------------------------

Dim cmd As New ADODB.Command
Dim rs as New ADODB.Recordset

Dim sQueryName as String
Dim lParam1 as long
Dim sParam2 as String

lParam1 = 100
sParam2 = "Miller"

sQueryName = "QueryInAccess97"

With cmd
Set .ActiveConnection = con 'con is an active ADODB.Connection
.CommandText = sQueryName
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("Param1", adInteger, adParamInput, ,
lParam1)
.Parameters.Append .CreateParameter("Param2", adVarChar, adParamInput, len(sParam2),
sParam2)
End With

rs.Open cmd, , adOpenKeyset, adLockOptimistic

DEFINITION of the query "QueryInAccess97":

PARAMETERS Param1 long, Param2 text;
SELECT FROM Table1 WHERE Table1.CustomerID =[Param1] AND Table1.Name = [Param2]

1) You must create the parameters in the same order as they are defined in
your query.(Same type and name aswell of course)
2) if you use a parameter of type 'adVarchar' its length must be passed aswell.
Aug 23 '06 #2
Hemant Pathak
92 Expert
Dim CMD As New ADODB.Command
Dim PRM As New ADODB.Parameter
Dim CNN as New ADODB.Connection
Dim Rec as New ADODB.Recordset
Dim Para1 as long
Para1="Hemant"
With cmd
Set .ActiveConnection = CNN ‘Select the Source of active Connection
.CommandText = "Balance" ‘Access Query Name
.CommandType = adCmdStoredProc
Set prm = .CreateParameter("Param1", adVarChar, adParamInput, Len(Para1),Para1)
.Parameters.Append prm
End With
Set Rec=cmd.Excute
Aug 23 '06 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Don Seckler | last post by:
I have an update query that runs when a report closes. I have several reports that will need to run the update query with diferent criteria. I'd like to simply make the criteria change in the...
3
by: Maciek Zywno | last post by:
Hi, I managed to do this by using a query and !! syntax, but it does not work with SPs. So I created a procedure with "@" to define a paraemter, but how can I pass a value to this procedure from...
2
by: rg | last post by:
Hi all, I have query about passing a parameter from form to a query. My situation is as follows: I have a query that pulls up some data from a table, however there is a condition that must...
7
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...
0
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...
9
by: Alan Silver | last post by:
Hello, I'm a bit surprised at the amount of boilerplate code required to do standard data access in .NET and was looking for a way to improve matters. In Classic ASP, I used to have a common...
2
by: Roger | last post by:
Anyone know how to pass a parameter to a query via a report in Access 2003?
1
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...
2
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 ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.